Select Multi-Valued Attribute in separate table w/ Foreign Key

jef3189 · #1 (**permalink**) 12-06-10, 11:49

Hello, I have a database structure:

Table Users:
--------------------------------
email (PK) | firstname | lastname
--------------------------------
johndoe@blah.com | John | Doe

then a User can have many interests (multi-valued) so I created another table called interests.

Table Interests:
--------------------
email (FK) | interest
--------------------
johndoe@blah.com | Surfing
johndoe@blah.com | Fishing

Now I want to select all the interests of a user where the email=$email.

I simply did:

Code:

$query="SELECT interest from Interests WHERE email=$email"

However it is only retrieving the FIRST row where email=$email.
i.e. It is returning:
| johndoe@blah.com | Interests: Surfing.

How can I retrieve ALL values of interest where the email matches?
i.e I want to Display:
| johndoe@blah.com | Interests: Surfing, Fishing.

healdem · #2 (**permalink**) 12-06-10, 12:01

presumably you are using PHP

text literals need to be encapsualted with a " or ' to delimit the value

try

PHP Code:


			
$query="SELECT interest from Interests WHERE email = '".$email."';"

jef3189 · #3 (**permalink**) 12-06-10, 12:11

thanks healdem, I am already doing this even though I forgot to include them in my code snippet.

The query in my code is verbatim:

Code:

$query = "SELECT interest from Interests WHERE email = '$email'";

This works and retrieves only ONE tuple from the table for some reason. I need it to return multiple tuples. More specifically ALL the tuples where the email = $email.

Why does the query stop at the first match? Why does it not continue to return more tuples after it finds the first?

healdem · #4 (**permalink**) 12-06-10, 13:37

so have you examined the sql being sent to the server?
have you checked to make certain there are multiple records?
have you run the same query that you are running in PHP in a query tool such as HeidiSQL or SQL query browser?
are you running your db server on *NIX or Windows.. Windows can have issues with capitalisation, whereas *NIX doesn't.. if you are runnign on windows check the column and table defintions

have you tried the variant of SQL I suggested?

r937 · #5 (**permalink**) 12-06-10, 13:46

try this --

Code:

SELECT GROUP_CONCAT(interest) AS all_interests
  FROM Interests 
 WHERE email = '$email'

mike_bike_kite · #6 (**permalink**) 12-07-10, 02:15

Could you just show us the php you use to pull each record? I'm guessing you don't have a loop to pull each record.

Internet Services

Web Development

Digital Marketing

Consumer Tech