[SOLVED] Help for a noob, easy question

[jbrill]

Hey,

 

ok heres the deal, I have two tables in the database. One is "subcategory" it holds all the subcategory's. the field "subcat" is the name of the subcategory and the other field is "cost" which represents the cost of each subcategory.

The other table is what hold the main information about the product.

 

I want to work out an easy math equation to determine the overall price. but in order to do this:

 

if the table "products" where subcat is equal to subcat from the table "subcategory", then display (form the subcategory table) the cost for that subcategory.

 

how do i do this?

[cooldude832]

What?

 

So you wan to get the sum of the cost field?

mysql_query(Select sum(cost) From `subcategory`) or die(mysql_error());

like that?

[teng84]

select subcat, price from subcategory where subact in ( select subcat from subcategory2)

 

hope that make the tricks

[jbrill]

that sounds like what m looking for, anyway you could write that in code?

[trq]

anyway you could write that in code?

 

That is sql.

[teng84]

hmmm thats the code

$result=mysql_query("select subcat, price from subcategory where subact in ( select subcat from subcategory2)");

mysql_fetch_assoc($result);

 

or may want to do the double query instead of single

$result=mysq_fetch_assoc(mysql_query('select subcat from subcategory2'));

$result=mysql_query("select subcat, price from subcategory where subact in ( ".explode(',',$result.")");

mysql_fetch_assoc($result);

 

Hmm there might be an error for i dont test those but what im giving is an idea

 

[teng84]

hmm sorry for this but thorpe may i know your job

I read some of your reply and i like some of it

 

 

[Barand]

Do you mean

<?php

$sql = "SELECT p.prod_name, s.cost
        FROM product p INNER JOIN subcategory s
            ON p.subcat_id = s.subc
        ORDER BY p.prod_name";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>"); 
while (list($prod, $cost) = mysql_fetch_row($res))
{
    echo "$prod : $cost <br />";
}
?>