How do I get information (via a parent table), from a child table that's using a foreign key that references the parent.
For example:
Table: user_list (parent)
-----------------------------
first_name | Last Name | ID |
-----------------------------
john | Appleseed | 4 |
| | |
Table: user_info: (child)
----------------------------------------------
email | password | userlist_ID |
----------------------------------------------
jappleseed@me.com | ******* | 4 |
Let's say I have John Appleseed's info stored in an array:
$johnsInfo;
How can I then retrieve John's email from the user_info table?
I'm trying this atm:
db->query("SELECT * FROM user_info WHERE userlist_ID = :ID", $johnsInfo);
Not sure if that's the right way of doing things though.
If this is a real example, you should just make the two tables into one table. Generally if tables have 1:1 correspondence, you should put them together (though, there are special cases when you shouldn't).
Anyway, you'd want to do something like:
SELECT ul.*, ui.* FROM user_list ul
LEFT JOIN user_info ui
ON ui.userlist_ID=ul.ID
WHERE ul.ID = :ID
There are different kinds of joins. Check out the MySQL documents (http://dev.mysql.com/doc/refman/5.0/en/join.html) or search for something like "database joins" to find out which one will end up working for you.
Here is the SQL you need
SELECT user_list.ID
,user_list.first_name
,user_list.last_name
,user_info.email
,user_info.password
FROM user_list
INNER JOIN user_info
ON user_list.ID = user_info.userlist_ID
WHERE user.ID = :ID