Error in syntax

[Russia]

This is a pagination script, with a delete function and is in a table. I am getting this error.

 

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

 

 

<?php
include("inc/config.php");
if (isset($_POST['del'])) 
{
  for ($count = 0;$count<count($_POST[delchk]);$count++)
   {
           $delete = $_POST[delchk][$count];
           $query = "DELETE FROM accounts WHERE id = '$delete'";
           $result = mysql_query($query);
           if (!$result) 
   {
            die("Error deleting accounts! Query: $query<br />Error: ".mysql_error());
        }
    }
}
  echo "<table class=\"gridtable\">
<thead>
    <tr>
        <th align=\"center\" scope=\"col\">Delete?</th>
        <th align=\"center\" scope=\"col\">Username</th>
        <th align=\"center\" scope=\"col\">Password</th>
        <th align=\"center\" scope=\"col\">Highscores</th>
        <th align=\"center\" scope=\"col\">Date</th>
        <th align=\"center\" scope=\"col\">IP Address</th>
        <th align=\"center\" scope=\"col\">Status</th>
    </tr>
</thead>
<tbody>";
echo "<form name = 'myform' action='' method='post'>";
$pagenum = (isset($_GET['pagenum'])) ? (int) $_GET['pagenum'] : 1;
$data = mysql_query("SELECT * FROM accounts") or die(mysql_error());
$rows = mysql_num_rows($data);
$page_rows = 4;
$last = ceil($rows/$page_rows);
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;
$data_p = mysql_query("SELECT * FROM accounts $max") or die(mysql_error());
while($info = mysql_fetch_array( $data_p ))
  {
  echo "<tr align=\"center\">";
  echo '<td><input type="checkbox" id="delchk" name="delchk[]" value="'.$info['id'].'" /></td>';  
  echo "<td class=\"valid\" >" . $info['username'] . "</td>";
  echo "<td class=\"valid\" >" . $info['password'] . "</td>";
  echo "<td><a target=frame2 href='" ."profile/hiscorepersonal.ws?user1=". $info['username'] ."'>Check Highscores</a></td>"; 
  echo "<td>" . $info['addeddate'] . "</td>";
  echo "<td>" . $info['ip'] . "</td>";
  echo "<td><img src=\"img/invalid.png\" title=\"Account information: INVALID!\"/><img src=\"img/valid.png\" title=\"Account information: VALID!\"/></td>";
  echo "</tr>";
  }
echo "</tbody>";
echo "</table>";
echo "<hr>";
echo "<input type='submit' name = 'del' value='Delete Selected'></form>";
echo "<input type='button' onclick='checkall(document.myform[\"delchk\"]);' value='Select All'>";
echo "<input type='button' onclick='uncheckall(document.myform[\"delchk\"]);' value='Deselect All'>";
echo "<hr>";
if ($pagenum == 1)
{
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'>First</a> ";
echo " | ";
echo " ";
$previous = $pagenum-1;
$current = $pagenum;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'>$previous</a> ";
echo " | ";
}
echo "$pagenum";
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " | ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'> $next</a> ";
echo " ";
echo " | ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last</a> ";
}
?> 

 

What is the problem? And how can I fix it?

[Mchl]

$data_p = mysql_query("SELECT * FROM accounts $max") or die(mysql_error());

 

You can't have negative limit offset.

[Russia]

Okay I have updated the code replacing it with this:

 

Change this line

$data_p = mysql_query("SELECT * FROM accounts $max") or die(mysql_error());

to this

$data_p = mysql_query("SELECT * FROM accounts $max") or die("SELECT * FROM accounts $max");

This should print out the query to the screen upon error. From the looks of it your $max string is using -4,4 as the $limit which means $pagenum is probably 0 and not 1.

 

It is now showing this error:

 

SELECT * FROM accounts limit -4,4

 

 

This is the updated code.

<?php
include("inc/config.php");
if (isset($_POST['del'])) 
{
  for ($count = 0;$count<count($_POST[delchk]);$count++)
   {
           $delete = $_POST[delchk][$count];
           $query = "DELETE FROM accounts WHERE id = '$delete'";
           $result = mysql_query($query);
           if (!$result) 
   {
            die("Error deleting accounts! Query: $query<br />Error: ".mysql_error());
        }
    }
}
  echo "<table class=\"gridtable\">
<thead>
    <tr>
        <th align=\"center\" scope=\"col\">Delete?</th>
        <th align=\"center\" scope=\"col\">Username</th>
        <th align=\"center\" scope=\"col\">Password</th>
        <th align=\"center\" scope=\"col\">Highscores</th>
        <th align=\"center\" scope=\"col\">Date</th>
        <th align=\"center\" scope=\"col\">IP Address</th>
        <th align=\"center\" scope=\"col\">Status</th>
    </tr>
</thead>
<tbody>";
echo "<form name = 'myform' action='' method='post'>";
$pagenum = (isset($_GET['pagenum'])) ? (int) $_GET['pagenum'] : 1;
$data = mysql_query("SELECT * FROM accounts") or die(mysql_error());
$rows = mysql_num_rows($data);
$page_rows = 4;
$last = ceil($rows/$page_rows);
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;
$data_p = mysql_query("SELECT * FROM accounts $max") or die("SELECT * FROM accounts $max");  
while($info = mysql_fetch_array( $data_p ))
  {
  echo "<tr align=\"center\">";
  echo '<td><input type="checkbox" id="delchk" name="delchk[]" value="'.$info['id'].'" /></td>';  
  echo "<td class=\"valid\" >" . $info['username'] . "</td>";
  echo "<td class=\"valid\" >" . $info['password'] . "</td>";
  echo "<td><a target=frame2 href='" ."profile/hiscorepersonal.ws?user1=". $info['username'] ."'>Check Highscores</a></td>"; 
  echo "<td>" . $info['addeddate'] . "</td>";
  echo "<td>" . $info['ip'] . "</td>";
  echo "<td><img src=\"img/invalid.png\" title=\"Account information: INVALID!\"/><img src=\"img/valid.png\" title=\"Account information: VALID!\"/></td>";
  echo "</tr>";
  }
echo "</tbody>";
echo "</table>";
echo "<hr>";
echo "<input type='submit' name = 'del' value='Delete Selected'></form>";
echo "<input type='button' onclick='checkall(document.myform[\"delchk\"]);' value='Select All'>";
echo "<input type='button' onclick='uncheckall(document.myform[\"delchk\"]);' value='Deselect All'>";
echo "<hr>";
if ($pagenum == 1)
{
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'>First</a> ";
echo " | ";
echo " ";
$previous = $pagenum-1;
$current = $pagenum;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'>$previous</a> ";
echo " | ";
}
echo "$pagenum";
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " | ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'> $next</a> ";
echo " ";
echo " | ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last</a> ";
}
?> 

[Mchl]

You can't have negative limit offset.

 

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

http://dev.mysql.com/doc/refman/5.0/en/select.html

[Russia]

What would I change or add to make it work?

 

I have no idea how to do it.

[Mchl]

I don't know, your code makes little sense to me.

[Russia]

Do you have a simpler way of doing it?

 

You know what I mean? Like I got the code from a website and just added the table and the delete fuction to it.

[Russia]

Bump, still need help. =|