Pagination and losing some records

[JPark]

I have a t-shirt web site that I am redesigning.  I have a pagination script and thought things were looking good until I found that I was not showing all of my records.

 

1.  Here is a page on the site: http://funny.teamspirittees.com/testing/shirts/sweats/funny_sweats_for_guys_and_girls.php  If you count the sweatshirts, you will find 19.  However, there are 23 total.

2.  Here is a page that lists and counts them: http://funny.teamspirittees.com/testing/shirts/sweats/test.php

 

I don't know why I am missing 3 sweatshirts...  it is happening on my other pages as well and I am hoping that fixing this page will help me fix the others.

 

Here's the pagination code:

<?php
		#################################		  
		### Begin Pagination Coding #####
		#################################
		$conn = mysql_connect($hostname, $user, $password) or die(mysql_error());
		mysql_select_db($database) or die(mysql_error());

		// find out how many rows are in the table 
		$sql =  "SELECT COUNT(shirtType) FROM shirts WHERE sex='Unisex'"; // EDIT
		$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
		$r = mysql_fetch_row($result);
		$numrows = $r[0];

		$columns= 2;

		// number of shirts to show per page
		$rowsperpage = 7;
		// find out total pages
		$totalpages = ceil($numrows / $rowsperpage);

		// get the current page or set a default
		if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
		   // cast var as int
		   $currentpage = (int) $_GET['currentpage'];
		} else {
		   // default page num
		   $currentpage = 1;
		} // end if

		// if current page is greater than total pages...
		if ($currentpage > $totalpages) {
		   // set current page to last page
		   $currentpage = $totalpages;
		} // end if
		// if current page is less than first page...
		if ($currentpage < 1) {
		   // set current page to first page
		   $currentpage = 1;
		} // end if

		// the offset of the list, based on current page 
		$offset = ($currentpage - 1) * $rowsperpage;

		// get the info from the db 
		$sql = "SELECT * FROM shirts WHERE sex='Unisex' LIMIT $offset, $rowsperpage"; //EDIT
		$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

		echo "<div class='shirts'>";
		$row = mysql_fetch_array($result) or die(mysql_error());
		// mysql_fetch_array() returns both an assocative array and a numerically indexed array --
		// a combination of the mysql_fetch_row() and the mysql_fetch_assoc() functions

		// set some basic variables;
		$tr=0;
		$flag= 0;
		$i=0;

		// run through the all the arrays to break them into individual items that I can reference later
		while($row = mysql_fetch_object( $result )) {
			$item[$flag]= $row->item;
			$title[$flag]= $row->title;
			$url[$flag]= $row->url;
			$imageUrl[$flag]= $row->imageUrl;
			$alt[$flag]= $row->alt;
			$sex[$flag]= $row->sex;
			$shirtType[$flag]= $row->shirtType;
			$flag++;
		}

		// find out how many rows we need
		$totalRows=($flag/2);

		// begin the table -- 6 rows and 3 columns
		echo "<table border='0' cellpadding='5' align='center'>";
			while ($tr <= $totalRows) {  // create the rows
					$td=1;  // each time through, re-start the column counter
					echo "<tr>";
					while ($td <= 2) {  //create the columns
						echo "<td>";
						if ($item[$i] == NULL) { break; }
						echo "<a href='".$url[$i]."'><img src='".strtolower($imageUrl[$i])."' border='0' alt='".$alt[$i]."' /></a>";
						// EDIT
						$i++;
						echo "</td>\n";
						$td++;
					}
					echo "</tr>";
					$tr++;
				}
		echo "</table>";

		/******  build the pagination links ******/
// range of num links to show
		$range = 3;
		echo "<div align='center'>";
		// if not on page 1, don't show back links
		if ($currentpage > 1) {
		   // show << link to go back to page 1
		   echo "<br /> <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
		   // get previous page num
		   $prevpage = $currentpage - 1;
		   // show < link to go back to 1 page
		   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
		} // end if 

		// loop to show links to range of pages around current page
		for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
		   // if it's a valid page number...
		   if (($x > 0) && ($x <= $totalpages)) {
			  // if we're on current page...
			  if ($x == $currentpage) {
				 // 'highlight' it but don't make a link
				 echo " [<b>$x</b>] ";
			  // if not current page...
			  } else {
				 // make it a link
			 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
			  } // end else
		   } // end if 
		} // end for

		// if not on last page, show forward and last page links	
		if ($currentpage != $totalpages) {
		   // get next page
		   $nextpage = $currentpage + 1;
			// echo forward link for next page 
		   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
		   // echo forward link for lastpage
		   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
		} // end if
		echo "</div>";
		/****** end build pagination links ******/
		echo "</div";
		echo "</div>";

		#############################
		### End Pagination Coding ###
		#############################		  
		?>		  

 

and the code for the test.php page

<?php
$i = 0;
$dir = opendir (".");
        while (false !== ($file = readdir($dir))) {
                if (strpos($file, '.gif',1)||strpos($file, '.jpg',1) ) {
                    echo $i.":<a href='".$file."'>$file</a><br />";
				$i++;
                }
        }
echo "<br /><br />".$i." total items";
?>

 

What am I missing?

 

Please and thank you!

 

Joe

[.josh]

Your pagination goes by what is listed in your database.  Your test.php reads from the directory.  The first thing I would check is to make sure your database entries match your files in your directory.

[JPark]

Ok... that was dumb

 

Here's a query of the database:

mysql_connect($hostname, $user, $password);
@mysql_select_db($database) or die( "Unable to select database");

$sql = "SELECT * FROM shirts WHERE sex='Unisex'"; //EDIT
$result=mysql_query($sql);

$num=mysql_num_rows($result);

mysql_close();

$i=0;

echo "<table border='1' width='75%'>";

while ($i < $num) 
{
	$item[$i] 		= mysql_result($result,$i,"item");
	$title[$i] 		= mysql_result($result,$i,"title");
	$url[$i] 		= mysql_result($result,$i,"url");
	$imageUrl[$i]	= mysql_result($result,$i,"imageUrl");
	$alt[$i]		= mysql_result($result,$i,"alt");
	$sex[$i]		= mysql_result($result,$i,"sex");
	$shirtType[$i]	= mysql_result($result,$i,"shirtType");

	echo "<tr>";
	echo "<td>".$i."</td>";
	echo "<td>".$imageUrl[$i]."</td>";
	echo "<td>".$item[$i]."</td>";
	echo "<td>".$title[$i]."</td>";				
	echo "<td>".$shirtType[$i]."</td>";
	echo "</tr>";

$i++;	
}
?>
</table>
<p>A total of <?php echo $num ?> rows were found.</p>

 

and I do get 23 items

http://funny.teamspirittees.com/testing/shirts/sweats/test.php vs

the 19 on

http://funny.teamspirittees.com/testing/shirts/sweats/funny_sweats_for_guys_and_girls.php.

 

And here's what's even more curious to me.  When I log into phpMyAdmin and query the database (SELECT * FROM shirts WHERE sex='Unisex'), I get all 23 sweatshirts.

 

What could be going on?

 

Thanks,

 

Joe

[JPark]

anyone?  Buehler...?  Buehler...?

[lemmin]

I see a number of places where the error could be, but instead of trying to fix it, I think you might consider changing the way that you do it. Here is an example of a much easier way to do it:

<?php
$entriesPerPage = 6;
$start = $_GET['page']*$entriesPerPage;
$sql = "SELECT *, (SELECT COUNT(*) FROM shirts WHERE sex='Unisex') as total FROM shirts WHERE sex='Unisex' LIMIT $start, $entriesPerPage";
$result=mysql_query($sql);
[...]
$total = $row['total'];
[...]

 

Then, when you are writing your page links, you can do something like this:

 

<?php
      $echo .= "<div><table width=100%><tr><td>";
      if ($_GET['page'])
         $echo .= "<a href=\"?page=0\"><<</a> <a href=\"?page=".($_GET['page']-1)."\">< Previous Page</a>";
      $echo .= "<td align=\"right\">";
      if ($_GET['page']*$entriesPerPage+$entriesPerPage < $total)
         $echo .= "<a href=\"?page=".($_GET['page']+1)."\">Next Page ></a> <a href=\"?page=".(floor(($total-1)/$entriesPerPage))."\">>></a>";
      $echo .= "</table></div>";

 

You can do the same thing to set the individual page links too. This way you can print everything from the query without checking the numbers. Plus, you aren't grabbing data from your DB that you aren't using.