BIG Pagination Problem

[troinfo]

Hey All,

I've been working on this code for three days and it seems like I'm almost there but.....

i found an ok script and i've been trying to make it work with my code.

What happens is I get the "prev 1 2 3 4 5 next" and everything else to display correctly with out a problem on the first page however when I click one of the links I get this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in view.php on line 18
Error: You have an error in your SQL syntax near 'limit 2,2' at line 1

The 'limit 2,2' changes to other numbers like 6,2 but i understand why that is.

Any help would be grand. I'm a NewB so any other comment are welcome.

here is the code for the view.php:

<?
session_start();
$tempcat_name = $GET[cat_name];
$SESSION[cat_id] = $GET[cat_id];
$SESSION[tempcat_name] = $tempcat_name;
$cat_id = $SESSION[cat_id];
$page = $GET[page];
$limit = 2;

$db_name ="alpha";
$connection = mysql_connect("*","","****") or die(mysql_error());
$db = mysql_select_db($db_name,$connection) or die(mysql_error());

$query_count = "SELECT userid, username, company, cat_name, pro_id, pro_name, pro_desc FROM user_table, category, products WHERE user_table.userid = products.user_id AND products.cat_id = category.cat_id AND category.cat_id = $cat_id";

$result_count = mysql_query($query_count);

// Pulls what we want from the database
$totalrows = mysql_num_rows($result_count);

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

$limitvalue = $page * $limit - ($limit);

$query = "SELECT * FROM user_table, category, products WHERE user_table.userid = products.user_id AND products.cat_id = category.cat_id AND category.cat_id = $cat_id limit $limitvalue, $limit";

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

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

   $bgcolor = "#efefef";	

while($row3 = mysql_fetch_array($result)){

    // Start The Background Check
    if($bgcolor == "#e4e4e4"){

        $bgcolor = "#efefef";

    } else {

        $bgcolor = "#e4e4e4";
    }    

	$company = $row3['company'];
	$user_id = $row3['user_id'];
	$cat_id = $row3['cat_id'];
	$cat_name = $row3['cat_name'];
	$pro_id = $row3['pro_id'];
	$pro_name = $row3['pro_name'];
	$pro_desc = $row3['pro_desc'];
    $cutpro_desc = substr($pro_desc,0,75);

$option_block .= "<tr bgcolor=$bgcolor><td height=45 valign=top><b>Company: </b>$company<br><b>Product Name: </b><a href=\"viewproduct.php?pro_id=$pro_id\">$pro_name</a> - $cutpro_desc ...</td><td width=5 ><input type=\"checkbox\"></td></tr>";

}

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

     echo("<a href=\"$_SERVER[PHP_SELF]?page=$pageprev\">PREV</a>&nbsp;"); 

} else {

    echo("PREV&nbsp;");

	}
$numofpages = $totalrows / $limit;

for($i = 1; $i <= $numofpages; $i++) {

    if($i == $page){

        echo($i."&nbsp;");

    } else { 

       echo("<a href=\"$_SERVER[PHP_SELF]?page=$i\">$i</a>&nbsp;");

    }

}

if(($totalrows % $limit) != 0) {

    if($i == $page){

        echo($i."&nbsp;");

    } else {

        echo("<a href=\"$_SERVER[PHP_SELF]?page=$i\">$i</a>&nbsp;");

    }
} 

if(($totalrows - ($limit * $page)) > 0){

    $pagenext = $page +1;

    echo("<a href=\"$_SERVER[PHP_SELF]?page=$pagenext\">NEXT</a>");

} else {

    echo("NEXT");
}
mysql_free_result($result);

?>

TIA,

troinfo

[steps]

Just a little tip:

You could use an abstraction layer like ADOdb, which does a lot of work for you. (Your code will work on a lot of databases, etc).

I think ADOdb has even a special feature to create those prev-next stuff.

Take a look: http://php.weblogs.com/ADODB

[troinfo]

Thanks I will check it out

[dalecosp]

Yeah, he could use something like that....but why, when all he needs to do is fix a simple syntax error???

PHP statements should end with a semicolon; (there's one now!) and so should MySQL statements. If you look at the query in the script (the one with the limit statements) there's only one semicolon.

Put one before and one after that double quote, and you'll fix this one.

HTH,

[troinfo]

Thanks for your reply Dalecosp.

I'm not really sure what you mean by putting a ";" in front of the and in the query statement.

The two tables are joined.

I tried it and got a sql error. Maybe I'm doing something wrong???

Could you or someone else explain this to me.

Thanks,
troinfo

[dalecosp]

Ah, English. I didn't say 'put a semicolon before the AND', I said 'put a semicolon before and a semicolon after that double quote' --- In other words, "finish your SQL statements properly"...

Your error message is this:

Error: You have an error in your SQL syntax near 'limit 2,2' at line 1 

Your syntax is this:

$query = "SELECT * FROM user_table, category, products WHERE user_table.userid = products.user_id 
AND products.cat_id = category.cat_id AND category.cat_id = $cat_id limit $limitvalue, $limit"; 

Put a semicolon exactly 2 characters from the end, like this:

$query = "SELECT * FROM user_table, category, products WHERE user_table.userid = products.user_id 
AND products.cat_id = category.cat_id AND category.cat_id = $cat_id limit $limitvalue, $limit;"; 

🙂

[keith73]

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in view.php on line 18
Error: You have an error in your SQL syntax near 'limit 2,2' at line 1

The 'limit 2,2' changes to other numbers like 6,2 but i understand why that is.

the error does not mean that the limit 2,2 statement is wrong, it usually means the part of the query just before it is wrong.

When you print out mysql_error(), also print out your query. just have to do print $query; and see what's going on.
I'm guessing that category.cat_id = $cat_id is probably the culprit.

and there is no need to put a semicolon in your query in php. mysql_query handles it just fine without it. In fact, the manual explicitly tells you not to put one in.

http://www.php.net/mysql_query
Note: The query string should not end with a semicolon.

• keith

[troinfo]

I got it working.....finally hehe.

Here it is:

<?
session_start();
$tempcat_name = $_GET['cat_name'];
$cat_id = $_GET['cat_id'];
$tempcat_name = $_SESSION['tempcat_name'];
$page = $_GET['page'];
$limit = 10;

$db_name ="alpha";
$connection = mysql_connect("******","******","******") or die(mysql_error());
$db = mysql_select_db($db_name,$connection) or die(mysql_error());

$query_count = "SELECT userid, username, company, cat_name, pro_id, pro_name, pro_desc FROM user_table, category, products WHERE user_table.userid = products.user_id AND products.cat_id = category.cat_id AND category.cat_id = $cat_id";

$result_count = mysql_query($query_count);    

    // Pulls what we want from the database
$totalrows = mysql_num_rows($result_count); 

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

$limitvalue = ($page * $limit - ($limit));

$query  = "SELECT userid, username, company, cat_name, pro_id, pro_name, pro_desc FROM user_table, category, products WHERE user_table.userid = products.user_id AND products.cat_id = category.cat_id AND category.cat_id = $cat_id limit $limitvalue, $limit";      

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


if(mysql_num_rows($result) == 0){
    $option_block .= ("<tr><td height=145  align=center colspan=2><P><em>Sorry! No results under this category yet check back soon. Thank you.</em></p></td></tr>");
}

   $bgcolor = "#efefef";	

while($row3 = mysql_fetch_array($result)){

    // Start The Background Check
    if($bgcolor == "#e4e4e4"){

        $bgcolor = "#efefef";

    } else {

        $bgcolor = "#e4e4e4";
    }    

	$company = $row3['company'];
	$user_id = $row3['user_id'];
	$cat_id2 = $row3['cat_id'];
	$cat_name = $row3['cat_name'];
	$pro_id = $row3['pro_id'];
	$pro_name = $row3['pro_name'];
	$pro_desc = $row3['pro_desc'];
    $cutpro_desc = substr($pro_desc,0,75);

$option_block .= "<tr bgcolor=$bgcolor><td height=45 valign=top><b>Company: </b>$company<br><b>Product Name: </b><a href=\"viewproduct.php?pro_id=$pro_id\">$pro_name</a> - $cutpro_desc ...</td><td width=5 ><input type=\"checkbox\"></td></tr>";

}




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

     $pagecount = ("<a href=\"$_SERVER[PHP_SELF]?page=$pageprev&cat_name=$cat_name&cat_id=$cat_id\">< PREV</a>&nbsp;"); 

} else {

    $pagecount = ("< PREV&nbsp;");

	}
$numofpages = ($totalrows / $limit);

for($i = 1; $i <= $numofpages; $i++) {

    if($i == $page){

        $pagecount .= ($i."&nbsp;");

    } else { 

       $pagecount .= ("[<a href=\"$_SERVER[PHP_SELF]?page=$i&cat_name=$cat_name&cat_id=$cat_id\">$i</a>]&nbsp;");

    }

}

if(($totalrows % $limit) != 0) {

    if($i == $page){

        $pagecount .= ($i."&nbsp;");

    } else {

        $pagecount .= ("[<a href=\"$_SERVER[PHP_SELF]?page=$i&cat_name=$cat_name&cat_id=$cat_id\">$i</a>]&nbsp;");

    }
} 

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

    $pagenext = $page +1;

    $pagecount .= ("<a href=\"$_SERVER[PHP_SELF]?page=$pagenext&cat_name=$cat_name&cat_id=$cat_id\">NEXT ></a>");

} else {

    $pagecount .= ("NEXT >");
}
mysql_free_result($result);
$option_block .= "<tr><td colspan=\"2\">&nbsp;</td></tr><tr><td align=\"center\" height=\"20\" colspan=\"2\">$pagecount</td></tr>";
?>

Thank you to all who posted replies.

troinfo 😃