using array in sql query

Question

Hi,

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);)

Answer 1

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

Answer 2

SELECT COUNT(product_ID)
FROM   product
WHERE  category_id IN (HERE you implode your categories)

Answer 3

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