title: Notification system in PHP
body: <p>I am building a responsive website same as social networking
  I have 4 tables <code>checkin</code> <code>friend</code> <code>post</code> <code>notification</code> </p>
  
  <p>Here is structure of <code>notification</code> table</p>
  
  <pre><code> Notification
  
   id 
   user_id
   activity_type   
   source_id  
   created_date   
   created_by     
   updated_date   
   updated_by 
  </code></pre>
  
  <p>Where activity type is a string for example "friendrequest" if someone accepts friend request,"checkin" if someone checkin to some event and "post" if someone creates a post.</p>
  
  <p>Now i store the data in <code>Notification</code> table whenever any of those action is triggered,but i am confused that how should i display the data in a list to logged in users.Data in the <code>notification</code> table will be like this</p>
  
  <pre><code>id  user_id activity_type source_id created_date  
  5   1       friend        488       2015-10-13   
  6   48      checkin       10        2015-10-13   
  7   1       checkin       9         2015-10-13   
  8   9       friend        7         2015-10-13   
  9   1       checkin       27        2015-10-13   
  10  48      post          18        2015-10-13  
  11  9       checkin       8         2015-10-13  
  </code></pre>
  
  <p>Like as per the <code>activity_type</code> the <code>source_id</code> will connect to either of <code>event</code>,<code>checking</code>or <code>post</code> table. So which query should i use to display notification correctly.</p>
  
  <p>And how do i control the notification fetch,like any activity should only be displayed to the user's friends only.</p>
  
  <p>I used this query but its not working properly</p>
  
  <pre><code>SELECT
      group_concat(`user_id`) as 'users',
      `activity_type`,
      `source_id` as 'post'
  FROM
      `notification`
  WHERE
      `source_id` IN ("488") 
  GROUP BY
      `source_id`,
      `activity_type` 
  ORDER BY
      `source_id` 
  </code></pre>
  
  <p>Let me know if there are any example projects available online as well
  From the answers i got here i wrote this query
  Let me know if it can be improvise any other way</p>
  
  <pre><code> SELECT 
        notification.*,event.title 
   FROM 
        notification 
   INNER JOIN 
        event 
   ON 
        event.id = notification.source_id 
   AND 
        notification.activity_type = "checkin"
   WHERE
        notification.user_id in (488,489) 
   UNION 
   SELECT 
        notification.*,user.firstname FROM notification 
   INNER JOIN 
        user 
   ON 
        user.id = notification.source_id 
   AND 
        notification.activity_type = "friend"
   WHERE
        notification.user_id in (488,489) 
  </code></pre>
  
  <p>What should i do to find the friends of logged in user?
  Should i write another query like</p>
  
  <pre><code>Select friend_id from friend where user_id=1
  </code></pre>
  
  <p>and save the array of <code>id</code> and pass it to the <code>notification</code> queries above</p>
  
  <p>Is there any way to do that any other way without writing 2 different queries?</p>