[RESOLVED] pagination problem

[mpar612]

I am having a pagination problem.

I have 6 rows being returned with the SQL query and I am printing 2 of those rows per page with navigation at the bottom that lists a number for each page returned and a button to go to the next page.

The code below prints 2 rows per page and returns all of the results, but it also prints an additional 3 pages and prints the text "Nothing to Display!" on these 3 pages. You can see this at Link .

Does anyone have any ideas why this is printing the extra pages? Thanks!

<?php

								$limit  = 2;                
								$query_count = "SELECT * FROM lounge_products";     
								$result_count = mysql_query($query_count);     
								$totalrows = mysql_num_rows($result_count); 

								$PHP_SELF = $_SERVER['PHP_SELF'];

								if(!isset($_GET['page'])){ // Checks if the $page variable is empty (not set)
									$page = 1;     // If it is empty, we're on page 1
								} else{
									$page = $_GET['page'];
								}       

								$limitvalue = $page * $limit - ($limit );  
								// Ex: (page2 * 5(items per page) = 10) - 5 = 5 <- data starts at 5

								$query  = "SELECT * FROM lounge_products WHERE category='Compilations' ORDER BY artist_name DESC LIMIT $limitvalue, $limit";   
								$result = mysql_query($query) or die("Error: " . mysql_error());  

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

								$counter = 0;
								$cells_per_row = 2;
								while($row=mysql_fetch_array($result))
								{
								   $counter++;
								   if(($counter % $cells_per_row) == 1)  { echo '<tr>'; }
								   print "<td align=\"center\" valign=\"top\"><a href=\"Product.php?upc=$row[upc]\" target=\"_parent\"><img border=\"0\" src=$row[image] height=80 width=80></a><br>
								   <font face=\"Arial\" size=\"1\" color=\"#810023\">$row[album_title]<br>
								   <font face=\"Arial\" size=\"1\" color=\"#810023\">\$$row[price]</td><td>&nbsp;&nbsp;&nbsp;</td>";			


							   if(($counter %  $cells_per_row) == 0) { echo '</tr>'; }
								}
							   // just in case we haven't closed the last row
							   // this would happen if our result set isn't divisible by $cells_per_row
							   if(($counter % $cells_per_row) != 0) { echo '</tr>'; }

									echo '<tr><td colspan=10 align=center>';

								if($page != 1){  
										$pageprev = $page - 1; //decrementing $page-- does not work on all php configurations.


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

									$numofpages = $totalrows / $limit;  


									#echo "<br>", $totalrows;
								   #exit;

									for($i = 1; $i <= $numofpages; $i++){
										/* This for loop will add 1 to $i at the end of each pass until $i is greater  
										than $numofpages. */
										if($i == $page){
											echo($i." ");
										}else{
											echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
										}
									} //code above has been decoded lozza


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

									if(($totalrows - ($limit * $page)) > 0){
										$pagenext = $page + 1; //incrementin $page like $page++ does not work in all enviroments

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

								echo '</tr></td>';

									mysql_free_result($result);

								?>

[ClarkF1]

It's your total rows query

You have $query_count = "SELECT * FROM lounge_products";

Then later you have $query = "SELECT * FROM lounge_products WHERE category='Compilations' ORDER BY artist_name DESC LIMIT $limitvalue, $limit"; for the sub-set.

These two queries need to match to make the total number correct (you don't need the ordering or limit)

The correct query is:

$query_count = "SELECT * FROM lounge_products WHERE category='Compilations'";

You shouldn't really use * useless you are going to use all the data from that row in the output. It is better practice to put the column names separated by commas

[mpar612]

That was it. Thanks!

[Gonwee]

ClarkF1 wrote:

...
You shouldn't really use * useless you are going to use all the data from that row in the output. It is better practice to put the column names separated by commas

Sorry if i take this off topic, but why is it better to use column names instead of *. Is it faster?

[ClarkF1]

It saves time by not querying the database for all the data.

Imagine if you had a database with thousands of rows and say 20 columns.

There's no point in retrieving the data for all 20 columns if you only need to perform a function based on 3 of them

It's a waste of execution time especially if it's a high traffic site.