Getting info from tables, from multiple variables

[derek mcgilvray]

Hi,
I wonder if anyone can point me in the right direction with this.
I did this:
$query = "SELECT * FROM products_to_categories WHERE cat_id='$cat_id'";
then found prod_id by doing this:
$num=mysql_numrows($result), $i=0;, while ($i < $num), etc and
$prod_id=mysql_result($result,$i,"prod_id");

Now, all these prod_ids that are being returned in the loop, how do I use them in another query? i.e. how do I do this:
select * from another_table where prod_id=$prod_id (all of them)?
Can someone give me a clue please? It would be much appreciated.

Derek

[scriptmasterdel]

Hi Derek,

You can do it the way you suggested by creating a mysql_query for each of the items in the loop of you can create a collection of the data using one query before you do the loop and then the collected data can be referenced later.

e.g.

$result = mysql_query("SELECT * FROM products_to_categories WHERE cat_id='$cat_id'");
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$prod_id=mysql_result($result,$i,"prod_id");
$subQuery = mysql_query("select * from another_table where prod_id=$prod_id");
while ($rowQuery = mysql_fetch_assoc($subQuery)) {
// ... show the data collected
}
}

... or ...

Create the collection to be used later:

$subQuery = mysql_query("select * from another_table");
while ($rowQuery = mysql_fetch_assoc($subQuery)) {
$preCollection[$rowQuery['idField']] = $rowQuery;
}

Then later you can call the field you require from the array:

$result = mysql_query("SELECT * FROM products_to_categories WHERE cat_id='$cat_id'");
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$prod_id=mysql_result($result,$i,"prod_id");
echo $preCollection[$prod_id]['field'];
}

I a aware that most of the code is incorrect but i am displaying it in this was purely as an example

Hopefully you will understand the angle i am coming from.

I would use the second method because it created less strain on the server and optimizes the code!

Good luck and if you have any more problems then please let me know.

[derek mcgilvray]

Thank you very much for your help Scriptmasterdel, it's working well now.