title: Social timeline SQL logic
body: <p>I am not experienced with MySQL genius so pardon me. I have 2 tables, </p>
  
  <ul>
  <li><p>TABLE 1 contains column names: <code>post_id (int, Auto Increment), post (varchar, user posts) and like (int) {like is incremented when a user likes the post e.g similar to facebook likes}</code></p></li>
  <li><p>TABLE 2 contains column name: <code>id (int, Auto Increment) and post_id(int, foreign key to TABLE 1), user_id(varchar, User who liked this post)</code></p></li>
  </ul>
  
  <p>Now here is the tricky part for me, I want to select all posts from TABLE 1 and also indicate to the user which post has already been liked through TABLE 2 when a user requests for timeline, thus preventing the user from liking the the post again e.g how likes works on Instagram or any other social network i.e unlike if already liked. </p>
  
  <p>What I tried using PHP and MySQL: Basically I retrieved all post from TABLE 1 based on the user retrieving a timeline and then I retrieved all data from TABLE 2 based on the user who needs the timeline, then I check to see if there is a match with TABLE 1's returned data and TABLE 2's returned data. With each returned post in the timeline I tag 1 to indicate in code the incoming post has been liked and 0 for not liked and then I display the appropriate UI flow to user in doing so I can make sure the user does not like the same post twice.</p>
  
  <p><strong>Selecting all from TABLE 2</strong></p>
  
  <pre><code>SELECT * FROM `TABLE_2` WHERE `username` = :username
  </code></pre>
  
  <p><strong>Selecting from TABLE 1</strong></p>
  
  <pre><code>SELECT * FROM `TABLE_1` WHERE `reporter` IN (SELECT `destination_id` FROM `followers` WHERE `source_id` = :username)
           UNION
           SELECT * FROM `TABLE_1` WHERE `reporter` = :username
           ORDER BY `created_at` DESC LIMIT 20
  </code></pre>
  
  <p>The above query returns a users timeline based on another logic and certain other factors</p>
  
  <p>Now I compare both returned data from TABLE 1 and TABLE 2</p>
  
  <pre><code>if($likes != NULL)
  {
     $count = 0;
     foreach($returnedtimeline["posts"] as $single)
     {
         if(array_search($likes[$count], $single) == "id")
         {
                $returnedtimeline["posts"][$count]["liked"] = 1;
                $count++;
         }else{
                $returnedtimeline["posts"][$count]["liked"] = 0;
                $count++;
              }
    }
  </code></pre>
  
  <p>But I believe there is a better way I can do this through one direct query than multiple queries because my methods has large disadvantages with time, amount of data and processing power of the server.</p>