SQL query join order by

Question

I'm making and instagram parody and I need to make a feed. I have 2 tables "following" and "Posts".

This is the "following" database:

This is the "posts" database:

Here is my code to try and select the people that the user follows and put their posts into their feed:

<?php

$username = $_SESSION['username'];
$sql = "SELECT username_follow FROM following WHERE follower = '$username'";
$rsu = mysql_query($sql, $conn);
while ($row = mysql_fetch_array($rsu)){
  $uername2 = $row[0];
  $sql2 = "SELECT pic, `time` FROM `Posts` WHERE username = '$uername2' ORDER BY `Posts`.`id` DESC";
  $res = mysql_query($sql2, $conn);
  //echo $row[0];
  while ($row2 = mysql_fetch_row($res)) {
    echo "<br> <br>";
    echo "<center>";
    echo "<img width='450px' height='450px' src='data:image;base64,".base64_encode($row2[0])."'>";

    echo "<br> <br>";
    echo "</center>";
  }
}

?>

But how can I show the pictures in chronological order?

Please stop using `mysql_*` functions. They've been deprecated for over 2 years now. Your code is wide open to [SQL injection](https://en.wikipedia.org/wiki/SQL_injection). This basicly means that it would take less than a minute for any visitor of your website to do whatever they want with your database. That includes destroying it. — icecub, Aug 12 '16 at 22:12
@icecub for the record, that is in a session and we don't know how it got there — Drew, Aug 12 '16 at 22:13
@Drew Agreed. But still it's best practise to stop using it anyway when there are better and safer functions available. I'm sure there's a fair bit you can do to prevent SQL injection by checking the string, but Prepared Statements is the safest bet imho. — icecub, Aug 12 '16 at 22:17
`mysql_` fuctions were deprecated 3 years ago with the release of [PHP5.5 on 20 June 2013](https://secure.php.net/ChangeLog-5.php#5.5.0). They are not available any more in PHP 7.xx. It is completely unnecessary to use these unmaintained features in code that you develop now. — trincot, Aug 12 '16 at 22:25
Possible duplicate of [Why shouldn't I use mysql\_\* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — tuxnani, Aug 13 '16 at 04:22

score 1 · Accepted Answer · answered Aug 12 '16 at 22:30

1

Join the two tables and order by time

SELECT f.username_follow, p.pic, p.time
FROM following AS f
JOIN Posts AS p ON p.username = f.username_follow
WHERE follower = '$username'
ORDER BY p.time

answered Aug 12 '16 at 22:30

Barmar

741,623
53
500
612

Thanks that works!! – Ethan Hofton Aug 13 '16 at 08:32

SQL query join order by

1 Answers1