如何使用PHP中的PDO使用PDO从子表中获取信息？

Question

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 |
----------------------------------------------
[e-mail]  |   ******* |       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.

Answer 1

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.