Simple Chat with PHP and Postgres DB

<?php
/*

Simple chat application allows chats between users of same corp_id
- standalone (needs no includes/external files)
- sessionless
- quite fast
- requires almost no resources

Usage:
- chat.php?corp_id=<my-group>&account_id=<my_id>&account_name=<my-name> to open chat window
- If user has opened the chat window he will be logged in (if he has logged in lately and closed the window his last_access is updated)
- If no other users in your room (corp_id) are logged in then a message "0 user online" appear
- If messages exists they will be displayed
- Every 60secs the window will refreshed (default, change Chat::reloadSecs)
- Simply pressing chat-button without selecting a user refreshes the chat messages (and resets the timer)
- Every other day the old messages from the previous day will be deleted (no cronjob required)


*** SQL: create postgres database named "chat" (or change dsn below) ***

-- Table: chat_msgs

-- DROP TABLE chat_msgs;
--CREATE SEQUENCE chat_messages_id_seq START 1;

CREATE TABLE chat_msgs
(
  id integer NOT NULL DEFAULT nextval('chat_messages_id_seq'::regclass),
  corp_id character varying,
  created timestamp without time zone NOT NULL,
  chat_from integer NOT NULL, -- points to chat_users::id
  chat_to integer NOT NULL, -- points to chat_users::id
  msg text,
  CONSTRAINT chat_messages_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE chat_msgs
  OWNER TO postgres;
COMMENT ON TABLE chat_msgs
  IS 'Contains all new chat messages, only actual day will be kept
Chat class constructor will delete older messages';
COMMENT ON COLUMN chat_msgs.chat_from IS 'points to chat_users::id';
COMMENT ON COLUMN chat_msgs.chat_to IS 'points to chat_users::id';


-- Index: chat_from

-- DROP INDEX chat_from;

CREATE INDEX chat_from
  ON chat_msgs
  USING btree
  (chat_from);

-- Index: chat_to_idx

-- DROP INDEX chat_to_idx;

CREATE INDEX chat_to_idx
  ON chat_msgs
  USING btree
  (chat_to);

-- Index: corp_id_idx

-- DROP INDEX corp_id_idx;

CREATE INDEX corp_id_idx
  ON chat_msgs
  USING btree
  (corp_id COLLATE pg_catalog."default");

-- Index: created_idx

-- DROP INDEX created_idx;

CREATE INDEX created_idx
  ON chat_msgs
  USING btree
  (created);





-- Table: chat_users

-- DROP TABLE chat_users;
CREATE SEQUENCE chat_users_id_seq START 1;

CREATE TABLE chat_users
(
  id integer NOT NULL DEFAULT nextval('chat_users_id_seq'::regclass),
  corp_id character varying(64),
  created timestamp without time zone,
  last_access timestamp without time zone,
  account_id integer,
  account_name character varying,
  status integer,
  ip character varying,
  browser character varying,
  CONSTRAINT id_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE chat_users
  OWNER TO postgres;

-- Index: m_account_id_idx

-- DROP INDEX m_account_id_idx;

CREATE INDEX m_account_id_idx
  ON chat_users
  USING btree
  (account_id);

-- Index: m_corp_id_idx

-- DROP INDEX m_corp_id_idx;

CREATE INDEX m_corp_id_idx
  ON chat_users
  USING btree
  (corp_id COLLATE pg_catalog."default");

-- Index: m_created_idx

-- DROP INDEX m_created_idx;

CREATE INDEX m_created_idx
  ON chat_users
  USING btree
  (created);

-- Index: m_last_access_idx

-- DROP INDEX m_last_access_idx;

CREATE INDEX m_last_access_idx
  ON chat_users
  USING btree
  (last_access);

-- Index: m_status_idx

-- DROP INDEX m_status_idx;

CREATE INDEX m_status_idx
  ON chat_users
  USING btree
  (status);


*/
?><?php

/**
 * class for simple chat between users belonging to the same corp_id
 */


class Chat {
    protected $reloadSecs = 60; // secs until page refreshes
    protected $db;
    protected $id = -1; // starting -1, will be set after loginUser
    protected $corp_id;
    protected $account_id; // own account_id
    protected $account_name; // own name
    protected $uri = "chat.php";


    /**
     * @param PDO $db
     * @param string $corp_id
     * @return object $this for method chaining
    */
    public function __construct(PDO $db, $corp_id) {
        $this->db = $db;
        $this->corp_id = $corp_id;
        $this->clearOldEntries();
        return $this;
    }


    public function getAvailableUsers() {
        $users = array();
        $sql = sprintf("SELECT id, account_name FROM chat_users WHERE created::date=NOW()::date AND corp_id='%s' AND status=1 AND id != %s", $this->corp_id, $this->id);
        $stmt = $this->db->query($sql);
        if ($stmt) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $users[$row['id']] = $row['account_name'];
            }
        }
        return $users;
    }


    public function loginUser($account_id, $account_name) {
        $this->account_id = $account_id;
        $this->account_name = $account_name;
        $sql = sprintf("SELECT id FROM chat_users WHERE corp_id='%s' AND account_id=%s", $this->corp_id, $this->account_id);
        $stmt = $this->db->query($sql);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $this->id = $row['id'];
            $this->updateUser();
        } else {
            $this->id = $this->_insertUser();
        }
        return $this;
    }


    public function loginUserById($id) {
        $sql = sprintf("SELECT account_id, account_name FROM chat_users WHERE id=%s", $id);
        $stmt = $this->db->query($sql);
        if ($stmt) {
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $this->loginUser($row['account_id'], $row['account_name']);
        }
        // handle case when no user found ?
        return $this;
    }


    public function updateUser() {
        $sql = sprintf("UPDATE chat_users SET last_access=NOW() WHERE id=%s", $this->id);
        $this->db->query($sql);
        return $this;
    }


    protected function _insertUser() {
        $user = $this->id; // should be -1
        $sql = sprintf("INSERT INTO chat_users (corp_id,created,last_access,account_id,account_name,status,ip,browser)
                        VALUES ('%s',NOW(),NOW(),%s,'%s',1,'%s','%s') RETURNING id"
                        ,$this->corp_id
                        ,$this->account_id
                        ,$this->account_name
                        ,$_SERVER['REMOTE_ADDR']
                        ,$_SERVER['HTTP_USER_AGENT']
                        );
        $stmt = $this->db->query($sql);
        if ($stmt) {
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            $user = $row['id'];
        }
        return $user;
    }


    public function getMessages() {
        $messages = array(); // stamp,chatter, msg, color
        // timestamp, arrow to indicate chat direction (-> to, <- from), chatter, msg, css (chat-local|chat-remote)
        $sql = sprintf("SELECT to_char(m.created::time, 'HH24:MI:SS') AS stamp,
            CASE WHEN chat_from=%s THEN (SELECT '&#8594; '||account_name FROM chat_users WHERE id=m.chat_to)
                ELSE (SELECT '&#8592; '||account_name FROM chat_users WHERE id=m.chat_from)
            END AS chatter,
            m.msg ,
            CASE WHEN chat_from=%s THEN 'chat-local' ELSE 'chat-remote' END AS css
            FROM chat_msgs m
            WHERE m.created::date = NOW()::date AND m.corp_id='%s' AND (m.chat_from=%s OR m.chat_to=%s)
            ORDER BY m.created DESC", $this->id, $this->id, $this->corp_id, $this->id, $this->id);
        $stmt = $this->db->query($sql);
        if ($stmt) {
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $messages[] = $row;
            }
        }
        return $messages;
    }


/**
 * clearOldEntries
 * instead of a cronjob, this function clears old messages and user after midnight if user logs in
 */
    public function clearOldEntries() {
        $sql = "DELETE FROM chat_users WHERE created::date < NOW()::date";
        $this->db->exec($sql);
        $sql = "DELETE FROM chat_msgs WHERE created::date < NOW()::date";
        $this->db->exec($sql);
        return $this;
    }


    public function postMessage($chat_to, $msg) {
        $msg = filter_var($msg, FILTER_SANITIZE_STRING);
        $sql = sprintf("INSERT INTO chat_msgs (corp_id, created, chat_from, chat_to, msg) VALUES ('%s',NOW(),%s,%s,'%s')",
            $this->corp_id, $this->id, $chat_to, $msg);
        $this->db->query($sql);
        return $this;
    }


    public function displayMessages(array $messages) {
        print("\n");
        print('<div id="chat-container">');
        foreach($messages as $msg) {
            print("\n");
            printf('<p class="%s">%s <strong>%s:</strong> %s</p>', $msg['css'], $msg['stamp'], $msg['chatter'], $msg['msg']);
        }
        print("\n</div>");
        return $this;
    }


    public function displayChatForm(array $users) {
        if (count($users)===0) {
            print('0 user online.');
        }
        if (count($users) > 0) {
            printf('<form id="chat-form" method="POST" action="%s">', $this->uri);
            print("\n");
            printf('<input type="hidden" name="chat_from" id="chat_from" value="%s" />', $this->id);
            printf('<input type="hidden" name="corp_id" id="corp_id" value="%s" />', $this->corp_id);
            print("\n");
            print('<select name="chat_to" id="chat_to"><option value="0">---</option>');
            foreach($users as $id=>$name) {
                printf('<option value="%s">%s</option>', $id, $name);
            }
            print('</select>');
            print("\n");
            print('<input type="text" name="msg" value="" />');
            print('<input type="submit" name="chat_submit" id="chat_submit" value="Chat" />');
            print("\n</form>\n<br />\n");
        }
    }


    public function buildUrl() {
        return sprintf("%s%s%s?corp_id=%s&chat_from=%s&stamp=%s", (array_key_exists('HTTPS', $_SERVER) && $_SERVER['HTTPS']=='on')? 'https://':'http://',$_SERVER['HTTP_HOST'], $_SERVER['SCRIPT_NAME'], $this->corp_id, $this->id, microtime(true));
    }


    public function displayHeader() {
        printf('<!DOCTYPE html>
<html lang="de">
<head>
    <title>Simple Chat [%s]</title>
    <meta charset="utf-8">
    <style>
        html, body { font: normal 0.9em arial,helvetica,sans-serif; background-color: #efefef;}
        .chat-local { color: red;}
        .chat-remote { color: blue;}
    </style>
</head>', $this->corp_id);
        // if login - reload page
        if ($this->id > 0) {
            printf('
<body onload="setTimeout(function(){location.href=\'%s\';},%s)">', $this->buildUrl(), $this->reloadSecs*1000);
        } else {
            print("<body>");
        }
    print("\n");
    }


    public function displayFooter() {
        print("\n
</body>
</html>");
    }



}// class Chat



//
// main
//

// params
$corp_id = array_key_exists('corp_id', $_REQUEST)? $_REQUEST['corp_id'] : false;
$account_id = array_key_exists('account_id', $_REQUEST)? $_REQUEST['account_id'] : false;
$account_name = array_key_exists('account_name', $_REQUEST)? $_REQUEST['account_name'] : false;
$chat_to = array_key_exists('chat_to', $_REQUEST)? $_REQUEST['chat_to'] : false;
$chat_from = array_key_exists('chat_from', $_REQUEST)? $_REQUEST['chat_from'] : false;
$msg = array_key_exists('msg', $_REQUEST)? $_REQUEST['msg'] : false;

// insufficient params
if (!$corp_id AND ( (!$account_id OR !$account_name) OR (!$chat_from))) {
    die("Params: <br />a) corp_id, account_id, account_name <br />b) corp_id, chat_from, chat_to, msg");
}

// db connection (postgres PDO)
try {
    $db = new PDO('pgsql:host=localhost;dbname=chat','postgres','');
} catch(Exception $e) {
    die("Fatal error: Could not connect to database!");
}


// create new chat object for a user
$chat = new Chat($db, $corp_id);

// login chat user or update last_access
if ($chat_from) {
    $chat->loginUserById($chat_from);
} elseif($account_id && $account_name) {
    $chat->loginUser($account_id, $account_name);
} else {
    die("Insufficient data - no Login possible!");
}

// post message
if ($chat_to && $msg) {
    $chat->postMessage($chat_to, $msg);
}

// get list with available users online (chat_users::id)
$users = $chat->getAvailableUsers();

// get all messages to and from current user
$messages = $chat->getMessages();

// html header
$chat->displayHeader();

// display the chat form
$chat->displayChatForm($users);

// display the messages
$chat->displayMessages($messages);

// html footer
$chat->displayFooter();

// sql to check if user has new messages if chat window is closed (needs account_id and corp_id as params)
// replace placeholders (%s) with meaningful data, return 0 or the number of unread messages
// SELECT COALESCE(COUNT(*),0) FROM chat_msgs m LEFT JOIN chat_users u ON m.chat_to=u.id WHERE u.account_id=%s AND u.corp_id='%s' AND m.created > u.last_access;