Using a lookup table [was: php/mySQL Question]

[joenathan20]

I'm stumped here with updating a table in php/mysql

I have 3 database tables:

Dine_options	Diners	Search_terms
*do_id d_id so_id	*so_id diner_name	*d_id st_name

Search_terms holds all "search terms" (ex: food types) .

Dine_options holds values for what diner (so_id) has what particular search terms (d_id)

I'm looking for a way to query results that will show what search terms each diner has.

Example Query:

-----------------------

Search terms from "Leo's Eatery" that are storked in dine_options are displayed with a "X::

Pizza           X

BBQ            X

Hamburger

Bakery        X

Greek

Russian       X

Salads        X

Mexican

-------------------------------

Confused and stumped. Thanks in advance! Cheers!

[David_Powers]

The structure of your dine_options table is wrong. Instead of do_id as your primary key, d_id and so_id should be declared as a joint primary key. This prevents the same combination from being entered more than once.

Your SQL query then becomes:

SELECT diner_name, st_name
FROM Diners, Search_terms, Dine_options
WHERE Diners.so_id = Dine_options.so_id
AND Search_terms.d_id = Dine_options.d_id