Combine 2 SQL Queries and output in list by views

Question

I got this little code:

$querym = mysql_query("SELECT count(*), videoid
FROM daystat_movies WHERE date >= now() - INTERVAL 1 DAY GROUP by videoid DESC");

while ($qshow = mysql_fetch_array($querym)) {
$videoid[1] = $qshow[videoid];
$date[1] = $qshow[date];

$queryc = mysql_query("SELECT * FROM daystat_movies WHERE date >= now() - INTERVAL 1 DAY AND videoid='$videoid[1]'");
$views[1] = mysql_num_rows($queryc);

echo "<tr><td>$videoid[1]</td> <td> $views[1] </td></tr>";

}

What gives me this outcome:

Most Viewed Videos (Last 24 Hours)  Views
98                                    3
100                                   4

What I want now is to have the video id with the most views (in this case 100 with 4 views) to show on top of the list.

Does this answer your question? [Why shouldn't I use mysql\_\* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Dharman, Feb 07 '20 at 14:13

score 1 · Accepted Answer · answered Jan 22 '20 at 12:12

You can use aggregate functions (like count(*)) in the ORDER BY clause. There is also no need to execute alot of queries in the while loop, just to get the number of rows, wich you should already know, since you've selected count(*). So the full code could be:

$querym = mysql_query("
    SELECT count(*) as cnt, videoid
    FROM daystat_movies
    WHERE date >= now() - INTERVAL 1 DAY
    GROUP by videoid
    ORDER BY cnt DESC
");

while ($qshow = mysql_fetch_array($querym)) {
   echo "<tr><td>{$qshow['videoid']}</td><td>{$qshow['cnt']}</td></tr>";
}

I also suggest to switch from the outdated, deprecated and already removed in PHP 7 mysql library to either mysqli or PDO.

Thankyou so much sir! You are going into my legends list :) .. My coding is outdated yes i know.. it has been some years ago since i played around with coding.. Guess its time to read some tutorials etc. and catch up! But this is exactly what i was wanting to do and it works perfect! — SeeNoMore, Jan 22 '20 at 13:21

Combine 2 SQL Queries and output in list by views

1 Answers1