Fetch Data from Last Row

[xProteuSx]

I am writing a PHP script that determines the number of rows in a mysql table, then goes to the last row and retrieves data from that row.  I have written a script which determines the number or rows, but how do I get it to read data from the last row specifically?

[rhodesa]

By 'script which determines the number of rows' I hope you are using mysql_num_rows()

 

To move to a specific row, use mysql_data_seek()

[xProteuSx]

So, if $numberofrows is the number of rows in a table I can get the data as follows??

 

$specificresult = mysql_query(mysql_data_seek($numberofrows)) or die (mysql_error());

 

I have never used this function before, and the official manual is confusing the crap out of me.

[rhodesa]

no, it would be:

 

<?php
  $sql = "SELECT * FROM `tablename` ORDER BY `somefield`";
  $result = mysql_query($sql);
  $num = mysql_num_rows($result);
  if($num){
    mysql_data_seek($result,$num-1)
    $last = mysql_fetch_array($result);

    echo $last['somefield'];
  }
?>

[frijole]

thats a handy little function.

[Barand]

Most efficient way to get number of rows in a table is

 

SELECT COUNT(*) FROM tablename

[sureshp]

Yes -- COUNT will be the best way to retrieve the no. of rows. Others taking time to execute where COUNT is not taking time to execute it in phpmyadmin .

 

[xProteuSx]

rhodesa, the code you have given me does not seem to function (even when I put the missing ';' following 'mysql_data_seek($result,$num-1)'.  I have a couple of questions, if you don't mind.

 

First of all, what does 'if($num)' do?  That does not seem like a proper boolean argument to me.  Am I missing something?  Second, '$last['handle]' comes back blank.  I have changed the tablename.

 

My table is entitled 'users' and I am looking to get the value of the 'handle' column for the last row.

 

I have it as ...

 

<?php

  $sql = "SELECT * FROM `users` ORDER BY 'id'";

  $result = mysql_query($sql);

  $num = mysql_num_rows($result);

  if($num){

    mysql_data_seek($result,$num-1)

    $last = mysql_fetch_array($result);

 

    echo 'The last registered member is: ' . $last['handle'];

  }

?>

[BlueSkyIS]

if you want the actual last record by somefield, just sort in desc and take the first record.

 

<?php
  $sql = "SELECT * FROM `tablename` ORDER BY `somefield` DESC";
  $result = mysql_query($sql) or die(mysql_error());
  $number_of_recs = mysql_num_rows($result);
  if ($number_of_recs) {
      $last = mysql_fetch_array($result);
      echo $last['somefield'];
  } else {
      echo "no records."
  }
?>

 

"what does 'if($num)' do?"

 

it's true if $num is greater than zero.

[rhodesa]

if you want the actual last record by somefield, just sort in desc and take the first record.

 

<?php
  $sql = "SELECT * FROM `tablename` ORDER BY `somefield` DESC";
  $result = mysql_query($sql) or die(mysql_error());
  $last = mysql_fetch_array($result);
  echo $last['somefield'];
?>

 

"what does 'if($num)' do?"

 

it's true if $num is greater than zero.

 

Agreed, with your stuff it would be:

 

<?php
  $sql = "SELECT * FROM `users` ORDER BY `id` DESC";
  $result = mysql_query($sql) or die(mysql_error());
  $last = mysql_fetch_assoc($result);
  echo 'The last registered member is: ' . $last['handle'];
?>

[xProteuSx]

Right on!  That did it.  I have yet to learn about 'mysql_fetch_assoc' so that's why I could not figure this out on my own.  Thanks very much.  This topic is now SOLVED ... speaking of which, wasn't there a 'Solved' button somewhere before??

[rhodesa]

mysql_fetch_array would have gotten you the same result, i just prefer _assoc