SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Sep 2003
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question A different question about selecting from 2 tables

    I have searched for hours and have tried many different solutions, but just can't find what I'm looking for. I'm sorry to post, but desperate at this point!

    Here's what I'm trying to do ... Let's say I have a database that stores user information, including a user's favorite colors (multiple). 2 tables, the first contains user information, the second contains color information.

    The userTable contains a userFAVORITECOLOR_ID field, which is a number associated with the colorNAME field in the colorTable.

    The user can have multiple favorite colors (the userFAVORITECOLOR_ID field in the userTable contains comma separated numbers).

    I want to display records, including the user's name and their favorite colors. This is what I have so far.

    $query = "SELECT *
    FROM userTable, colorTable
    WHERE userTable.userFAVORITECOLOR_ID = colorTable.userFAVORITECOLOR_ID


    I get results, but when a user has more than one favorite color, only the first is returned and the rest are not.

    Thanks for any help!

    userTable-------
    userID (primary key)
    userNAME
    userFAVORITECOLOR_ID

    colorTable-------
    userFAVORITECOLOR_ID (primary key)
    colorNAME

  2. #2
    SitePoint Wizard holmescreek's Avatar
    Join Date
    Mar 2001
    Location
    Northwest Florida
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by timpossible
    $query = "SELECT *
    FROM userTable, colorTable
    WHERE userTable.userFAVORITECOLOR_ID = colorTable.userFAVORITECOLOR_ID


    I get results, but when a user has more than one favorite color, only the first is returned and the rest are not.

    Thanks for any help!

    userTable-------
    userID (primary key)
    userNAME
    userFAVORITECOLOR_ID

    colorTable-------
    userFAVORITECOLOR_ID (primary key)
    colorNAME

    In your second table, create a field for the user id.

    userTable-------
    userID (primary key)
    userNAME


    colorTable-------
    colorTable_ID (primary key)
    colorTable_userID
    colorNAME

    Thus you would have :

    User Table
    16 John Doe
    17 Bob Smith

    Index Userid Color
    1 16 Blue
    2 16 Green
    3 16 Yellow
    4 17 Blue
    5 17 Green

    Thus John Doe likes Blue, Green, Yellow and Bob Smith likes Blue and Green.


    $sql = "select userTable.userName as UTID, colorTable_userID as CT_UID, colorName from userTable, color_Table where UT_ID=CT_UID"


    You could also could create a third table of color names, then in the color table insert the id of the color names into an int field stored in the color table. Your tables in that case would be like :

    User Table
    16 John Doe
    17 Bob Smith

    Color Index
    Index Userid Color
    1 16 100
    2 16 101
    3 16 102
    4 17 101
    5 17 102

    Color Table
    Index Color Name
    100 Red
    101 Green
    102 Blue
    intragenesis, llc professional web & graphic design


Bookmarks

Posting Permissions

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