Pagination Problem

[fr600]

I having a problem with pagination. The error I get is as follows:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in d:\Server\Apache\htdocs\index.php on line 8
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 'table LIMIT 1, 3' at line 1

And the code is as fololows:

<?php
@mysql_connect(localhost, root, 123456) or die("ERROR--CAN'T CONNECT TO SERVER");
@mysql_select_db("pagination") or die("ERROR--CAN'T CONNECT TO DB");

$limit = 3;
$query_count = "SELECT count(*) FROM table";
$result_count   = mysql_query($query_count);
$totalrows  = mysql_num_rows($result_count);

if(empty($page)){
$page = 1;
}

$limitvalue = $page * $limit - ($limit);
$query  = "SELECT * FROM table LIMIT $limitvalue, $limit";        

$result = mysql_query($query) or die("Error: " . mysql_error());

if(mysql_num_rows($result) == 0){
echo("Nothing to Display!");
}

$bgcolor = "#E0E0E0";

echo("<table>");

while($row = mysql_fetch_array($result)){
if ($bgcolor == "#E0E0E0"){
$bgcolor = "#FFFFFF";
}else{
$bgcolor = "#E0E0E0";
}

echo("<tr bgcolor=".$bgcolor.">n<td>");
echo($row["users"]);
echo("</td>n<td>");
echo($row["usersID"]);
echo("</td>n</tr>");
}

echo("</table>");

if($page != 1){
$pageprev = $page--;

echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a>&nbsp;");
}else{
echo("PREV".$limit."&nbsp;");
}

$numofpages = $totalrows / $limit;

for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
echo($i."&nbsp;");
}else{
echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;");
}
}

if(($totalrows % $limit) != 0){
if($i == $page){
echo($i."&nbsp;");
}else{
echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;");
}
}

if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page++;

echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>");
}else{
echo("NEXT".$limit);
}

mysql_free_result($result);
?>

This is the SQL:

CREATE TABLE table (
usersID int(11) NOT NULL auto_increment,
users varchar(100) NOT NULL default '',
PRIMARY KEY (usersID),
UNIQUE KEY title (users)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
INSERT INTO table VALUES (1, 'test1');
INSERT INTO table VALUES (2, 'test2');
INSERT INTO table VALUES (3, 'test3');
INSERT INTO table VALUES (4, 'test4');
INSERT INTO table VALUES (5, 'test5');

Can you please help me?

[dalecosp]

$query  = "SELECT * FROM table LIMIT $limitvalue, $limit";

Check the MySQL manual, but I think that LIMIT only takes one value/parameter. The above code is expanding to "limit 1,3"; this creates a syntax error and causes the query to return void, which gives the first error above ("supplied argument blah blah").

[Weedpacket]

Isn't table a reserved word in MySQL?

[fr600]

Originally posted by Weedpacket
Isn't table a reserved word in MySQL?

Yes table is a reserved word. TABLE changed to TEST but still it's not working...

[Weedpacket]

Try removing the @ from your connection and database selection calls, and change the line

$result_count   = mysql_query($query_count); 

to read

$result_count   = mysql_query($query_count) or die(mysql_error()); 

If you read the error messages you'll see that it ran into trouble just trying to count the rows of your first query (which, incidentally, will always be 1 once you get it working). So one can guess that it was failing before then, and you just didn't know because you were suppressing error messages.