Using Join to return one column

[ppunzi]

I have two tables manufacturer and product.  The product table has a column called manufacturer that corresponds to the manufacturer id (key) I am trying to create a table with a single column that has all the rows of the product table but only returns the names from the names column and null if there is no manufacturer.  I used the following inside a case which is sending the results to a tab delimited file:

 

case 'brand': $item[brand] = mysql_query("SELECT  manufacturers.name FROM manufacturers RIGHT JOIN products ON  manufacturers.id = products.manufacturer");

 

The problem is I am receiving resource ID # error messages in the resulting tab delimited file and I am not sure if there is a problem with the usage of RIGHT JOIN or nesting it in a case statement.

 

Thanks for any help.

[ppunzi]

Sorry forgot the guideline info:

 

MySQL version 5.0.91-community-log

 

SQL RIGHT JOIN Syntax:

 

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

 

[gristoi]

You have only queried the database and not fetched the resource fully. You need to loop through the results:

$query = mysql_query( query here ........);

While ( $row = mysql_fetch_array($query))
{

Return results ......
}

[ppunzi]

Thank you for your help.  That makes sense.  I guess my next question is how do I nest that within the CASE if I need the result to be a variable - can I run the RIGHT JOIN and MYSQL_FETCH_ARRAY outside of the Case phrasing and have the result look like a variable from a table to input into the CASE statement?

[kickstart]

Hi

 

Yes. Something like this

 

$query = mysql_query("SELECT  manufacturers.name FROM manufacturers RIGHT JOIN products ON  manufacturers.id = products.manufacturer");

While ( $row = mysql_fetch_array($query))
{
switch (true)
{
case $row['brand'] == 'fred' :
//some code for one brand
break;
case $row['brand'] == 'burt' :
//some code for another brand
break;
}
}

 

All the best

 

Keith