Jump to content


Photo

WHERE not working. Please help


  • Please log in to reply
14 replies to this topic

#1 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 02:48 AM

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.finalfant...parent=Hardware

Line 142 is the line:

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

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

}


#2 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 16 August 2006 - 02:50 AM

the where clause should come BEFORE teh order by clause
follow me on twitter @PHPsycho

#3 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 02:57 AM

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

#4 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 16 August 2006 - 03:08 AM

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.
follow me on twitter @PHPsycho

#5 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 03:27 AM

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.finalfant...parent=Hardware


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

}


#6 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 16 August 2006 - 03:40 AM

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.
follow me on twitter @PHPsycho

#7 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 03:45 AM

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?

#8 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 16 August 2006 - 03:54 AM

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

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?
follow me on twitter @PHPsycho

#9 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 04:03 AM

one record for each product.

#10 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 16 August 2006 - 04:06 AM

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...
follow me on twitter @PHPsycho

#11 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 04:09 AM

so you want me to add a field stating how many are in stock?

#12 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 16 August 2006 - 04:10 AM

I don't want you to do anything - but It woud most certainly help you in the long AND short run.
follow me on twitter @PHPsycho

#13 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 04:11 AM

ill do it.

#14 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 16 August 2006 - 04:13 AM

just added you to msn if you have it.
follow me on twitter @PHPsycho

#15 Nothadoth

Nothadoth
  • Members
  • PipPipPip
  • Advanced Member
  • 119 posts

Posted 16 August 2006 - 04:16 AM

I do. I haven't had a popup though




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users