0

I have the following that grabs all the categories under a parent category:

<?php
$childrows = array();
$result2 = mysql_query("SELECT * FROM categories WHERE category_parent_id='$cid'"); while
($row = mysql_fetch_array($result2)){
$childrows [] = $row['category_id'];
print_r($childrows); }
?>

This returns:

Array ( [0] => 47 ) Array ( [0] => 47 [1] => 48 ) Array ( [0] => 47 [1] => 48 [2] => 63 ) Array ( [0] => 47 [1] => 48 [2] => 63 [3] => 64 ) Array ( [0] => 47 [1] => 48 [2] => 63 [3] => 64 [4] => 68 ) Array ( [0] => 47 [1] => 48 [2] => 63 [3] => 64 [4] => 68 [5] => 69 )

The problem im having is the next stage, I need to count the number of products(product_id) under the categories(category_id) stored in the array from the products table.

There must be a way!!??

Thanks for looking. B.

Full code:

    <?php
    if ($num_rows == 0) {
$result2 = mysql_query("SELECT * FROM categories WHERE category_parent_id='$cid'");
while ($row = mysql_fetch_array($result2)){
$childrows [] = $row['category_id'];
}
$category_string = implode(",",$childrows);
$result3 = mysql_query("SELECT   category_id, COUNT(product_id) 
                        FROM     products                         
                        WHERE category_id IN ($in_string)");
$result3 = mysql_fetch_array($result3);
$result3 = $result3[1];
echo $result3;
}
else {
echo $num_rows;
}
    ?>

but this just reurns:

Notice: Undefined variable: in_string in on line 31(WHERE category_id IN ($in_string)");)

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on line 32 ($result3 = mysql_fetch_array($result3);)

Improve this question
2
  • You need to fetch them.... $result3 = mysql_query(...); echo $result3; Commented Aug 18, 2009 at 9:47
  • but that iswhat i have done...is it not? Commented Aug 18, 2009 at 9:49

3 Answers 3

Reset to default
1 
$result3 = mysql_query("SELECT   category_id, COUNT(product_id) 
                        FROM     products                         
                        WHERE category_id IN ($in_string)");
$result3 = mysql_fetch_array($result3);
$result3 = $result3[1];
echo $result3;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

it doesn't like - result3[1] --> unexpected '['
0

First of all, beware of SQL injection, you should at least mysql_real_escape_string($cid) if $cid is untrusted input (input from a user.)

Your question is not particularly clear to me, but you just do a second query getting the amount of products per category given the array, you can do it like this:

/* After obtaining $childrows */
$category_string = implode(",",$childrows);
$result3 = mysql_query("SELECT COUNT(product_id) FROM products 
                        WHERE category_id IN ($category_string)");

$result3 will contain the handle to the recordset of the single number representing the count of all products, counted once per each category they are in, which belong to the categories in the $childrows array

Improve this answer

5 Comments

I think he wants all products, without the group by
Maybe... as I say, ambiguous question
Yes *Svetlozar I would just like a single number returned totaling the number of products that fall under all the child categories of a perticular parent category....sorry if I didnt explain very well!
Of a particular parent or of all parent categories you got in your array?
the array contains all the child categories of a perticular parent, I would like the total number of products under all the child categories under that perticular parent...
0 
SELECT COUNT(product_ID)
FROM   product
WHERE  category_id IN (HERE you implode your categories)
Improve this answer

4 Comments

yes a product can be under a few categories, but say it was in 2 categories i would like it to appear in the count of both.
Then do not use DISTINCT COUNT, and leave the query as is
No, I think he means that he wants them to be counted twice, not to appear in each category.
like this: SELECT COUNT(product_ID) FROM product WHERE category_id IN (implode(",",$childrows)); ????

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.