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!
userID (primary key)
userFAVORITECOLOR_ID (primary key)