[SOLVED] retrieving last record from mysql

[MikeDXUNL]

$result =  mysql_query("SELECT * FROM news WHERE date <= NOW()") or die(mysql_error());

 

id | title

[1] result 1

[2] result 2

[3] result 3

[4] result 4

 

i need the last row retrieved info ([id:4]).. but i don't want to write two separate queries... help please?

 

[rhodesa]

$result =  mysql_query("SELECT * FROM news WHERE date <= NOW() ORDER BY id DESC LIMIT 1") or die(mysql_error());

[dclamp]

Try this:

 

$result =  mysql_query("SELECT * FROM news WHERE date <= NOW()") or die(mysql_error());

//will get the last row.
$last = $result[(count($result)-1)];

[MikeDXUNL]

yeah i knew that, but like i said; i dont want to use two seperate queries. i am also using $result to display all the data on the page.

 

and dclamp;

your method would work, but some id's are not displayed with my query.

some items in the database are > NOW() so $last would not always equal the last id selected from the database

[rhodesa]

then move all of them into an array first....

 

$result =  mysql_query("SELECT * FROM news WHERE date <= NOW()") or die(mysql_error());
$data = array();
while($row = mysql_fetch_array($result)){
  $data[] = $row;
}
$last = $data[count($data)-1];

[dclamp]

then move all of them into an array first....

 

$result =  mysql_query("SELECT * FROM news WHERE date <= NOW()") or die(mysql_error());
$data = array();
while($row = mysql_fetch_array($result)){
  $data[] = $row;
}
$last = $data[count($data)-1];

 

ah missed that part...

[Psycho]

Actually, I don't think dclamps code would work. It is using a count() on a mysql result resource. Should be using a mysql_num_rows(). But, you could use something like that without dumping the results into an array first:

 

$result =  mysql_query("SELECT * FROM news WHERE date <= NOW() ORDER BY id ASC") or die(mysql_error());

//Add last record to a new array
$last_record = mysql_result($result, mysql_num_rows($result)-1);

//Run through all results from query
while ($record = mysql_fetch_assoc($result))
{
    // Do something
}

[dclamp]

when you use mysql_fetch_array() it comes back as an array, then you can use count() to get the last row.

[Psycho]

when you use mysql_fetch_array() it comes back as an array, then you can use count() to get the last row.

 

mysql_fetch_array() fetches one record as an array - not the entire result set. The OP is looking for the last record int he result set.

[rhodesa]

when you use mysql_fetch_array() it comes back as an array, then you can use count() to get the last row.

 

mysql_fetch_array() fetches one record as an array - not the entire result set. The OP is looking for the last record int he result set.

 

i tried giving the OP a query for the last record, but they wanted ALL the records AND the last record. that is why you have to do the loop and then use count(). besides...the OP marked this SOLVED

[Psycho]

No disrespect intended. I was just making a correction (you can't use count() on a result set as posted in Reply #2, was not referring to the array solution) and showing another alternative to get both all the records and the last record independantly without dumping the results into an array and without a 2nd query.