Grab the latest row from a table

[fighnight]

I am creating a news system and I want to grab like the latest news from the table of news, how can I do this?

[hitman6003]

SELECT id, article_title FROM table_name ORDER BY date_submitted DESC LIMIT 1

[law]

if you don't have "date submitted" field or you want to grab the highest id row of some other table you can use num_row to produce the id number for your query

 

http://us2.php.net/mysql-num-rows

[.josh]

if you don't have "date submitted" field or you want to grab the highest id row of some other table you can use num_row to produce the id number for your query

 

http://us2.php.net/mysql-num-rows

 

One would hope that a table full of news would have dates/times associated with them...

[hitman6003]

if you don't have "date submitted" field or you want to grab the highest id row of some other table you can use num_row to produce the id number for your query

 

That's the worst idea I've ever heard.  The method you are suggesting is to do:

 

$query = "SELECT id FROM table";
$result = mysql_query($query);
echo "Number of rows in table is " . mysql_num_rows($result);

 

What if there is 100000 rows in the table?  That query is now returning everyone one of them to php which then must count them to get the result...at a minimum it would take 15-20 seconds to do the retrieve and then send the rows to php, not counting any other processing.  It makes much, MUCH more sense to do the counting in the database:

 

$query = "SELECT COUNT(id) FROM table";
$result = mysql_query($query);
echo "Number of rows in table is " . mysql_result($result, 0);

 

If the table is a MyISAM table, then the latter query's result is near instant.  If it's InnoDB, and you are counting on an indexed column (and you should be), then it is again, near instant.