planzelle

Simple Chat with PHP and Postgres DB

Nov 26th, 2015
123
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2. /*
  3.  
  4. Simple chat application allows chats between users of same corp_id
  5. - standalone (needs no includes/external files)
  6. - sessionless
  7. - quite fast
  8. - requires almost no resources
  9.  
  10. Usage:
  11. - chat.php?corp_id=<my-group>&account_id=<my_id>&account_name=<my-name> to open chat window
  12. - 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)
  13. - If no other users in your room (corp_id) are logged in then a message "0 user online" appear
  14. - If messages exists they will be displayed
  15. - Every 60secs the window will refreshed (default, change Chat::reloadSecs)
  16. - Simply pressing chat-button without selecting a user refreshes the chat messages (and resets the timer)
  17. - Every other day the old messages from the previous day will be deleted (no cronjob required)
  18.  
  19.  
  20. *** SQL: create postgres database named "chat" (or change dsn below) ***
  21.  
  22. -- Table: chat_msgs
  23.  
  24. -- DROP TABLE chat_msgs;
  25. --CREATE SEQUENCE chat_messages_id_seq START 1;
  26.  
  27. CREATE TABLE chat_msgs
  28. (
  29.   id integer NOT NULL DEFAULT nextval('chat_messages_id_seq'::regclass),
  30.   corp_id character varying,
  31.   created timestamp without time zone NOT NULL,
  32.   chat_from integer NOT NULL, -- points to chat_users::id
  33.   chat_to integer NOT NULL, -- points to chat_users::id
  34.   msg text,
  35.   CONSTRAINT chat_messages_pk PRIMARY KEY (id)
  36. )
  37. WITH (
  38.   OIDS=FALSE
  39. );
  40. ALTER TABLE chat_msgs
  41.   OWNER TO postgres;
  42. COMMENT ON TABLE chat_msgs
  43.   IS 'Contains all new chat messages, only actual day will be kept
  44. Chat class constructor will delete older messages';
  45. COMMENT ON COLUMN chat_msgs.chat_from IS 'points to chat_users::id';
  46. COMMENT ON COLUMN chat_msgs.chat_to IS 'points to chat_users::id';
  47.  
  48.  
  49. -- Index: chat_from
  50.  
  51. -- DROP INDEX chat_from;
  52.  
  53. CREATE INDEX chat_from
  54.   ON chat_msgs
  55.   USING btree
  56.   (chat_from);
  57.  
  58. -- Index: chat_to_idx
  59.  
  60. -- DROP INDEX chat_to_idx;
  61.  
  62. CREATE INDEX chat_to_idx
  63.   ON chat_msgs
  64.   USING btree
  65.   (chat_to);
  66.  
  67. -- Index: corp_id_idx
  68.  
  69. -- DROP INDEX corp_id_idx;
  70.  
  71. CREATE INDEX corp_id_idx
  72.   ON chat_msgs
  73.   USING btree
  74.   (corp_id COLLATE pg_catalog."default");
  75.  
  76. -- Index: created_idx
  77.  
  78. -- DROP INDEX created_idx;
  79.  
  80. CREATE INDEX created_idx
  81.   ON chat_msgs
  82.   USING btree
  83.   (created);
  84.  
  85.  
  86.  
  87.  
  88.  
  89. -- Table: chat_users
  90.  
  91. -- DROP TABLE chat_users;
  92. CREATE SEQUENCE chat_users_id_seq START 1;
  93.  
  94. CREATE TABLE chat_users
  95. (
  96.   id integer NOT NULL DEFAULT nextval('chat_users_id_seq'::regclass),
  97.   corp_id character varying(64),
  98.   created timestamp without time zone,
  99.   last_access timestamp without time zone,
  100.   account_id integer,
  101.   account_name character varying,
  102.   status integer,
  103.   ip character varying,
  104.   browser character varying,
  105.   CONSTRAINT id_pk PRIMARY KEY (id)
  106. )
  107. WITH (
  108.   OIDS=FALSE
  109. );
  110. ALTER TABLE chat_users
  111.   OWNER TO postgres;
  112.  
  113. -- Index: m_account_id_idx
  114.  
  115. -- DROP INDEX m_account_id_idx;
  116.  
  117. CREATE INDEX m_account_id_idx
  118.   ON chat_users
  119.   USING btree
  120.   (account_id);
  121.  
  122. -- Index: m_corp_id_idx
  123.  
  124. -- DROP INDEX m_corp_id_idx;
  125.  
  126. CREATE INDEX m_corp_id_idx
  127.   ON chat_users
  128.   USING btree
  129.   (corp_id COLLATE pg_catalog."default");
  130.  
  131. -- Index: m_created_idx
  132.  
  133. -- DROP INDEX m_created_idx;
  134.  
  135. CREATE INDEX m_created_idx
  136.   ON chat_users
  137.   USING btree
  138.   (created);
  139.  
  140. -- Index: m_last_access_idx
  141.  
  142. -- DROP INDEX m_last_access_idx;
  143.  
  144. CREATE INDEX m_last_access_idx
  145.   ON chat_users
  146.   USING btree
  147.   (last_access);
  148.  
  149. -- Index: m_status_idx
  150.  
  151. -- DROP INDEX m_status_idx;
  152.  
  153. CREATE INDEX m_status_idx
  154.   ON chat_users
  155.   USING btree
  156.   (status);
  157.  
  158.  
  159. */
  160. ?><?php
  161.  
  162. /**
  163.  * class for simple chat between users belonging to the same corp_id
  164.  */
  165.  
  166.  
  167. class Chat {
  168.     protected $reloadSecs = 60; // secs until page refreshes
  169.     protected $db;
  170.     protected $id = -1; // starting -1, will be set after loginUser
  171.     protected $corp_id;
  172.     protected $account_id; // own account_id
  173.     protected $account_name; // own name
  174.     protected $uri = "chat.php";
  175.  
  176.  
  177.     /**
  178.      * @param PDO $db
  179.      * @param string $corp_id
  180.      * @return object $this for method chaining
  181.     */
  182.     public function __construct(PDO $db, $corp_id) {
  183.         $this->db = $db;
  184.         $this->corp_id = $corp_id;
  185.         $this->clearOldEntries();
  186.         return $this;
  187.     }
  188.  
  189.  
  190.     public function getAvailableUsers() {
  191.         $users = array();
  192.         $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);
  193.         $stmt = $this->db->query($sql);
  194.         if ($stmt) {
  195.             while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  196.                 $users[$row['id']] = $row['account_name'];
  197.             }
  198.         }
  199.         return $users;
  200.     }
  201.  
  202.  
  203.     public function loginUser($account_id, $account_name) {
  204.         $this->account_id = $account_id;
  205.         $this->account_name = $account_name;
  206.         $sql = sprintf("SELECT id FROM chat_users WHERE corp_id='%s' AND account_id=%s", $this->corp_id, $this->account_id);
  207.         $stmt = $this->db->query($sql);
  208.         $row = $stmt->fetch(PDO::FETCH_ASSOC);
  209.         if ($row) {
  210.             $this->id = $row['id'];
  211.             $this->updateUser();
  212.         } else {
  213.             $this->id = $this->_insertUser();
  214.         }
  215.         return $this;
  216.     }
  217.  
  218.  
  219.     public function loginUserById($id) {
  220.         $sql = sprintf("SELECT account_id, account_name FROM chat_users WHERE id=%s", $id);
  221.         $stmt = $this->db->query($sql);
  222.         if ($stmt) {
  223.             $row = $stmt->fetch(PDO::FETCH_ASSOC);
  224.             $this->loginUser($row['account_id'], $row['account_name']);
  225.         }
  226.         // handle case when no user found ?
  227.         return $this;
  228.     }
  229.  
  230.  
  231.     public function updateUser() {
  232.         $sql = sprintf("UPDATE chat_users SET last_access=NOW() WHERE id=%s", $this->id);
  233.         $this->db->query($sql);
  234.         return $this;
  235.     }
  236.  
  237.  
  238.     protected function _insertUser() {
  239.         $user = $this->id; // should be -1
  240.         $sql = sprintf("INSERT INTO chat_users (corp_id,created,last_access,account_id,account_name,status,ip,browser)
  241.                         VALUES ('%s',NOW(),NOW(),%s,'%s',1,'%s','%s') RETURNING id"
  242.                         ,$this->corp_id
  243.                         ,$this->account_id
  244.                         ,$this->account_name
  245.                         ,$_SERVER['REMOTE_ADDR']
  246.                         ,$_SERVER['HTTP_USER_AGENT']
  247.                         );
  248.         $stmt = $this->db->query($sql);
  249.         if ($stmt) {
  250.             $row = $stmt->fetch(PDO::FETCH_ASSOC);
  251.             $user = $row['id'];
  252.         }
  253.         return $user;
  254.     }
  255.  
  256.  
  257.     public function getMessages() {
  258.         $messages = array(); // stamp,chatter, msg, color
  259.         // timestamp, arrow to indicate chat direction (-> to, <- from), chatter, msg, css (chat-local|chat-remote)
  260.         $sql = sprintf("SELECT to_char(m.created::time, 'HH24:MI:SS') AS stamp,
  261.             CASE WHEN chat_from=%s THEN (SELECT '&#8594; '||account_name FROM chat_users WHERE id=m.chat_to)
  262.                 ELSE (SELECT '&#8592; '||account_name FROM chat_users WHERE id=m.chat_from)
  263.             END AS chatter,
  264.             m.msg ,
  265.             CASE WHEN chat_from=%s THEN 'chat-local' ELSE 'chat-remote' END AS css
  266.             FROM chat_msgs m
  267.             WHERE m.created::date = NOW()::date AND m.corp_id='%s' AND (m.chat_from=%s OR m.chat_to=%s)
  268.             ORDER BY m.created DESC", $this->id, $this->id, $this->corp_id, $this->id, $this->id);
  269.         $stmt = $this->db->query($sql);
  270.         if ($stmt) {
  271.             while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  272.                 $messages[] = $row;
  273.             }
  274.         }
  275.         return $messages;
  276.     }
  277.  
  278.  
  279. /**
  280.  * clearOldEntries
  281.  * instead of a cronjob, this function clears old messages and user after midnight if user logs in
  282.  */
  283.     public function clearOldEntries() {
  284.         $sql = "DELETE FROM chat_users WHERE created::date < NOW()::date";
  285.         $this->db->exec($sql);
  286.         $sql = "DELETE FROM chat_msgs WHERE created::date < NOW()::date";
  287.         $this->db->exec($sql);
  288.         return $this;
  289.     }
  290.  
  291.  
  292.     public function postMessage($chat_to, $msg) {
  293.         $msg = filter_var($msg, FILTER_SANITIZE_STRING);
  294.         $sql = sprintf("INSERT INTO chat_msgs (corp_id, created, chat_from, chat_to, msg) VALUES ('%s',NOW(),%s,%s,'%s')",
  295.             $this->corp_id, $this->id, $chat_to, $msg);
  296.         $this->db->query($sql);
  297.         return $this;
  298.     }
  299.  
  300.  
  301.     public function displayMessages(array $messages) {
  302.         print("\n");
  303.         print('<div id="chat-container">');
  304.         foreach($messages as $msg) {
  305.             print("\n");
  306.             printf('<p class="%s">%s <strong>%s:</strong> %s</p>', $msg['css'], $msg['stamp'], $msg['chatter'], $msg['msg']);
  307.         }
  308.         print("\n</div>");
  309.         return $this;
  310.     }
  311.  
  312.  
  313.     public function displayChatForm(array $users) {
  314.         if (count($users)===0) {
  315.             print('0 user online.');
  316.         }
  317.         if (count($users) > 0) {
  318.             printf('<form id="chat-form" method="POST" action="%s">', $this->uri);
  319.             print("\n");
  320.             printf('<input type="hidden" name="chat_from" id="chat_from" value="%s" />', $this->id);
  321.             printf('<input type="hidden" name="corp_id" id="corp_id" value="%s" />', $this->corp_id);
  322.             print("\n");
  323.             print('<select name="chat_to" id="chat_to"><option value="0">---</option>');
  324.             foreach($users as $id=>$name) {
  325.                 printf('<option value="%s">%s</option>', $id, $name);
  326.             }
  327.             print('</select>');
  328.             print("\n");
  329.             print('<input type="text" name="msg" value="" />');
  330.             print('<input type="submit" name="chat_submit" id="chat_submit" value="Chat" />');
  331.             print("\n</form>\n<br />\n");
  332.         }
  333.     }
  334.  
  335.  
  336.     public function buildUrl() {
  337.         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));
  338.     }
  339.  
  340.  
  341.     public function displayHeader() {
  342.         printf('<!DOCTYPE html>
  343. <html lang="de">
  344. <head>
  345.    <title>Simple Chat [%s]</title>
  346.     <meta charset="utf-8">
  347.     <style>
  348.         html, body { font: normal 0.9em arial,helvetica,sans-serif; background-color: #efefef;}
  349.         .chat-local { color: red;}
  350.         .chat-remote { color: blue;}
  351.     </style>
  352. </head>', $this->corp_id);
  353.         // if login - reload page
  354.         if ($this->id > 0) {
  355.             printf('
  356. <body onload="setTimeout(function(){location.href=\'%s\';},%s)">', $this->buildUrl(), $this->reloadSecs*1000);
  357.         } else {
  358.             print("<body>");
  359.         }
  360.     print("\n");
  361.     }
  362.  
  363.  
  364.     public function displayFooter() {
  365.         print("\n
  366. </body>
  367. </html>");
  368.     }
  369.  
  370.  
  371.  
  372. }// class Chat
  373.  
  374.  
  375.  
  376. //
  377. // main
  378. //
  379.  
  380. // params
  381. $corp_id = array_key_exists('corp_id', $_REQUEST)? $_REQUEST['corp_id'] : false;
  382. $account_id = array_key_exists('account_id', $_REQUEST)? $_REQUEST['account_id'] : false;
  383. $account_name = array_key_exists('account_name', $_REQUEST)? $_REQUEST['account_name'] : false;
  384. $chat_to = array_key_exists('chat_to', $_REQUEST)? $_REQUEST['chat_to'] : false;
  385. $chat_from = array_key_exists('chat_from', $_REQUEST)? $_REQUEST['chat_from'] : false;
  386. $msg = array_key_exists('msg', $_REQUEST)? $_REQUEST['msg'] : false;
  387.  
  388. // insufficient params
  389. if (!$corp_id AND ( (!$account_id OR !$account_name) OR (!$chat_from))) {
  390.     die("Params: <br />a) corp_id, account_id, account_name <br />b) corp_id, chat_from, chat_to, msg");
  391. }
  392.  
  393. // db connection (postgres PDO)
  394. try {
  395.     $db = new PDO('pgsql:host=localhost;dbname=chat','postgres','');
  396. } catch(Exception $e) {
  397.     die("Fatal error: Could not connect to database!");
  398. }
  399.  
  400.  
  401. // create new chat object for a user
  402. $chat = new Chat($db, $corp_id);
  403.  
  404. // login chat user or update last_access
  405. if ($chat_from) {
  406.     $chat->loginUserById($chat_from);
  407. } elseif($account_id && $account_name) {
  408.     $chat->loginUser($account_id, $account_name);
  409. } else {
  410.     die("Insufficient data - no Login possible!");
  411. }
  412.  
  413. // post message
  414. if ($chat_to && $msg) {
  415.     $chat->postMessage($chat_to, $msg);
  416. }
  417.  
  418. // get list with available users online (chat_users::id)
  419. $users = $chat->getAvailableUsers();
  420.  
  421. // get all messages to and from current user
  422. $messages = $chat->getMessages();
  423.  
  424. // html header
  425. $chat->displayHeader();
  426.  
  427. // display the chat form
  428. $chat->displayChatForm($users);
  429.  
  430. // display the messages
  431. $chat->displayMessages($messages);
  432.  
  433. // html footer
  434. $chat->displayFooter();
  435.  
  436. // sql to check if user has new messages if chat window is closed (needs account_id and corp_id as params)
  437. // replace placeholders (%s) with meaningful data, return 0 or the number of unread messages
  438. // 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;
RAW Paste Data