Query For Multiple Foreign Key To Same Table

[robk27]

I have the following tables:

 

USERS

-id

-fname

-lname

 

COMPANIES

-id

-name

-type

 

PRODUCTS

-id

-product

-restaurant (linked to companies.id)

-vendor (linked to companies.id)

-user (linked to users.id)

-transaction_date

 

 

I have the following code to try to output data from the Products table but it isn't working:

 

$sql = mysql_query("SELECT products.*, companies.name FROM products INNER JOIN companies ON products.vendor = companies.id ON products.restaurant = companies.id WHERE vendor='$id' AND companies.type='$type'") or die(mysql_error());
$num=mysql_num_rows($sql);
<th><h5>Date</h5></th>
<th><h5>Meal</h5></th>
<th><h5>Restaurant</h5></th>
<th><h5>Vendor</h5></th>
$i=0;
while ($i < $num) {
$f1=mysql_result($result,$i,"transaction_date");
$f2=mysql_result($result,$i,"product");
$f3=mysql_result($result,$i,"restaurant");
$f4=mysql_result($result,$i,"vendor");
<td><?php echo $f1; ?></td>
<td><?php echo $f2; ?></td>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>

$i++;
}

 

I looked on another site and found this answer:

http://stackoverflow...-keys-php-mysql

 

but that doesn't help.

 

The problem is that I can only get either the restaurant or vendor name to show but not both. The $id and $type are created with the session, fyi.

 

Any ideas? Thank you

Edited October 19, 2012 by robk27

[JohnTipperton]

can you provide your .sql script ill do the query.

[DavidAM]

You have to join the table twice. Once it is acting as a "Vendor" table and once it is acting as a "Restaurant" table:

 

SELECT products.*, Vendors.name AS Vendor, Restaurants.name as Restaurant
FROM products INNER JOIN companies AS Vendors ON products.vendor = Vendors.id 
JOIN companies AS Restaurants ON products.restaurant = Restaurants.id 
WHERE vendor='$id' 
-- AND companies.type='$type'

 

I'm not sure what to do with that last line. I'm guessing it would be something like:

 

AND Vendors.type = 'Vendor' -- or whatever code you use for vendor records
AND Restaurants.type = 'Restaurant' -- or whatever code you use for restaurant records

 

but, if the id in companies is unique, I don't think you need to check the type at all