SHARE
TWEET

Simple Chat with PHP and Postgres DB

planzelle Nov 26th, 2015 102 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top