Jump to content

Archived

This topic is now archived and is closed to further replies.

Nothadoth

WHERE not working. Please help

Recommended Posts

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=Hardware

Line 142 is the line:

while($productnumber = mysql_fetch_array($productnoquery)) {

[code]$parent = $_GET['subparent'];
 
// Connect to Navigation database table
mysql_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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 table
mysql_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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
[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?

Share this post


Link to post
Share on other sites
LOL

Suggest 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...

Share this post


Link to post
Share on other sites
I don't want you to do anything - but It woud most certainly help you in the long AND short run.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.