Pagination query error

[graham23s]

Hi GUys,

 

im getting a mysql_num_rows errors from my pagination query:

 

<?php
  case "edit_products";
  
  ###############################################
  # Pagination start
  ###############################################
  if(!isset($_GET['page'])){ 
 $page = 1; 
     } else { 
 $page = $_GET['page']; 
  } 
    
  $max = 10; 
  $num = $page * $max - $max;  
  ###############################################
  # Pagination start
  ###############################################

  // do a query to get the products //
  $q_prods = "SELECT * FROM `fcp_products`";
  $r_prods = mysql_query($q_prods);
  $n_prods = mysql_num_rows($r_prods);

  // pagination query //
  $q_pagination = "SELECT * FROM `fcp_products` LIMIT $num, $max"; 
  $r_pagination = mysql_query($q_pagination);
  $t_pagination = mysql_num_rows($r_pagination);

  if(($n_prods) == 0)
  {
   standard_message("Error","No products to display.");   
  }
  
   // display them //
  print("<table width='750' border='0' cellpadding='5' cellspacing='1' />\n");
  print("<tr>\n");
  print("<th colspan='4'>Products - [$n_prods]</th>\n");
  print("</tr>\n"); 
  print("<tr>\n");
  print("<th>ID</th><th>Product</th><th>Product Name</th><th>Action</th>\n");
  print("</tr>\n");  
  
  // loop //
  while($row = mysql_fetch_array($r_prods))
  {
   // vars //
   $p_id = $row['id'];
   $p_name = $row['product_name'];
   $p_thumb = $row['product_thumbnail'];
  
  print("<tr><td class='td_style' align='center'>$p_id</td><td class='td_style' align='center'><img src='products/thumbnails/$p_thumb'></td><td class='td_style' align='center'>$p_name</td><td class='td_style' align='center'>[<a class='smart_links' href='admin.php?page=master_edit&productid=$p_id'>EDIT</a>]-[<a class='smart_links' href='admin.php?page=master_delete&productid=$p_id'>DELETE</a>]-[<a class='smart_links' href='admin.php?page=master-disable&productid=$p_id'>DISABLE</a>]</td></tr>");
  
  }   
  // end the table //
  print("<tr>\n");
  print("<td colspan='4' class='td_style' align='right'>&nbsp</td>\n");
  print("</tr>\n");
  print("</table><br />");
  
  ###############################################
  # Pagination end
  ###############################################

  ###############################################
  # Pagination end
  ###############################################

  break;
?>

 

$t_pagination = mysql_num_rows($r_pagination);

 

this line is giving me the error! i cant see why though!

 

thanks guys

 

Graham

[cooldude832]

you need to run all queries in a method similar to

<?php
$q = "Select * from `table`";
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);
?>

 

odds are you have a mysql error and you aren't verifying your query so.

[graham23s]

hmm added the or die and got:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-10, 10' at line 1

 

not sure exactly what the error is though

 

cheers mate

 

Graham

[cooldude832]

if u added it like I typed it it should print out the whole query with all the variables supplied can I see it please

[graham23s]

doh sorry, here is the query printed to the screen:

 

SELECT * FROM `fcp_products` LIMIT -10, 10

 

cheers mate

 

Graham

[cooldude832]

when using the operation of LIMIT on a query the first number is the position in the return rows to start and the second is the number of rows to return (if 1 parameter is supplied the single parameter is the number of rows)

 

How can you start at row -10?

[graham23s]

i know, where the heck is -10 coming from?

 

is this part wrong: $num = $page * $max - $max;

 

cheers

 

Graham

[cooldude832]

you aren't checking if $page is a valid number

 

What you should have to verify is more like

<?php
$page = $_GET['page'];
if(intval($page) < 1){
$page = 1;
}
?>

Then think about your math

if I'm on page 1 and my items per page = 10 I want to view items 0 to 9  so my LIMIT is LIMIT 0, 10 

The second parameter to limit is always the same so don't worry about that

to get my 0 I need to say

<?php
$max = 10;
$lim = $page*$max;
$lim = $lim-$max;
?>

(same math just spread out)

 

Odds are you have a $page = 0  or less than 0 thus 0*10-10 = -10

[.josh]

http://www.phpfreaks.com/tutorial/basic-pagination