Select next row in php

Question

Hello,

I have a set of records in a mysql database and am trying to make select next and previous buttons on a webpage. I have issues coding it in php however; any help is greatly appreciated.

My records do not have auto increment id attached to them.

My code is as follows:

$result = mysql_query($query) or die("Couldn't execute query");

$row_number = 1;

while ($row = mysql_fetch_assoc($result)) { 
$row_number ++;

if ($row_number <= mysql_num_rows($result)) {
$nextRecord=mysql_data_seek($result,$row_number);

    while ($nextRow = mysql_fetch_row($nextRecord)) {
        echo $nextRow['family_accession_number'];}

when I try to run the script, the following message came out:

Warning: mysql_data_seek() [function.mysql-data-seek]: Offset 2 is invalid for MySQL result index 3 (or the query data is unbuffered) in C:\xampp\htdocs\survey\continuetitle.php on line 23

Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\survey\continuetitle.php on line 25

Line 23 is: $nextRecord=mysql_data_seek($result,$row_number);

Thanks!

EDIT: I changed while ($nextRow = mysql_fetch_row($nextRecord)) { to

while ($nextRow = mysql_fetch_row($result)) { 

but the same error occurs on line 23

EDIT:

Found error in SQL query which returns only one row.

Answer 1

try

$nextRecord=mysql_data_seek($result,$row_number-1);

Answer 2

This method means that the database much fetch and return the entire data set to the client (where the seek is carried out). A much efficient solution would be to use the LIMIT clause to make the database return only a single row to the client:

$select_qry=$select_qry . " LIMIT $offset,1";

C.

Answer 3

Well, it's a long time since I use direct database access using mysql_ functions. I'm using AdoDB and I suggest you try it by yourself. Your above code if rewritten using AdoDB will be like this:

//create a connection
$adodb = &ADONewConnection($dsn);
$adodb->SetFetchMode(ADODB_FETCH_BOTH);

//Execute a query
$rs =& $adodb->Execute($query);
if ( ! $rs )
{
  echo 'Error occured: ' . $adodb->ErrorMsg();
  die();
}
//check if query return any row
if ( $rs->RecordCount() > 0 )
{
  while ( $row = $rs->FetchRow() )
  {
    echo $row['family_accession_number'];
  }
}

If you want to have prev and next link, then you must have a page handling for it. For example, in the following code, it will return only 10 row, and the page will need to have prev and next link to get the next or previous 10 row of data

//get page parameter
$page = intval($_GET['page']);
if ( $page <= 0 )
{
  $page = 1;
}
//default value
$per_page = 10;
//calculate start from the $page value
$offset = ($page - 1) * $per_page;

//create a connection
$adodb = &ADONewConnection($dsn);
$adodb->SetFetchMode(ADODB_FETCH_BOTH);

//execute query
$rs =& $adodb->SelectLimit($query, $per_page, $offset);
if ( ! $rs )
{
  echo 'Error occured: ' . $adodb->ErrorMsg();
  die();
}
//check if query return any row
if ( $rs->RecordCount() > 0 )
{
  while ( $row = $rs->FetchRow() )
  {
    echo $row['family_accession_number'];
  }
  //print prev and next link
  echo '<a href="THIS_PAGE_URL?page='.($page-1).'">Prev</a> ';
  echo '<a href="THIS_PAGE_URL?page='.($page+1).'">Next</a> ';
}
else
{
  echo 'No result';
}

I hope this is what you want to do.