Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    2

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

    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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."';" 
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2010
    Posts
    2
    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?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT GROUP_CONCAT(interest) AS all_interests
      FROM Interests 
     WHERE email = '$email'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •