Pagination Problem

[graham23s]

Hi Guys,

 

I normally use the same pagination code in different pages just change a few queries here and there, this one is giving me some trouble:

 

<?php
  case "wwham":
  
  // ================================================================================== //
  // pagination start
  // ================================================================================== //
  
  if(!isset($_GET['page'])){ 
 $page = 1; 
     } else { 
 $page = $_GET['page']; 
  } 
    
  $max = 5; 
  $num = $page * $max - $max;  

  // ================================================================================== //
  // pagination start
  // ================================================================================== //
  
   // query
   $qW = "SELECT * FROM `fcp_wwham` ORDER BY `DATE_ADDED` LIMIT $num, $max";
   $rW = mysql_query($qW);
   $nW = mysql_num_rows($rW);   
   
   // any results
   if (($nW) > 0)
   {
   
      // DISPLAY WHAT QUESTIONS
      print("<table width='95%' border='0' cellpadding='5' cellspacing='1' class='tbl_login' />\n");
      print("<tr>\n");
      print("<td colspan='3' align='left' class='c3'><b>WWHAM Questions</b></td>\n");
      print("</tr>\n"); 
      print("<tr>\n");
      print("<td align=\"center\" class='c3'><b>Customer Name</b></td><td align=\"center\" class='c3'><b>Date</b></td><td align=\"center\" class='c3'><b>Action</b></td>\n");
      print("</tr>\n");
      print("<tr class=\"c5\">\n");
      print("<td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td><td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td><td align=\"left\"><img src=\"images/pixel.gif\" width=\"1\" height=\"1\"></td>\n");
      print("</tr>\n");
      
      // loop
      while ($aW = mysql_fetch_array($rW))
      {
      
      // vars
      $c_wi = $aW['ID'];
      $c_id = $aW['CUSTOMER_ID'];
      $c_dt = $aW['DATE_ADDED'];
      
      // format date
      $date_formatted = date("F j, Y, g:i a", strtotime($c_dt)); 
      
      // query to get the customers name
      $q_n = "SELECT * FROM `fcp_customers` WHERE `id`='$c_id'";
      $r_n = mysql_query($q_n);
      $a_n = mysql_fetch_array($r_n);
      
      $fname = ucwords($a_n['first_name']);
      $lname = ucwords($a_n['last_name']);
      
      // alternate border color
      $row_color = ($row_color == "class=\"no_color\"") ? "class=\"c1\"" : "class=\"no_color\"";
      
      // print rows
      print("<tr $row_color><td align=\"center\"><a class=\"smart_links\" href=\"admin.php?page=view_customers&customer_id=$c_id\">$fname $lname</a></td><td align=\"center\">$date_formatted</td><td align=\"center\"><a class=\"smart_links\" href=\"admin.php?page=view-wwham-id&wwham-id=$c_wi\">View</a> / Delete</td></tr>");
      
      } // end while
      
      print("</table><br />\n");
      
  // ================================================================================== //
  // pagination end
  // ================================================================================== //
  $totalpage     = ceil($nW/$max) + 1;  
  $total_results = ceil($nW/$max);
  
  $prevlink = ($page - 1);
  $nextlink = ($page + 1);
  
  // styling //
  print("<div id='container'>");
  
  // previous link //
  if($page > 1) { 
        echo "<a class=\"page_links\" href=\"admin.php?page=wwham&page=$prevlink\"><<<</a></span> \n"; 
  } 
    
  for($i=1; $i < $totalpage; $i++) { 
    
        if ($i == $page) { 
            echo "<span class=\"page_links\">$i</span> \n"; 
        } else { 
            echo "<a class=\"page_links\" href=\"admin.php?page=wwham&page=$i\">$i</a></span> \n"; 
        } 
  } 

  if($page < $totalpage - 1) { 
        echo "<a class=\"page_links\" href=\"admin.php?page=wwham&page=$nextlink\">>>></a></a>\n"; 
  }   
  
  print("</div><br />");
  // ================================================================================== //
  // pagination end
  // ================================================================================== //
?>

 

this is the problem part:

 

$qW = "SELECT * FROM `fcp_wwham` ORDER BY `DATE_ADDED` LIMIT $num, $max";

 

when echoed back it produces:

 

SELECT * FROM `fcp_wwham` ORDER BY `DATE_ADDED` LIMIT -5, 5

 

the main error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

 

i know the query is the problem i'm not sure why though! when i change the value of $max the pagination links at the bottom are printed correctly, the query seems to be the main problem!

 

thanks for any help guys

 

Graham

[lanmonkey]

$qW = "SELECT * FROM `fcp_wwham` ORDER BY `DATE_ADDED` LIMIT $num, $max";

 

those are backticks in the query string, I think they should be single quotes.

 

in PHP a backtick is for executing shell commands

 

try this:

 

$qW = "SELECT * FROM 'fcp_wwham' ORDER BY 'DATE_ADDED' LIMIT $num, $max";

 

I also think you will get away with removing the single quotes altogether.

 

Failing all this try echoing out the quesry tring and pasting it into phpmyadmin and seing what it does.

[graham23s]

Hi Ian,

 

Thnaks mate, tried that to still the same for some reason, the answer is probably staring me in the face aswell lol

 

cheers

 

Graham

[ToonMariner]

<?php if(!isset($_GET['page'])){ 
 $page = 1; 
     } else { 
 $page = $_GET['page']; 
  } 
    
  $max = 5; 
  $num = $page * $max - $max;

....
?>

 

change that to...

<?php if(empty($_GET['page'])){ 
 $page = 1; 
     } else { 
 $page = $_GET['page']; 
  } 
    
  $max = 5; 
  $num = (--$page) * $max;

....
?>

 

 

[graham23s]

Thanks guys sorted

 

Graham

[.josh]

Your query is failing because your offset is a negative number.  Table rows start at 0.  I didn't look at the rest of your code to find out how you arrived at a negative number, but I'm assuming there's some logic bug somewhere that generates that.  I'd start with how your $page is generated.  Even after that, you need to further validate your $page from the GET var.  Your validation is to simply assign 1 to it if it's not set.  What if it's a negative number, like right now (bug in your script)? Even after you fix the bug, people can enter in negative numbers from the url and the same thing will happen.  Or worse.  Your script throws it directly into your query.  No checking if it's a valid number in a valid range, if it's some arbitrary value, nothing.  Read up on sql injection.