Nothadoth Posted August 16, 2006 Share Posted August 16, 2006 I am using the WHERE statement to select from the products database table where the product category name is the same as the category showing so that I can say how many items are in that category.However it is giving me errors. Can somebody tell me why?The variable $parent does hold the info because I tested it by printing it.Here is my url: http://www.finalfantasyfan.net/igbltd/browse.php?mode=Computing&subparent=HardwareLine 142 is the line:while($productnumber = mysql_fetch_array($productnoquery)) { [code]$parent = $_GET['subparent']; // Connect to Navigation database tablemysql_connect('localhost','hidden','hidden'); mysql_select_db('noth_igbltduk'); $querycats = mysql_query("SELECT * FROM productcategories_comp ORDER BY name ASC");while($product = mysql_fetch_array($querycats)) { $productnoquery = mysql_query("SELECT * FROM products ORDER BY id DESC LIMIT 1 WHERE parent='$parent'");while($productnumber = mysql_fetch_array($productnoquery)) { $productno = $productnumber['id']; } if ($cc == 4) {print "</tr><tr>";$cc = 1;} else {$cc++;} print "<td width='25%' valign='top'> <table border='0' cellpadding='0' cellspacing='5' style='border-collapse: collapse; margin-top: 5' bordercolor='#111111' width='100%'> <tr> <td width='100%' valign='top'><center><b>".$product['name']." (".$productno.")</b></center></td> </tr> <tr> <td width='100%' valign='top'><center><a href='".$ROOT."browse.php?catmode=".$product['name']."'><img src='".$ROOT."shop/".$product['parent']."/".$product['thumb_img']."' border='0'></a></center></td> </tr> </table> </td>";}[/code] Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 16, 2006 Share Posted August 16, 2006 the where clause should come BEFORE teh order by clause Quote Link to comment Share on other sites More sharing options...
Nothadoth Posted August 16, 2006 Author Share Posted August 16, 2006 but i need the order by clause to determine the highest product id, so that i can say how many items are in the category.Is there a way to find out how many rows are in a table where the parent field is equal to $parent?thanks Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 16, 2006 Share Posted August 16, 2006 What????????SELECT * FROM products WHERE parent='$parent' ORDER BY id DESC LIMIT 1 Will do this.1. Select every field from products where parent=$parent.2. Order the results by id in descending order.3. Limit the number of results retured to 1 result.if you want to know the number of items you can either do$qry = "SELECT * FROM products WHERE parent='$parent'";$qry = mysql_query($queyr);$num = mysql_num_rows($query);or use the COUNT in the query itself.SELECT COUNT('id') FROM products WHERE parent='$parent' The latter being more efficient UNLESS you are going to use all the information in the table then use teh former query. Quote Link to comment Share on other sites More sharing options...
Nothadoth Posted August 16, 2006 Author Share Posted August 16, 2006 Ok i used the order by desc limit 1 one because I couldn't get the count to work.however, it wont show a value: http://www.finalfantasyfan.net/igbltd/browse.php?mode=Computing&subparent=Hardware[code]$parent = $_GET['subparent']; // Connect to Navigation database tablemysql_connect('localhost','hidden','hidden'); mysql_select_db('noth_igbltduk'); $querycats = mysql_query("SELECT * FROM productcategories_comp ORDER BY name ASC");while($product = mysql_fetch_array($querycats)) { $productnoquery = mysql_query("SELECT * FROM products WHERE parent='$parent' ORDER BY id DESC LIMIT 1 ");while($productnumber = mysql_fetch_array($productnoquery)) { $productno = $productnumber['id']; } if ($cc == 4) {print "</tr><tr>";$cc = 1;} else {$cc++;} print "<td width='25%' valign='top'> <table border='0' cellpadding='0' cellspacing='5' style='border-collapse: collapse; margin-top: 5' bordercolor='#111111' width='100%'> <tr> <td width='100%' valign='top'><center><b>".$product['name']." (".$productno.")</b></center></td> </tr> <tr> <td width='100%' valign='top'><center><a href='".$ROOT."browse.php?catmode=".$product['name']."'><img src='".$ROOT."shop/".$product['parent']."/".$product['thumb_img']."' border='0'></a></center></td> </tr> </table> </td>";}[/code] Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 16, 2006 Share Posted August 16, 2006 You have a while loop to assign product number - it will only ever assign the ID of the product not the number in stock and there will only ever be 1 record so no use using a while loop there!The methdo you use is at risk because you generate a query within a loop and many servers are set up to limit the number of queries per script to 50 (depending on the settings for teh user the script belongs to) so you could hit trouble there at some point.This I think is one of those where you must extract ALL the info from each table and put it into an array. Then use hash tables to grab the bits you need out of each array. Quote Link to comment Share on other sites More sharing options...
Nothadoth Posted August 16, 2006 Author Share Posted August 16, 2006 So how would I do this?Im new to php. Could you tell me? In the products database there is a field called sub_parent which will be equal to the variable $subparent.I need to count how many rows there are with the sub_parent field being the same as $subparent or something? To see how many items are in each category. How can I do this? Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 16, 2006 Share Posted August 16, 2006 [code]<?php$qry = mysql_query("SELECT * FROM productcategories_comp ORDER BY name ASC");$cats = array();while($row = mysql_fetch_assoc($qry)) { foreach($row as $key => $val) { $cats[$key][] = $val; }}$qry = mysql_query("SELECT * FROM products WHERE parent='$parent' ORDER BY id DESC");$products = array();while($row = mysql_fetch_array($qry)) { foreach($row as $key => $val) { $products[$key][] = $val;}?>[/code]that generates two (potentially massive) arrays of categories and products.BUT before I go on I need to know your table structure.Are you creating a new record for each item in the products OR (and better to do) are you storing one record for each product and having a field in that record of how many you have in stock? Quote Link to comment Share on other sites More sharing options...
Nothadoth Posted August 16, 2006 Author Share Posted August 16, 2006 one record for each product. Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 16, 2006 Share Posted August 16, 2006 LOLSuggest you cahnge it now - it will save you lots of time hassel and not to mention a significant amount of disk space in the not too distant... Quote Link to comment Share on other sites More sharing options...
Nothadoth Posted August 16, 2006 Author Share Posted August 16, 2006 so you want me to add a field stating how many are in stock? Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 16, 2006 Share Posted August 16, 2006 I don't want you to do anything - but It woud most certainly help you in the long AND short run. Quote Link to comment Share on other sites More sharing options...
Nothadoth Posted August 16, 2006 Author Share Posted August 16, 2006 ill do it. Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 16, 2006 Share Posted August 16, 2006 just added you to msn if you have it. Quote Link to comment Share on other sites More sharing options...
Nothadoth Posted August 16, 2006 Author Share Posted August 16, 2006 I do. I haven't had a popup though Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.