CODEWITHSUNDEEP
phpmysqlsql-order-bylimitquerying
James
James

Reputation: 546

PHP and MySQL: Order by most recent date and limit 10

I am building a notes system on my site and I've got to the stage where users can post notes into the MySQL database using PHP and then PHP prints them out on a page. However, when they print/echo out, the oldest one appears first but I want the most recent first. I also want them to be limited to 10, so only 10 appear on the page. Here is my PHP code, your help will be much appreciated:

// initialize some variables
$notedisplaylist = "";
$myObject = "";
$result = mysql_query("SELECT * FROM notes WHERE note_author_id='$u_id' ORDER BY         date_time");

while($row = mysql_fetch_array($result)){
  $note_title = $row["note_title"];
  $note_body = $row["note_body"];
  $date = $row["date_time"];
  $notedisplaylist .= '<h2>' . $note_title . '</h2><br /><p>' . $note_body . '</p><hr /><p>Noted: ' . $date . '</p><hr /><br />';
}

Upvotes: 16

Views: 69504

Answers (6)

Ali
Ali

Reputation: 286

If in case you want your LIMIT to be a variable, here I named it $limit:

"SELECT * FROM tbl ORDER BY input_date DESC LIMIT 0, $limit";

Upvotes: 0

dubvfan87
dubvfan87

Reputation: 641

Do this

$result = mysql_query("SELECT * FROM notes WHERE note_author_id='$u_id' ORDER BY date_time DESC LIMIT 0, 10");

Upvotes: 5

kgilden
kgilden

Reputation: 10356

Try

$result = mysql_query("SELECT * FROM notes WHERE note_author_id='$u_id' ORDER BY date_time DESC LIMIT 10");

For a more detailed explanation on ORDER and LIMIT, visit the MySQL doc's articles about sorting rows and the basic select syntax (look for a bullet describing LIMIT).

Upvotes: 7

Book Of Zeus
Book Of Zeus

Reputation: 49895

use:

SELECT * FROM notes WHERE note_author_id='$u_id' ORDER BY date_time DESC LIMIT 10

DESC : descending order ( from newest to oldest ) LIMIT 10: first 10 records found.

Upvotes: 10

Sylvain Cleymans
Sylvain Cleymans

Reputation: 1927

This should do it : 

$result = mysql_query("SELECT * FROM notes WHERE note_author_id='$u_id' ORDER BY date_time DESC LIMIT 0, 10");

Upvotes: 30

Mithun Satheesh
Mithun Satheesh

Reputation: 27845

give like

 ORDER BY date_time DESC

otherwise you are sorting them in ascending order.. thats why older ones come first

Upvotes: 6

Related Questions