Pagination Error

[timmah1]

I get this error when I try to run this script

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 '-1,1' at line 1

 

It worked before I changed the table name

here is the code

<?php
require("config.php");

//This checks to see if there is a page number. If not, it will set it to page 1
if (!(isset($pagenum)))
{
$pagenum = 1;
$id = 2;
}

//Here we count the number of results
//Edit $data to be your query
$data = mysql_query("SELECT * FROM customers") or die(mysql_error());
$rows = mysql_num_rows($data);

//This is the number of results displayed per page
$page_rows = 1;

//This tells us the page number of our last page
$last = ceil($rows/$page_rows);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
$id = 2;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

//This is your query again, the same one... the only difference is we add $max into it
$data_p = mysql_query("SELECT * FROM customers $max") or die(mysql_error());

//This is where you display your query results
while($info = mysql_fetch_array( $data_p ))
{
$clientID = $info['id'];
?>
<form name="view" method="post" action="">
<table width="575" border="0" align="center" cellpadding="3" cellspacing="0">
  <tr>
    <td width="100">Client Name</td>
    <td><input name="client" type="text" id="client" value="<?=$info['client']; ?>" readonly="true" /></td>
    <td>Client ID</td>
    <td><input name="clientid" type="text" id="clientid" value="<?=$info['clientID']; ?>" readonly="true" /></td>
  </tr>
  <tr>
    <td>Contact Name</td>
    <td><input type="text" name="clientName" id="clientName" value="<?=$info['clientName']; ?>" readonly="true" /></td>
    <td>Date</td>
    <td><input type="text" name="date" id="date" value="<?=$info['date']; ?>" readonly="true" /></td>
  </tr>
  <tr>
    <td>Address</td>
    <td><input type="text" name="address" id="address" value="<?=$info['address']; ?>" readonly="true" /></td>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td colspan="4"><table width="100%" border="0" cellspacing="0" cellpadding="3">
      <tr>
        <td width="100">City</td>
        <td><input name="city" type="text" id="city" size="20" value="<?=$info['city']; ?>" readonly="true" /></td>
        <td>State</td>
        <td><input name="state" type="text" id="state" size="10" maxlength="11" value="<?=$info['state']; ?>" readonly="true" /></td>
        <td>Zip</td>
        <td><input name="zip" type="text" id="zip" size="10" maxlength="11" value="<?=$info['zip']; ?>" readonly="true" /></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td colspan="4"><table width="100%" border="0" cellspacing="0" cellpadding="3">
      <tr>
        <td width="100">Company Phone</td>
        <td><input name="company" type="text" id="company" size="13" maxlength="12" value="<?=$info['company']; ?>" readonly="true" /></td>
        <td>Ext.</td>
        <td><input name="ext" type="text" id="ext" size="6" maxlength="5" value="<?=$info['ext']; ?>" readonly="true" /></td>
        <td>Cell Phone</td>
        <td><input name="cell" type="text" id="cell" size="13" maxlength="12" value="<?=$info['cell']; ?>" readonly="true" /></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td width="100">Fax</td>
    <td><input name="fax" type="text" id="fax" size="13" maxlength="12" value="<?=$info['fax']; ?>" readonly="true" /></td>
    <td>Email</td>
    <td><input type="email" name="email" id="email" value="<?=$info['email']; ?>" readonly="true" /></td>
  </tr>
  <tr>
    <td width="100" align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
    <td align="left" valign="top"> </td>
  </tr>
  <tr>
    <td width="100" align="left" valign="top">Level of service</td>
    <td align="left" valign="top"><input name="level" type="text" id="level" size="8" value="<?=$info['level']; ?>" readonly="true" /></td>
    <td align="left" valign="top">Description</td>
    <td align="left" valign="top"><textarea name="description" cols="20" rows="5" id="description" readonly="true" /><?=$info['description']; ?></textarea></td>
  </tr>
  <tr>
    <td colspan="4"><table width="100%" border="0" cellspacing="0" cellpadding="3">
      <tr>
        <td width="100">Tax %</td>
        <td><input name="tax" type="text" id="tax" size="4" maxlength="3" value="<?=$info['tax']; ?>" readonly="true" /></td>
        <td>Late Fee Days</td>
        <td><input name="latedays" type="text" id="latedays" size="4" maxlength="3" value="<?=$info['latedays']; ?>" readonly="true" /></td>
        <td>Amount $</td>
        <td><input name="amount" type="text" id="amount" size="6" maxlength="6" value="<?=$info['amount']; ?>" readonly="true" /></td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td colspan="4"><table width="100%" border="0" cellspacing="0" cellpadding="3">
      <tr>
        <td width="100">Terms</td>
        <td><input name="terms" type="text" id="terms" size="6" maxlength="6" value="<?=$info['terms']; ?>" readonly="true" /></td>
        <td>Day to send</td>
        <td><input name="days" type="text" id="days" size="6" maxlength="6" value="<?=$info['days']; ?>" readonly="true" /></td>
        <td>Active?</td>
        <td><input <?php if (!(strcmp($info['active'],"yes"))) {echo "checked=\"checked\"";} ?> type="radio" name="active" id="active" value="yes" readonly="true" />
          Yes 
            <input <?php if (!(strcmp($info['active'],"no"))) {echo "checked=\"checked\"";} ?> type="radio" name="active" id="active" value="no" readonly="true" /> 
            No</td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td colspan="4" align="center"><table border="0" cellspacing="0" cellpadding="6">
      <tr>
        <td align="center" valign="top"><input type="submit" name="add" id="add" value="Add New Client"></td>
        <td align="center" valign="top"><input type="submit" name="edit" id="edit" value="Edit Client" /></td>
        <td align="center" valign="top"><input type="submit" name="delete" id="delete" value="Delete Client" />
        <input type="hidden" name="clientID" id="clientID" value="<?=$info['id']; ?>" />
        <input type="submit" name="invoice" id="invoice" value="Print Invoice" />
        </td>
      </tr>
    </table></td>
  </tr>
  <tr>
    <td colspan="4" align="center">
<?php
}
echo "<p>";

// This shows the user what page they are on, and the total number of pages
echo " --Page $pagenum of $last-- <p>";

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}orders.php?id=$info[id]&pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}orders.php?id=$info[id]&pagenum=$previous'> <-Previous</a> ";
}

//just a spacer
echo " ---- ";

//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}orders.php?id=$info[id]&pagenum=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}orders.php?id=$info[id]&pagenum=$last'>Last ->></a> ";
}
?>
    </td>
  </tr>
</table>
</form>

 

Can anybody tell me why this is happening?

 

Thanks in advance

[flyhoney]

Somehow,

($pagenum - 1) * $page_rows

 

is negative.  On this line:

 

$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

 

You can't have a negative limit in an SQL query.

[timmah1]

I guess what I don't understand then, is why does this one work?

$data = mysql_query("SELECT * FROM clients") or die(mysql_error());
$rows = mysql_num_rows($data);

//This is the number of results displayed per page
$page_rows = 1;

//This tells us the page number of our last page
$last = ceil($rows/$page_rows);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
$id = 2;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

 

When all I did was change table names?

[flyhoney]

Maybe this line?

 

$last = ceil($rows/$page_rows);

 

Are you sure the SQL query is not returning an empty result set?

[Maq]

Where are you getting $pagenum from?  Can you add some echos to make sure you have correct values?

 

echo "pagenum" . $pagenum . "
last" . $last;

//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;

[timmah1]

Are you sure the SQL query is not returning an empty result set?

 

Yup.

Didn't realize nothing was in the database until you said something.

Thanks again