Jump to content


Photo

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


  • Please log in to reply
6 replies to this topic

#1 Round

Round
  • Members
  • PipPipPip
  • Advanced Member
  • 104 posts
  • LocationEngland

Posted 23 October 2006 - 09:11 AM

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:-
<?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"] );
	}
	?>

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

If it all gets too much just remember that, at the end of the day... It gets dark!! or that on the other hand... There are more Fingers!!
If you eventually get it right then Hip Hip ARRAY!! lol


#2 Round

Round
  • Members
  • PipPipPip
  • Advanced Member
  • 104 posts
  • LocationEngland

Posted 23 October 2006 - 09:30 AM

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

If it all gets too much just remember that, at the end of the day... It gets dark!! or that on the other hand... There are more Fingers!!
If you eventually get it right then Hip Hip ARRAY!! lol


#3 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 23 October 2006 - 09:53 AM

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


<?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);

?>


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...

#4 Round

Round
  • Members
  • PipPipPip
  • Advanced Member
  • 104 posts
  • LocationEngland

Posted 23 October 2006 - 10:12 AM

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

<?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"] );
}
?>

Many Thanks

If it all gets too much just remember that, at the end of the day... It gets dark!! or that on the other hand... There are more Fingers!!
If you eventually get it right then Hip Hip ARRAY!! lol


#5 gmwebs

gmwebs
  • Members
  • PipPipPip
  • Advanced Member
  • 174 posts
  • LocationLondon

Posted 23 October 2006 - 10:18 AM

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

#6 argoSquirrel

argoSquirrel
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 24 October 2006 - 07:54 AM

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:

SELECT TOP 1 title, body, etc...
FROM News
ORDER BY submit_date DESC


#7 Round

Round
  • Members
  • PipPipPip
  • Advanced Member
  • 104 posts
  • LocationEngland

Posted 24 October 2006 - 09:01 AM

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.

If it all gets too much just remember that, at the end of the day... It gets dark!! or that on the other hand... There are more Fingers!!
If you eventually get it right then Hip Hip ARRAY!! lol





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users