Counting returned results

BCAV_WEB · January 12, 2011

Hi, I have a MySQL query that is directly affected by an input form and i'm trying to couple this with some pagination. What I need to do is count the returned data.

I have a second query, running on the same page that counts the entire rows of a cetain field, which is fine when no one has serach, affecting the normal query. But the moment some one looks and find one returned item, the pagination is screwed. I know the issue is lying with the query, but for the life of me I cannot figure it out. It's been a long and busy day, maybe some rest would help.

If anyone can help me out they would be a life saver!!

Normal Query

$car_query = " SELECT * FROM cars WHERE model  LIKE '%$vehicle%' OR make LIKE '%$vehicle%' OR model_details LIKE '%$vehicle%' OR search LIKE '%$vehicle%' ORDER BY $filter $direction LIMIT $offset, $rowsPerPage";						
				$car_result = mysql_query($car_query) or die ("Error in query: $car_query. ".mysql_error());

Pagination Query

$query   = "SELECT COUNT(model) AS numrows FROM cars";
				$result  = mysql_query($query) or die('Error, query failed');
				$row     = mysql_fetch_array($result, MYSQL_ASSOC);
				$numrows = $row['numrows'];

Pikachu2000 · January 12, 2011

You probably don't want to get a count of how many total records exist to use for pagination, but rather count the number of records that will actually be potentially displayed as a result of the search.

fenway · January 12, 2011

Besides, exact values for pagination is just slow -- Google doesn't do it, either.

BCAV_WEB · January 13, 2011

Yes I know that would be the better course of action to count the records returned and if say 7 show 1 page, and if 14 show 1, 2 on the idea of 7 records per page. But how would I go about this?

BCAV_WEB · January 13, 2011

Okay, i've been looking at the coding, firstly ive removed the count within the query for $numrows = mysql_num_rows($result); which is doing the same job, but counting the rows returned. I have also altered the query that is affecting the variable $numrows as it should be the same query as the search without having the limit by etc...

This is working fine, as when I have 14 results they are split into 2 pages, displaying as Page 1 and Page 2 on the pagination. However, when I click page two it goes directly back to the orginial query of select all. Any ideas??

<?php include "sections/phparea.php";?>
<?php include "sections/header.php";?>
<?php include "sections/left.php";?>
<!-- start content -->
	<div id="content">
		<h2>Test Area</h2>
            
            <?php	
				print "
						<form name='test' target='_self' method='post'>
						<table class=''> 
							<tr> 
								<td>
									<input name='vehicle' type='text' id='search_name' size='16'>
									<input type='image' src='images/search.gif' alt='search' name='search' id='search' value='search'/>

								</td>
							</tr>
							<tr>
								<td>
									<select name='filter' id='filter'>
										<option value='make' selected='selected'>Filter By</option>
										<option value='make'>Vehicle Manufacture</option>
										<option value='model'>Vehicle Model</option>
										<option value='our_price'>Price</option>
										<option value='delivery_time'>Delivery Time</option>
									</select>
								</td>
							</tr>
							<tr>
								<td>
									<input type='radio' name='direction' value='ASC' checked />Ascending 
									<input type='radio' name='direction' value='DESC' />Descending
								</td>
							</tr>
		    			</form>

				      ";
				include "connections/dbconnect.php";


				if(isset($_POST['search_x'])) 
				{	
					$vehicle = $_POST['vehicle'];
					$filter = $_POST['filter']; 
					$direction = $_POST['direction']; 
				}
				else 
				{
					$filter = "make"; 
					$direction = "ASC"; 
				}  
				//$manfactures = "Ford";

				if(isset($_GET['limit'])) 
				{	
					$rowsPerPage = $_GET['limit'];;
				}
				else 
				{
					// how many rows to show per page
					$rowsPerPage = 7;
				}


				// by default we show first page
				$pageNum = 1;

				// if $_GET['page'] defined, use it as page number
				if(isset($_GET['page']))
				{
					$pageNum = $_GET['page'];

				}

				// counting the offset
				$offset = ($pageNum - 1) * $rowsPerPage;


				$car_query = " SELECT * FROM cars WHERE model  LIKE '%$vehicle%' OR make LIKE '%$vehicle%' OR model_details LIKE '%$vehicle%' OR search LIKE '%$vehicle%' ORDER BY $filter $direction LIMIT $offset, $rowsPerPage";						
				$car_result = mysql_query($car_query) or die ("Error in query: $car_query. ".mysql_error()); 
				setlocale(LC_MONETARY, 'en_GB');
				$fmt = '%i';

				$num_rows = mysql_num_rows($car_result);

				// how many rows we have in database
				$query   = "SELECT * FROM cars WHERE model  LIKE '%$vehicle%' OR make LIKE '%$vehicle%' OR model_details LIKE '%$vehicle%' OR search LIKE '%$vehicle%' ORDER BY $filter $direction";
				$result  = mysql_query($query) or die('Error, query failed');
				$row     = mysql_fetch_array($result, MYSQL_ASSOC);
				//$numrows = $row['numrows'];
				$numrows = mysql_num_rows($result);

				// how many pages we have when using paging?
				$maxPage = ceil($numrows/$rowsPerPage);

				// print the link to access each page
				$self = $_SERVER['PHP_SELF'];
				$nav  = '';

				for($page = 1; $page <= $maxPage; $page++)
				{
				   if ($page == $pageNum)
				   {
					  $nav .= " $page "; // no need to create a link to current page
				   }
				   else
				   {
					  $nav .= " <a href=\"$self?page=$page&limit=$rowsPerPage\">$page</a> ";
				   }
				}

				if ($pageNum > 1)
				{
				   $page  = $pageNum - 1;
				   $prev  = " <a href=\"$self?page=$page&limit=$rowsPerPage\">[Prev]</a> ";

				   $first = " <a href=\"$self?page=1&limit=$rowsPerPage\">[First Page]</a> ";
				}
				else
				{
				   $prev  = ' '; // we're on page one, don't print previous link
				   $first = ' '; // nor the first page link
				}

				if ($pageNum < $maxPage)
				{
				   $page = $pageNum + 1;
				   $next = " <a href=\"$self?page=$page&limit=$rowsPerPage\">[Next]</a> ";

				   $last = " <a href=\"$self?page=$maxPage&limit=$rowsPerPage\">[Last Page]</a> ";
				}
				else
				{
				   $next = ' '; // we're on the last page, don't print next link
				   $last = ' '; // nor the last page link
				}


				if (mysql_num_rows($car_result) > 0) 
				{



					while ($car_row = @ mysql_fetch_array($car_result)) 
					{

						print "
								<table class='details'>
									<tr>
										<td rowspan='2'>
											<img src=\"". $car_row["image"] ."\" alt='" . $car_row["image_alt"] . "' />
										</td>
										<td colspan='2'>
											<a href='" . $car_row["what_link"] . "'>
												" . $car_row["model"]." ".$car_row["model_details"] . " 
											</a>
									</tr>
									<tr>
										<td>
											<p class='info'>
												RRP:<br/>
												What Price:<br/> 	
												Our Price:<br/>
												VAT:<br/> 
												Savings of:<br/> 
												Delivery Time: 

											</p>
										</td>
										<td>
											<p class='info1'>
										";
								 $price = ($car_row["excluding_vat_price"] * $car_row["vat"]) + ($car_row["excluding_vat_price"])+ $car_row["other_costs"];
												echo money_format($fmt, $car_row["rrp"] );
								  print "<br/>";

												echo money_format($fmt, $car_row["what_price"] );
								  print "<br/>";

												echo money_format($fmt, $price );

								 $vat = $car_row["vat"] * 100;
								 print "<br/>

												" . $car_row["vat_info"] . " @ $vat%
										";

								  $savings = $car_row["rrp"] - $price;
								  print " 		<br/>
												<font color=\"red\">";
													echo money_format($fmt, $savings );
								  print " 		</font><br/> 
												" . $car_row["delivery_time"] . "
											</p>
										</td>
									</tr>
									<tr>
										<td>
										";
											//NOT WORKING!! 
											//$query_cols = "SELECT * FROM colours JOIN car_to_color ON (colours.id = car_to_color.colour_id) WHERE car_id = 1";

											$query_cols = "SELECT * FROM colours JOIN car_to_colour ON (car_to_colour.colour_id = colours.id) WHERE car_to_colour.car_id = '{$car_row['id']}' ORDER BY colours.price ASC";

											//$query_cols = "SELECT DISTINCT colours.id, colours.colour_img, colours.colour, colours.colour_img_alt, colours.price, colours.colour_type FROM colours,cars  WHERE colours.model LIKE '%$vehicle%' AND cars.model LIKE '%$vehicle%'";
											//$query_cols = "SELECT DISTINCT model FROM colours";
											$cols_result = mysql_query($query_cols) or die ("Error in query: $query_cols. ".mysql_error());									if (mysql_num_rows($cols_result) > 0) 
											{
												while ($cols_row = @ mysql_fetch_array($cols_result)) 
												{
									  
										?> 
															<a class='colours' href='#' onmouseout='hideTooltip()' onmouseover='showTooltip(event,"<?php print "" . $cols_row["colour"] . " " . $cols_row["colour_type"] . "<br/>(£" . $cols_row["price"] . ")"; ?>");return false'>
                                      <?php
													print "																
															<img src=\"". $cols_row["colour_img"] ."\" alt='" . $cols_row["colour_img_alt"] . "' /></a>
														  ";
												}
											}


							  
							  print "
										</td>
										<td colspan='2'>
											<div id='CollapsiblePanel" . $car_row["id"] . "' class='CollapsiblePanel'>
											  <div class='CollapsiblePanelTab' tabindex='0'><img class='drop' src='images/drop_down.jpg' alt='Go' /></div>
											  <div class='CollapsiblePanelContent'>
											  		<p>
														<div class='title_tab1'>Standard Specification</div>
															<table class='standard_spec'>
																<tr>
																	<th>
																		Engine Size:
																	</th>
																	<td>
																		".$car_row["engine"]."
																	</td>
																</tr>
																<tr>
																	<th>
																		BHP:
																	</th>
																	<td>
																		".$car_row["bhp"]."
																	</td>
																</tr>
																<tr>
																	<th>
																		Fuel Type:
																	</th>
																	<td>
																		".$car_row["fuel_type"]."
																	</td>
																</tr>
																<tr>
																	<th>
																		Body Style:
																	</th>
																	<td>
																		".$car_row["body_style"]."
																	</td>
																</tr>
																<tr>
																	<th>
																		Number of Doors:
																	</th>
																	<td>
																		".$car_row["no_doors"]."
																	</td>
																</tr>
																<tr>
																	<th>
																		Wheels:
																	</th>
																	<td>
																		".$car_row["wheels"]."
																	</td>
																</tr>
															</table>      
													</p>
									";

								//QUERY FOR TITLE START	
								$query_title = "SELECT * FROM extras JOIN car_to_extra ON (car_to_extra.extras_id = extras.id) WHERE car_to_extra.car_id = '{$car_row['id']}' ORDER BY extras.order ASC";
								$title_results = mysql_query($query_title) or die ("Error in query: $query_title. ".mysql_error());
								$current_heading = '';
								print "<div class='addtional_extras'>";// ADDED TO TRY TO SORT OUT POSITIONING ISSUE
								while ($title_row = @ mysql_fetch_array($title_results )) 
								{
									if ($current_heading != $title_row["title"]) 
									{
    										// The heading has changed from before, so print the new heading here.
   											$current_heading = $title_row["title"];
    										print "	
														<div class='title_tab2'>" . $title_row["title"] . "</div>
											  ";	
  										}


									  ?>
                                            
                                            <a class='data' href='#' onmouseout='hideTooltip()' onmouseover='showTooltip(event,"<?php print "" . $title_row["info"] . "<br/>(£" . $title_row["price"] . ")"; ?>");return false'>
                                      <?php
													print "													
															<img class='extra_img' src=\"". $title_row["img"] ."\" alt='" . $title_row["img_alt"] . "' /></a>


										";	

								}// CLOSES WHILE LOOP ($title_row = @ mysql_fetch_array($title_results )) 
								print "</div>";// CLOSES DIV IMAGE55



						print "												 
											  </div>
										</div>
										<script type='text/javascript'>
										<!--
											var CollapsiblePanel" . $car_row["id"] . " = new Spry.Widget.CollapsiblePanel('CollapsiblePanel" . $car_row["id"] . "', {contentIsOpen:false});
										//-->
										</script>
										</td>
									</tr>
									<tr>
										<td colspan='2'>
											<p class='correct_prices'>
									 			*Prices correct as of ".$car_row["price_check"]."
											</p>
										</td>
									</tr>
								";	

					}

				}


				else
				{
					print "
								<table class='details'>
									<tr>
										<td>Sorry, but we don't seem to have that vehicle available to us.</td>
									</tr>
						  ";
				}


				print "</table>";	

				//echo $num_rows;
				echo "There are ".mysql_num_rows($car_result)." Employee(s).";
				// print the navigation link				
				echo $first . $prev . $nav . $next . $last;	
				?>
						<form name='limit' target='_self' method='GET'>
							<select onchange='document.limit.submit();' name='limit' id='limit'>
                                	<option value='7'>Default</option>
                                    <option value='10'>10</option>
								<option value='15'>15</option>
								<option value='20'>20</option>
                                    <option value='25'>25</option>
							</select>
						</form>



        </div>
	<!-- end content -->
<?php include "sections/right.php";?>
<?php include "sections/footer.php";?>

BLaZuRE · January 13, 2011

Are your $_GET and $_POST variables the values you expect? Use print_r() to verify that:

<pre><?php print_r($your_array); ?></pre>

Is the correct query executing to render page 2? echo your queries and paste them into PHPMyAdmin to test and see whether it errors or runs correctly, as expected.

I don't see anything that catches my eye but you've posted a lot of code. Shorten it to your problem areas and don't post what's not relevant.

BCAV_WEB · January 14, 2011

I think i've resolved the issue, what was happening was the second query that was being used to count the query wasn't being drawn based on the previous search, filtering system. So just simply adding the same restrictions to the querry bar the limiting per page worked the pagination out. T

// how many rows we have in database
				$query   = "SELECT * FROM cars WHERE model  LIKE '%$vehicle%' OR make LIKE '%$vehicle%' OR model_details LIKE '%$vehicle%' OR search LIKE '%$vehicle%' ORDER BY $filter $direction";
				$result  = mysql_query($query) or die('Error, query failed');
				$row     = mysql_fetch_array($result, MYSQL_ASSOC);
				//$numrows = $row['numrows'];
				$numrows = mysql_num_rows($result);

he next issue was that the page links at the bottom didn't remember the query, which was solved by adding the posted across data to the URL.

   $next = " <a href=\"$self?page=$page&limit=$rowsPerPage&vehicle=$vehicle\">[Next]</a> ";

				   $last = " <a href=\"$self?page=$maxPage&limit=$rowsPerPage&vehicle=$vehicle\">[Last Page]</a> ";

Can anyone see any major issues in what i've done? The only thing, I have noticed is when you search for a product say Volkswagen Polo, it sometimes comes up with the else statement for record not found, but the pagination comes up with page 1 and if clicked will bring the results up. This is a little strange as it only happens like 2% of the time.

fenway · January 14, 2011

Yes -- you're getting back all of the data just to count.

BCAV_WEB · January 17, 2011

What is the problem with that?

fenway · January 19, 2011

What is the problem with that?

Slow as hell... use COUNT(*).

DarkKnight2011 · January 20, 2011

can i just ask..... i was told that using something like count(id) was better and faster than using count(*)

That correct??

mikosiko · January 20, 2011

can i just ask..... i was told that using something like count(id) was better and faster than using count(*)

That correct??

the answer is... depend

here is an old article about it but still very much covering the possibles scenarios (read the comments too..one of them in special give more insights to the topic)

http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

BCAV_WEB · January 25, 2011

But the query is being used more than just counting the data, such as displaying data. So surely using the count(*) wouldn't allow this?

Pikachu2000 · January 25, 2011

That isn't what the second chunk of code in your OP indicate. It shows you're selecting every field of every record in the table just to get a count.

BCAV_WEB · January 26, 2011

How much will changing to count(*) make? As the entire query isn't taking too long or I don't notice any major problems.

mikosiko · January 26, 2011

How much will changing to count(*) make? As the entire query isn't taking too long or I don't notice any major problems.

how much is the difference between do things in the right way (or best way possible) or in the no so right way?... there are some thing that are simply "good practices" because in the long term are going to create good programming habits on you and for sure sooner or later are going to save you some grief.

At the end of the day is your choice what to do... but if you came here looking for help and answers I imagine that you are looking also to learn "best practices" right?

BCAV_WEB · January 27, 2011

Fair point, not really looked at that coding in a while, so where about would the count(*) bit go?

fenway · January 28, 2011

In the column list.

BCAV_WEB · January 31, 2011

In the column list :wtf: I've had a look at the coding and my brain just doesn't seem to wanna play ball today

fenway · January 31, 2011

Change your "*" to "COUNT(*)", and then either use a column alias, or mysql_result.

Sign In

Counting returned results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information