-6

I need to get the result order by time but because im using a sql query into a while of an other sql query i get the result ordered by $arrID

$req1==mysql_query("SELECT friendId FROM friends ");
while($res1=mysql_fetch_row($req1)) {
    $arrID = $res['friendId'];

    $req = mysql_query("SELECT * FROM posts WHERE postby=$arrID  ORDER BY posttime DESC ");
    while ($res = mysql_fetch_row($req)) {
        echo $res['bostby'];
        echo $res['postcontent'];
    }
}

the result is :

post 1 by 2 content aaaaaaaa  time: 11:60
post 2 by 2 content bbbbbbbb   time 11:59
post 3 by 2 content aaaaaaaa  time: 11:58
post 4 by 2 content bbbbbbbb   time 11:56
post 5 by 3 content aaaaaaaa  time: 11:61
post 6 by 3 content bbbbbbbb   time 11:60
post 7 by 3 content aaaaaaaa  time: 11:59
post 8 by 5 content bbbbbbbb   time 12:20
post 8 by 5 content bbbbbbbb   time 12:19

and i need the resut to be order by time not by id

post 8 by 5 content bbbbbbbb   time 12:20
post 8 by 5 content bbbbbbbb   time 12:19
post 1 by 2 content aaaaaaaa  time: 11:60
post 6 by 3 content bbbbbbbb   time 11:60
post 2 by 2 content bbbbbbbb   time 11:59
post 7 by 3 content aaaaaaaa  time: 11:59
Barmar
  • 741,623
  • 53
  • 500
  • 612
bennaya marwen
  • 1
  • 3
  • 1
    ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 26 '17 at 21:32
  • _**thanks** for the advice but can you solve this plz??_ – bennaya marwen May 26 '17 at 21:36
  • And don't use a query in a while of another query. See JOINs. – Strawberry May 26 '17 at 22:40

1 Answers1

1

Use a single query that joins the two tables, instead of doing nested loops.

SELECT p.* FROM posts AS p
JOIN friends AS f ON p.postby = f.friendId
ORDER BY p.posttime DESC

From your comment, it appears your first query isn't so simple. But you can join with a subquery.

SELECT p.* FROM posts AS p
JOIN (
    SELECT user_from AS friendId
    FROM friend_requests 
    WHERE user_to = $SessionID AND accepted = '1'
    UNION
    SELECT user_to AS friendId
    FROM friend_requests
    WHERE user_from = $SessionID AND accepted = '1'
) AS f ON p.postby = f.friendId
ORDER BY p.posttime DESC
Barmar
  • 741,623
  • 53
  • 500
  • 612