Jump to content

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


Round

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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...
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.