Accessing records in descending order in database

Question

retrieving the enteries :

  <?php 
   $query1 = "SELECT * FROM table2 WHERE id='{$user}' order by srno desc " ;
   $result1=mysql_query($query1,$con);
   if (!($result1) )
   {
      die('Error: ' . mysql_error($con));
   }
   else
   {
      $values1= mysql_fetch_array($result1);
   }
    mysql_close($con);
  ?>

for descending order display I am doing this:

while($values1)
{
  echo $row['srno'];
  echo " " . $row['Symptoms'];
  echo "<br>";
}

This makes all the records to be displayed on the same page in descending order. How do i make them display on different pages.Is there a way of accessing values of array values1 using index so that I can manipulate different records on different pages.

score 1 · Accepted Answer · answered Nov 06 '13 at 03:52

If you wanna display different results on different pages, you can always filter them in the query with limit and offset:

 // In PAGE 1
$query1 = "SELECT * FROM table2 WHERE id='{$user}' order by srno desc LIMIT 10" ; 


 // In PAGE 2
$query2 = "SELECT * FROM table2 WHERE id='{$user}' order by srno desc LIMIT 10 OFFSET 10" ;

Note: This is called server side pagination.

Edit: It is clear you should never write that query more than once, automate the pagination process and make it add limit of offset to the query in the php script according to the parameters it gets. Then, you would call the php script and pass it a parameter such as

         myPHPScript.php?page=2

By the way, in your code, you are only displaying the first result. You should do:

          while ($row = mysql_fetch_array($result1, MYSQL_NUM)) {

          }

thanks @fos.alex ! But If i am redirecting to same page just query2 must work. Am I right ? If not please clarify — Mithilesh, Nov 06 '13 at 04:02

score 0 · Answer 2 · edited May 23 '17 at 12:21

You can't use a result set like that across pages. You'd have to select the single result you want to use on a page, then link the next/previous page with the next/previous result's ID in the link, then select that result on the next page.

To get the next and previous ids from your current result, have a look at this post.

You'll probably want to check that the next/previous results exist before outputting a link to them as well.

Accessing records in descending order in database

2 Answers2