Skip to main content
J
joenathan20
Participant
September 2, 2009
Question

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

  • September 2, 2009
  • 1 reply
  • 486 views

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

I have 3 database tables:

Dine_optionsDinersSearch_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!

This topic has been closed for replies.

1 reply

David_Powers
David_Powers
Inspiring
September 2, 2009

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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices