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

问题描述：

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.

如何从使用引用该外键的外键的子表中获取信息（通过父表）例如： p>

表： user_list strong>（父） p>

  ----------------------------- 
first_name | 姓氏|  ID | 
 ----------------------------- 
 john |  Appleseed |  4 | 
 |  |  | 
  code>  pre> 
 
 表： user_info  strong> :(子） p> 
 
 
  ----  ------------------------------------------ 
电子邮件| 密码|  userlist_ID | 
 ----------------------------------------------  
jappleseed@me.com |  ******* |  4 | 
  code>  pre> 
 
 假设我将John Appleseed的信息存储在一个数组中： p> 
 
 
  $ johnsInfo; 
   code>  pre> 
 
 如何从user_info表中检索John的电子邮件？   p> 
 
 
我正在尝试这个atm： p> 
 
 
  db-＆gt; query（“SELECT * FROM user_info WHERE userlist_ID =：ID”，  $ johnsInfo）; 
  code>  pre> 
 
 不确定这是否是正确的做事方式。 p> 
  div>

答

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

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

相关推荐