BCAV_WEB

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

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

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

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?

What is the problem with that?

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.

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";?>

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?

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'];

I've figured out, it seems on my orginial query I was ordering by price so all the £0.00 came first regardless of the title, so the first fix was to order by title but that then put upholstery at the bottom. So I created an new field called order, to arrange the section to my personal preference. So all sorted now

Okay, well we are doing a query which is to return data on additional extras, so title (Interior), images (image/extras/interior_light.jpg) price etc... The first point is that each inserted bit of data will have a repeating title ie interior, leather seats and interior, cloth seats. So we don't want them to repeat so I'm doing the following if statement, just ignore the while and the print at the top. But the first thing I'm doing is defing the variable $current_heading as nothing then I'm saying within the while query, if variable $current_heading is not retrived data field title (interior) etc... then display, which worked about an hour ago when I had 6 bits of data 3 upholstery and 3 interior. Since adding extra data such as packages, safety & technology, it has stopped working. Duplicating titles, my inital thought was I might have accidently added a space meaning that it wouldn't be the same as the previous one, but on inspection of the data this isnt the issue. I hope that explains it a little better. Any ideas of whats going wrong? $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> "; }

Hi, I'm having a slight issue with some coding (see below). It worked a few minutes ago before I add "Packages" & "Safety & Technology" titles to the MySQL database. Hopefully the attached image has worked, but if it hasn't go to www.bikescarsandvans.co.uk/test.php and select the first Audi A3 additional extras drop down menu and you'll see whats going wrong. $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.price 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

Done, it Ive just had to play about with the CSS, setting a width and reducing padding and loads of junk like that but sorted now. Thanks for your help

I've just downloaded and looked at the image you've attached and you don't seem to go on the correct bit. The grey bar that says "Additional Extras" drops down and in there are images, such as walnut wood interior etc... If I limit it to one per line the drop down box goes stupidly to long, if a car has say 20 additional extras. Ps. Sorry for the late repoly I've had a few days off from work

Just realised that if I have even number with the MySQL result, the CSS of it all looks okay. So would a work around be to have a bit of coding that counted the rows and if odd, stick in an extra blank bit of data? Also on the manual inputted on (details.php) the coding has a <br/> after two; see example below, just ignore the $title_row["info"] etc... and imagine prices, text etc... *Also please note there is only the 1st Audi that has images in the additonal extras bit on the test.php page. <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> <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><br/> <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> <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><br/>