Displaying Last Record in a table?? **Solved**

[Round]

Hello all,
How do i display the last record within a table?
I have a table that stores news and want my site only to display the latest entry.
So far I have:-
[code]<?php
#connect to db
$conn = @mysql_connect(  "localhost", "username", "password" )
      or die( "Err:conn");
#select db
$rs = @mysql_select_db( "dbname", $conn)
or die( "ERR:Db");

#create query
$sql = "select * from vwnews order by subject desc limit 1";

#exe query
$rs = mysql_query( $sql, $conn )
or die( "Could not execute Query");

#retrieve data
while ( $row = mysql_fetch_array( $rs ) )
{
echo ( $row["subject"] );
}
?>[/code]

but it keeps displaying the top value?
Any Ideas?
Many Thanks

[Round]

Doh!! of course it's not displaying last record. It is displaying subject in alphabetical order!! Monday mornings!! LOL.  :D
Anyway this just means I need help on getting it to display the last record I realise why my current code isn't.
Cheers

[gmwebs]

Would you perhaps have to first determine the total number of rows and then limit your resultset by total number -1?

[code]

<?php

$sql = "Select columnname from table";

$rs = mysql_query($sql, $conn);

$numrows = mysql_num_rows($rs);

$lastrecord = ($numrows - 1);

$newsql = "Select * from table limit " . $lastrecord . ", 1"; // LIMIT startingrecord, recordsfromstartingrecord

$result = mysql_query($newsql, $conn);

?>

[/code]

Something along those lines maybe? I would think there is a far more efficient way of doing this, but this is all I can think of right now...

[Round]

Awesome Cheers gmwebs ive managed to get it to work:-

[code]<?php
#connect to db
$conn = @mysql_connect(  "localhost", "username", "password" )
      or die( "Err:conn");
#select db
$rs = @mysql_select_db( "dbname", $conn)
or die( "ERR:Db");

#create query
$sql = "Select subject from vwnews";

#exe query
$rs = mysql_query( $sql, $conn )
or die( "Could not execute Query");

$numrows = mysql_num_rows($rs);

$lastrecord = ($numrows - 1);

$newsql = "Select * from vwnews limit " . $lastrecord . ", 1"; // LIMIT startingrecord, recordsfromstartingrecord

$result = mysql_query($newsql, $conn);

while( $row = mysql_fetch_array($result) )
{

echo ( "".$row["subject"] );
}
?>
[/code]

Many Thanks

[gmwebs]

Good stuff... Glad I could help! You might want to edit the subject as solved?

[argoSquirrel]

Just FYI, this is not a technique you would want to use on a fairly active site.

Because you are breaking the query up into 2 separate queries, you may potentially get off sync if an insert occurs between the 2 queries.  Or, for a large site, 100 inserts may occur during that time.  This is 1 reason why Stored Procedures and Transactions are a big deal.

In your case it may not be a big deal, but I thought I should raise the issue (for the future perhaps ;) )

Really, what it sounds like you should be doing is not counting on the last row to be your latest news, but having a submit_date field on your News table.  Then you could simply do:

[code]
SELECT TOP 1 title, body, etc...
FROM News
ORDER BY submit_date DESC
[/code]

[Round]

Thats cool I looked into that, also checked if it had timestamp data. The thing is its a little box in the corner of the page that shows the latest news eg. The library will now close at 4.30 on friday.
Its already really out of date, no one is adding to it, so I thought I would just keep it in place and not alter the tables. I know if I took it away all of a sudden it would get noticed and people will want it again.

If the traffic gets really high and people do start to update it regulary then I will have to look at the table structure. However you have gave me a thought that if the query can't execute I could put an error message in that says: Welcome to the college. That way it will never show what the user thinks is an error.

Thanks for the advice.