mysql_fetch_array() problems with TOP(10)

Question

trying to make a compliments area of my test site

$result = mysql_query("SELECT message, username FROM compliments order by date DESC");
while($r = mysql_fetch_array($result))    
{
    $Name = $r["username"];
    $Message = $r["message"];
}

this however, gets all the messages,

i want to limit to only the most recent 10

so i tried this..

$result = mysql_query("SELECT TOP(10) message, username FROM compliments order by date ASC");
while($r = mysql_fetch_array($result))    
{
    $Name = $r["username"];
    $Message = $r["message"];
}

throws an error

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /var/www/vhosts/localhost/httpdocs/readcompliments.php on line 52

how can i do this successfuly?

EDIT:

Now using

SELECT  message, username FROM compliments order by date ASC LIMIT 10

and it works! but the problem is, that its getting the oldest 10! how do i make it so it gets the most recent 10?

Please stop using PHP's deprecated mysql_ API. If ASC isn't working, try something else — Strawberry, Mar 19 '18 at 06:40

D-Shih · Accepted Answer · 2018-03-19T03:37:40.070

2

IF you are using Mysql

TOP is for sqlServer,LIMIT for Mysql

SELECT  message, username FROM compliments order by date ASC LIMIT 10

Instead of

SELECT TOP(10) message, username FROM compliments order by date ASC

EDIT

If you want to get most recent 10,You can use order by date DESC

SELECT  message, username FROM compliments order by date DESC LIMIT 10

edited Mar 19 '18 at 03:37

answered Mar 19 '18 at 03:26

D-Shih

42,799
6
22
44

it works! but the problem is, that its getting the oldest 10! how do i make it so it gets the most recent 10? – BingBang32 Mar 19 '18 at 03:31
@BingBang32 I edit my answer wish can help you – D-Shih Mar 19 '18 at 03:38
hi, i really need to sort by the most recent 10 but to sort by ASC, not DESC – BingBang32 Mar 19 '18 at 03:48
`ASC` is oldest to newest,`DESC ` is newest to oldest. `order by date DESC LIMIT 10` will get most recent 10 – D-Shih Mar 19 '18 at 03:54

score 1 · Answer 2 · answered Mar 19 '18 at 03:27

1

Add LIMIT to your query:

"SELECT message, username FROM compliments order by date ASC LIMIT 10;"

answered Mar 19 '18 at 03:27

Karlo Kokkak

3,632
4
17
31

Rendi Wahyudi Muliawan · Answer 3 · 2018-03-19T03:57:29.697

1

Don't forget MySQL is deprecated, change your script to MySQLi

Warning MySQL was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

$result = mysqli_query($conn, "SELECT  message, username FROM compliments order by date DESC LIMIT 10");
while($r = mysqli_fetch_array($result))    
{
    $Name = $r["username"];
    $Message = $r["message"];
}

edited Mar 19 '18 at 03:57

answered Mar 19 '18 at 03:51

Rendi Wahyudi Muliawan

294
1
9

mysql_fetch_array() problems with TOP(10)

3 Answers3