CODEWITHSUNDEEP
phpmysqlsql
charlie
charlie

Reputation: 1384

PHP/SQL Query find last result

I am running a while loop in PHP selecting data from a mysql database. How can i find out what the last record is,

for example:

$sql="SELECT * from table1 ";
$rs=mysql_query($sql,$conn);
while($result=mysql_fetch_array($rs))
{
echo $result["col1"].' - '.$result["col2"].'<br>';
}

then when it gets to the last record i want to display it like:

echo 'Last Record: '.$result["col1"].' - '.$result["col2"].'<br>';

Upvotes: 0

Views: 1740

Answers (5)

pilcrow
pilcrow

Reputation: 58524

Future-proof this routine by doing it the "hard way":

while ($next_row = fetch_row(...)) {
  if ($prev_row) { do_output($prev_row); }
  $prev_row = $next_row;
}
if ($prev_row) { do_output($prev_row, FLAG_IS_LAST_ROW); }

Why? Future maintenance might make mysql_num_rows() unreliable, either because your result set gets too big, or because you want to interface with a variety of SQL backends.

By default, the MySQL client library pulls the entire result set into memory — that is how it knows the number of rows SELECTed without having to count fetches. This behavior is rather convenient for small result sets, but devastating for large result sets. This it is user-configurable. (The options are usually named something like "store_result v. use_result" or "buffered v. unbuffered.")

Additionally, most RDBMS interfaces do not make the size of the result set known in advance. If you want to interface with these some day in a reusable way, you'll need to change your approach.

Upvotes: 0

BenM
BenM

Reputation: 53198

You basically need to record how many rows you have, and then set up a counter. You can do that using mysql_num_rows():

$sql="SELECT * from table1";
$rs = mysql_query($sql,$conn);
$numRows = mysql_num_rows($rs);

$i = 1;
while($result=mysql_fetch_array($rs))
{
    echo ($i == $numRows) ? 'Last Record: '.$result["col1"].' - '.$result["col2"].'<br />' : $result["col1"].' - '.$result["col2"].'<br />';
    $i++;
}

You should note though that the mysql_*() family of functions is now deprecated. For security and longevity, you really ought to be using MySQLi or PDO.

Upvotes: 2

jason ye
jason ye

Reputation: 1

$sql="SELECT * from table1 ";
$rs=mysql_query($sql,$conn);
$num_rows  =  mysql_num_rows ($rs);

for ($i=0; $i < $num_rows; $i++) { 
    $result=mysql_fetch_array($rs);
    if ($i == ($num_rows - 1)) {
        echo 'Last Record: '.$result["col1"].' - '.$result["col2"].'<br>';
    } else {
        echo $result["col1"].' - '.$result["col2"].'<br>';
    }
}

Upvotes: 0

Rogue
Rogue

Reputation: 11483

You can simply use the sql query itself to get the last value, based on whatever ordering you want (or just use DESC to get the bottom of the natural order):

SELECT * FROM table1
ORDER BY your_column DESC
LIMIT 1;

Edit: Since you're looking for the last row, you could check with mysql_num_rows

$numrows = mysql_num_rows($rs);
$i = 1;
// in while loop...
    if ($i === $numrows) {
        // print last result
    } else {
        // print normal result
    }
    $i++;
// end while loop

Essentially, you want a counter for the record you are on and then write when the number of rows is the same as the row number you are on (e.g. the last one)

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Get the total count of rows returned and check use a flag variable for the loop iterations and check in loop if flag == total rows

$t=mysql_num_row($rs);
$i=0;
while($result=mysql_fetch_array($rs))
{
$i++;

if($t == $i){
echo "Last Record ";
}
echo $result["col1"].' - '.$result["col2"].'<br>';
}

mysql_num_rows

Upvotes: 1

Related Questions