Jump to content

WHERE not working. Please help


Nothadoth

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]
Link to comment
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.
Link to comment
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]
Link to comment
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.
Link to comment
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?
Link to comment
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?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.