Pagination Problems

[b455m4573r]

Hey PHP Freaks,

First of all I want to express my sincerest gratitude to whoever helps me solve this problem.

I'm new to PHP, I started about 2 weeks ago.

This is for a products page I was writing. The client updates his files which populates the SQL Database, then the PHP writes all the icons out.

I thought pagination would make it look a little cleaner. But I didn't think it would be this much work.

 

The conditional statement at the top is for when the user wants to sort the products by category (It's for a gun shop, so its categorized my centerfire, rimfire, ect...), if not it just posts everything.

 

 

The error I get is...

 

Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in C:\wamp\www\Guns N' Gadgets Site\Products.php on line 151

Call Stack

# Time Memory Function Location

1 0.0009 698840 {main}( ) ..\Products.php:0

2 0.0060 713416 mysql_fetch_array ( ) ..\Products.php:151

 

Line 151 is the while loop.

 

 

Here is the code.

 

 

 

<?php

 

//error_reporting(0);

require_once("includes/connect.php");

 

if(isset($_GET['category'])) {

$cat = $_GET['category'];

$qstuff = "SELECT guns_id,guns_brand,guns_model,guns_img,guns_price FROM tbl_guns WHERE guns_cat = '".$cat."' ORDER BY guns_id DESC";

$Fixit = mysql_query($qstuff);

 

}else{

$qstuff = "SELECT guns_id,guns_brand,guns_model,guns_img,guns_price FROM tbl_guns ORDER BY guns_id DESC";

$Fixit = mysql_query($qstuff);

 

}

 

 

 

 

$rowsperpage = 2;

if(isset($_GET['page'])) {

$pagenum = $_GET['page'];

}else{

$pagenum=1;

}

 

$offset = ($pagenum - 1) * $rowsperpage;

$totalnum = mysql_query("SELECT guns_id FROM tbl_guns");

$total = mysql_num_rows($totalnum);

$maxpage = ceil($total/$rowsperpage);

$self = $_SERVER['PHP_SELF'];

 

 

$qstring = $Fixit.",".$offset.",".$rowsperpage;

$results = $qstring;

 

if($total > $rowsperpage) {

if ($pagenum > 1) {

  $page = $pagenum - 1;

  $prev = "<a href=\"".$self."?page=".$page."\">prev</a>";         

  $first = " <a href=\"".$self."?page=1\">first</a> ";

  }else{

  $prev = "    ";         

  $first = "  ";

  }

 

if ($pagenum < $maxpage) {

  $page = $pagenum + 1;

  $next = "<a href=\"".$self."?page=".$page."\">next</a>";         

  $last = " <a href=\"".$self."?page=".$maxpage."\">last</a>";

  }else{

  $next = "    ";         

  $last = "  ";

  }

 

  }

  ?>

       

                    <div id="productsInfo">

                    <?php

                    while($row = mysql_fetch_array($results)){

echo "<div class=\"productIcon\"><a href=\"productdetails.php?id=".$row['guns_id']."\"><img class=\"thumbReSize\" src=\"images/".$row['guns_img']."\" />".$row['guns_brand']."<br />".$row['guns_model']."<br />$".$row['guns_price']."</a></div>";

}

                    ?>

                   

                    <div id="paginationContainer"><?php

 

  echo $first."  ".$prev."  Page ".$pagenum." of ".$maxpage." pages  ".$next."  ".$last;

 

?></div>

 

 

 

Thanks again.

 

                 

[Psycho]

Well, here is the while loop

  while($row = mysql_fetch_array($results)){

 

And the error states

Warning: mysql_fetch_array() expects parameter 1 to be resource, string given

 

So, apparently $results is a string. Let's look where it is defined

 

$qstring = $Fixit.",".$offset.",".$rowsperpage;
$results = $qstring;

 

I have a feeling you meant to RUN the query and assign the results to $results like this

$qstring = $Fixit.",".$offset.",".$rowsperpage;
$results = mysql_query($qstring) or die(mysql_error());

[b455m4573r]

Hey,

Thanks for the speedy reply.

I tried that chunk of code you provided and the mysql_error spat out this

"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 'Resource id #5,0,2' at line 1"

[litebearer]

Near top of his code...

$Fixit = mysql_query($qstuff);

the variable is a resource and IDB should be used as part of the later query string itself

[b455m4573r]

Thanks for the reply,

 

So litebearer you purpose that I change

$Fixit = mysql_query($qstuff);

To something like

$Fixit = $qstuff;

 

and run the query later all at once?

[PFMaBiSmAd]

You have far too many query statements, mysql_query statements, variables, and variable names that don't hint at their purpose floating around.

 

For the most straightforward code, you need to form query statements that do two things for each page request -

 

1) A query statement with/with-out the WHERE clause that will be used to find the total number of matching rows.

 

2) The query statement from step #1 with the addition of a LIMIT clause on the end to get the actual rows for the requested page number.

 

See this pseudo code -

<?php

if(isset($_GET['category'])) {
$cat = mysql_real_escape_string($_GET['category']);
$base_query = "SELECT guns_id,guns_brand,guns_model,guns_img,guns_price FROM tbl_guns WHERE guns_cat = '".$cat."' ORDER BY guns_id DESC";
}else{
$base_query = "SELECT guns_id,guns_brand,guns_model,guns_img,guns_price FROM tbl_guns ORDER BY guns_id DESC";
}

// execute the base query to get the number of rows
$result = mysql_query($base_query);
$total = mysql_num_rows($result);

//... code to calculate the number of pages and the offset based on the requested page number ...

// execute the query to get the result set for the requested page
$query = "$base_query LIMIT $offset,$rowsperpage";
$result = mysql_query($query);

// loop over the result set and output the data
while($row = mysql_fetch_array($result)){

... your code inside the loop ...

}

[b455m4573r]

Thanks  PFMaBiSmAd!

 

That solved the sorting based on the query, but if there isn't enough items to need pagination is coughs out these huge errors because the

  $prev = "<a href=\"".$self."?page=".$page."\">prev</a>";

Is no longer needed right?

I guess I would need to write a conditional for that variable?

 

Also, I would like to apologize for my lack knowledge and general neediness. I'm trying to grasp this.

[PFMaBiSmAd]

If you want to only produce the pagination links when the total number of matching items is greater than the rowsperpage, you can put all the logic that forms and outputs the pagination links inside of a conditional statement that tests if($total > $rowsperpage){... pagination link code ...}

[DavidAM]

For the most straightforward code, you need to form query statements that do two things for each page request -

 

1) A query statement with/with-out the WHERE clause that will be used to find the total number of matching rows.

 

2) The query statement from step #1 with the addition of a LIMIT clause on the end to get the actual rows for the requested page number.

 

I'm sorry. Is there some reason you suggest running the same query twice instead of using SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()? I mean, returning all those rows, twice, is very inefficient. At least, just use a SELECT COUNT() instead of selecting all the data. Did I miss something there?

[PFMaBiSmAd]

For the most straightforward code

 

The OP is having difficulty assigning things to and using the correct variables at the right points in his code. He needs to be able to crawl before he can walk.

[Proletarian]

For the most straightforward code, you need to form query statements that do two things for each page request -

 

1) A query statement with/with-out the WHERE clause that will be used to find the total number of matching rows.

 

2) The query statement from step #1 with the addition of a LIMIT clause on the end to get the actual rows for the requested page number.

 

I'm sorry. Is there some reason you suggest running the same query twice instead of using SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS()? I mean, returning all those rows, twice, is very inefficient. At least, just use a SELECT COUNT() instead of selecting all the data. Did I miss something there?

 

He's suggesting running two different queries. You need to know how many rows you're working with -- and which rows within a certain range you're working with -- before you can even start assembling the page.

[Shadowing]

also  b455m4573r

 

you should really be using error control on your queries. What is going to happen is this. When you become better at PHP you are going to realize why its important to use them and you will be going back through pages and pages of code adding it into your queries.

 

like Psycho's example

 

$qstring = $Fixit.",".$offset.",".$rowsperpage;
$results = mysql_query($qstring) or die(mysql_error());

 

 

[b455m4573r]

Hey Everyone,

 

So I have cleaned up my code a bit, but now I'm running into a whole new mess of issues.

Well, just one really.

 

When I click the category links to sort the while loop echo based on the category in the database it works fine.

But when I click on "next page" it just goes to the 2nd page of all the products, not just the ones in the category.

 

Let me explain it a different way. When I open the page, it shows all items in the database, with all the pageination working properly, when I click the link to sort them by category, that works as well, but when I click the next button while in the category to go to the next page, it goes to the second page of all items.

 

Once again sorry about the messy code, I feel like I'm missing a conditional statement for when I click the category, but I don't know how to approach it.

If you want to see the full code...http://pastebin.com/embed.php?i=0cFf6QkH

<?php


require_once("includes/connect.php");

if(isset($_GET['category'])) {
$cat = mysql_real_escape_string($_GET['category']);
$base_query = "SELECT guns_id,guns_brand,guns_model,guns_img,guns_price FROM tbl_guns WHERE guns_cat = '".$cat."' ORDER BY guns_id DESC";
}else{
$base_query = "SELECT guns_id,guns_brand,guns_model,guns_img,guns_price FROM tbl_guns ORDER BY guns_id DESC";
}

$result = mysql_query($base_query);
$total = mysql_num_rows($result);




$rowsperpage = 3;
if(isset($_GET['page'])) {
$pagenum = $_GET['page'];
}else{
$pagenum=1;
}

$offset = ($pagenum - 1) * $rowsperpage;
$maxpage = ceil($total/$rowsperpage);
$self = $_SERVER['PHP_SELF'];


$query = "$base_query LIMIT $offset,$rowsperpage";
$result = mysql_query($query);


if ($pagenum > 1) {
  $page = $pagenum - 1;
  $prev = "<a href=\"".$self."?page=".$page."\">prev</a>";          
  $first = " <a href=\"".$self."?page=1\">first</a> ";
  }else{
  $prev = "    ";          
  $first = "  ";
  }

if ($pagenum < $maxpage) {
  $page = $pagenum + 1;
  $next = "<a href=\"".$self."?page=".$page."\">next</a>";          
  $last = " <a href=\"".$self."?page=".$maxpage."\">last</a>";
  }else{
  $next = "    ";          
  $last = "  ";
  }
  ?>



             
                    </div>
                    
                    <div id="productsInfo">
                    <?php
                    	while($row = mysql_fetch_array($result)) {
					echo "<div class=\"productIcon\">
							<a href=\"productdetails.php?id=".$row['guns_id']."\">
								<img class=\"thumbReSize\" src=\"images/".$row['guns_img']."\" />
								".$row['guns_brand'].
								"<br />".$row['guns_model'].
								"<br />$".$row['guns_price'].
							"</a>
						</div>";
					}
                    ?>
                    
                    <div id="paginationContainer"><?php

				  echo $first."  ".$prev."  Page ".$pagenum." of ".$maxpage." pages  ".$next."  ".$last;




				 ?>
                                  </div>
  

[PFMaBiSmAd]

The links you build for the first, previous, next, last need to also include any other $_GET parameters you are using on the page. See the following post on how to build the links for the page number AND retain any other $_GET parameters - http://www.phpfreaks.com/forums/index.php?topic=348834.msg1646676#msg1646676