[Resolved] Paging and ORDER BY DESC

[crash3909]

Hi,

I am using ORDER BY column_name DESC, and have paging code at the same time. The problem is, that although the data is fetched properly in descending order, what is printed on the last page I want on the first page. Here is the query and I don't know what else you would need, since the paging code is long. How can I make this work?

mysql_query("SELECT * FROM table_name ORDER BY column_name DESC LIMIT ".$start.", ".$limit);

Thank you in advance.

[bradgrafelman]

Now wait just a minute.

It's correctly fetching it in descending order, like you want it to? But you want the info on the last page to be on the first? I'm either going insane, or you're telling me you want the order flipped, meaning ascending...

Give us some example, could you?

[crash3909]

Yes... it is enough to drive one insane

I will include a link to the page (in testing phase) that I am working on.

http://www.graywig.com/guestbook/guestbook.php

The entry dated 2005-04-14 needs to be at the very top of the very first page. The way it is now -- using DESC, it is the first entry of the second page (last page - for now). When I use ASC, the entry dated 2005-04-14 is the on the first page, but it is last entry on that page. Once again, I need this on the top of the first page. I hope this cleared up the insanity.

Thanks

[liquorvicar]

can you post the relevant pagination code from your script? i think what you're trying to achieve should be the default behaviour with ORDER BY ... DESC.

what you currently have there is odd and not what i would normally expect. i suspect there may be an error in your pagination code.

[bradgrafelman]

Switch it to ASC order and let us know when you've done that... I'd like to see this behavior you're describing.

[crash3909]

Here is the paging code. The first section is output page, the second is the class php file.

Thanks again

  include ("config.php");
	$link = mysql_pconnect($gb_type, $gb_user, $gb_password)
		or die("Could not connect: " . mysql_error());
	mysql_select_db( $gb_db ) or die ( "Couldn't open $db: ".mysql_error() );
/* Instantiate class */ 
require_once("class.pager.php"); 
$p = new Pager; 

/* Show many results per page? */ 
$limit = 10; 

/* Find the start depending on $_GET['page'] (declared if it's null) */ 
$start = $p->findStart($limit); 

/* Find the number of rows returned from a query; Note: Do NOT use a LIMIT clause in this query */ 
$count = mysql_num_rows(mysql_query("SELECT * FROM Guestbook")); 

/* Find the number of pages based on $count and $limit */ 
$pages = $p->findPages($count, $limit); 

/* Now we use the LIMIT clause to grab a range of rows */ 
$result = mysql_query("SELECT * FROM Guestbook ORDER BY id DESC LIMIT ".$start.", ".$limit); 

/* Now get the page list and echo it */ 
$pagelist = $p->pageList($_GET['page'], $pages); 
echo $pagelist;

print "<font class=\"Text\">$header</font><br><br>";

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {
	if (!mysql_data_seek($result, $i)) {
		echo "Cannot seek to row $i: " . mysql_error() . "\n";
	continue;
	}
if(!($row = mysql_fetch_object($result)))
	continue;
	echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
	<tr><td bgcolor=\"#3366FF\"><strong><font class=\"HText\">$row->Date</font></strong></td></tr>
	<tr><td><font class=\"Text\">$row->Name</font></td></tr>
	<tr><td><a href=\"mailto:$row->Email\"><font class=\"Text\">$row->Email</font></a></td></tr>
	<tr><td><i><font class=\"Text\">$row->Message</font></i></td></tr>
	</table></font><br>";
}
mysql_free_result($result);
mysql_close( $link );
print "<br><font class=\"Text\">$footer</font><br>";

---

Class

class Pager 
  { 
//  int findStart (int limit) 
//  Returns the start offset based on $_GET['page'] and $limit 

   function findStart($limit) 
    { 
     if ((!isset($_GET['page'])) || ($_GET['page'] == "1")) 
      { 
       $start = 0; 
       $_GET['page'] = 1; 
      } 
     else 
      { 
       $start = ($_GET['page']-1) * $limit; 
      } 

 return $start; 
} 
// int findPages (int count, int limit) 
// Returns the number of pages needed based on a count and a limit 

   function findPages($count, $limit) 
    { 
     $pages = (($count % $limit) == 0) ? $count / $limit : floor($count / $limit) + 1; 

 return $pages; 
} 
// string pageList (int curpage, int pages) 
// Returns a list of pages in the format of "« < [pages] > »" 

   function pageList($curpage, $pages) 
    { 
     $page_list  = ""; 

 // Print the first and previous page links if necessary
 if (($curpage != 1) && ($curpage)) 
  { 
   $page_list .= "  <a href=\"".$_SERVER['PHP_SELF']."?page=1\" title=\"First Page\">«</a> "; 
  } 

 if (($curpage-1) > 0) 
  { 
   $page_list .= "<a href=\"".$_SERVER['PHP_SELF']."?page=".($curpage-1)."\" title=\"Previous Page\"><</a> "; 
  } 

 // Print the numeric page list; make the current page unlinked and bold
 for ($i=1; $i<=$pages; $i++) 
  { 
   if ($i == $curpage) 
    { 
     $page_list .= "<b>".$i."</b>"; 
    } 
   else 
    { 
     $page_list .= "<a href=\"".$_SERVER['PHP_SELF']."?page=".$i."\" title=\"Page ".$i."\">".$i."</a>"; 
    } 
   $page_list .= " "; 
  } 

 // Print the Next and Last page links if necessary
 if (($curpage+1) <= $pages) 
  { 
   $page_list .= "<a href=\"".$_SERVER['PHP_SELF']."?page=".($curpage+1)."\" title=\"Next Page\">></a> "; 
  } 

 if (($curpage != $pages) && ($pages != 0)) 
  { 
   $page_list .= "<a href=\"".$_SERVER['PHP_SELF']."?page=".$pages."\" title=\"Last Page\">»</a> "; 
  } 
 $page_list .= "</td>\n"; 

 return $page_list; 
} 
// string nextPrev (int curpage, int pages) 
// Returns "Previous | Next" string for individual pagination (it's a word!) 

   function nextPrev($curpage, $pages) 
    { 
     $next_prev  = ""; 

 if (($curpage-1) <= 0) 
  { 
   $next_prev .= "Previous"; 
  } 
 else 
  { 
   $next_prev .= "<a href=\"".$_SERVER['PHP_SELF']."?page=".($curpage-1)."\">Previous</a>"; 
  } 

 $next_prev .= " | "; 

 if (($curpage+1) > $pages) 
  { 
   $next_prev .= "Next"; 
  } 
 else 
  { 
   $next_prev .= "<a href=\"".$_SERVER['PHP_SELF']."?page=".($curpage+1)."\">Next</a>"; 
  } 

 return $next_prev; 
} 
  }

[liquorvicar]

in your code, you've got

mysql_query("SELECT * FROM Guestbook ORDER BY id DESC LIMIT ".$start.", ".$limit);

i was assuming you wanted to sort on the date field in your table, not the ID field?

[crash3909]

Sorry for the confusion....

I just redid the test database and called each entry Test1, Test2, etc. I also change the limit per page from 10 to 5 entries.

http://www.graywig.com/guestbook/guestbook.php

However, on the output page, nobody really sees the id, but the id should correspond with the Date when it comes to specific order.

Test10 is the most recent entry and this entry I would like to be the very top entry on the first page.

As of this posting, the query puts Test10 on the bottom of the first page, and it needs to be at the top of this page. Here is the query:

$result = mysql_query("SELECT * FROM Guestbook ORDER BY id DESC LIMIT ".$start.", ".$limit);

I did a db-dump and copied it here. The columns are:

id, Date, Name, Email, Message

INSERT INTO Guestbook VALUES (1, '2005-04-19 09:20:07', 'Rich', '[e-mail]', 'Test1');
INSERT INTO Guestbook VALUES (2, '2005-04-19 09:20:48', 'Rich', '[e-mail]', 'Test2');
INSERT INTO Guestbook VALUES (3, '2005-04-19 09:21:53', 'Rich', '[e-mail]', 'Test3');
INSERT INTO Guestbook VALUES (4, '2005-04-19 09:22:25', 'Rich', '[e-mail]', 'Test4');
INSERT INTO Guestbook VALUES (5, '2005-04-19 09:22:46', 'Rich', '[e-mail]', 'Test5');
INSERT INTO Guestbook VALUES (6, '2005-04-19 09:23:13', 'Rich', '[e-mail]', 'Test6');
INSERT INTO Guestbook VALUES (7, '2005-04-19 09:24:18', 'Rich', '[e-mail]', 'Test7');
INSERT INTO Guestbook VALUES (8, '2005-04-19 09:24:38', 'Rich', '[e-mail]', 'Test8');
INSERT INTO Guestbook VALUES (9, '2005-04-19 09:25:38', 'Rich', '[e-mail]', 'Test9');
INSERT INTO Guestbook VALUES (10, '2005-04-19 09:26:01', 'Rich', '[e-mail]', 'Test10');

[liquorvicar]

can you echo out the SELECT statement at the top of each page?
i just set up a test table mimicking your structure and phpMyAdmin is giving me the right results.

[crash3909]

Yep.... I used a SELECT query in my admin, and the output was correct. I tried your suggestion and used ECHO at the top of each page. No luck, unless someone has the right code for it. It seems that it is definitely a problem in how it prints, I'm just not sure what to fix.

Any more suggestions are very welcome... Thanks

[liquorvicar]

it could be this line:

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {

you're looping backwards through the results.
why don't you try this:

while ($row = mysql_fetch_object($result)) {
        echo "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
        <tr><td bgcolor=\"#3366FF\"><strong><font class=\"HText\">$row->Date</font></strong></td></tr>
        <tr><td><font class=\"Text\">$row->Name</font></td></tr>
        <tr><td><a href=\"mailto:$row->Email\"><font class=\"Text\">$row->Email</font></a></td></tr>
        <tr><td><i><font class=\"Text\">$row->Message</font></i></td></tr>
        </table></font><br>";
    } 

[crash3909]

Success... thank you