使用从另一个表中检索的数据从表中检索数据

I need to get the id from one table in a database and use that id as a "key" in order to get the name from another table. For example

我需要从数据库中的一个表中获取id并将该id用作“密钥”,以便从另一个表中获取名称。例如

 $result2 = mysql_query("SELECT * FROM XXX WHERE id=8 ") 
 or die(mysql_error());  

 while($row = mysql_fetch_array( $result2 )) {

echo $row['client_id'];

Here i get the client_id from the table 1. Now how do i implement that into this so that it returns the name and last name only of those that have the above id

在这里,我从表1中获取client_id。现在我如何实现它,以便它只返回具有上述id的名称和姓氏

$data = mysql_query("SELECT firstname, lastname FROM YYY WHERE ???? :S ") 
or die(mysql_error()); 
Print "<table border cellpadding=3>"; 
while($info = mysql_fetch_array( $data )) 
   { 
   Print "<tr>"; 
   Print "<th>fname:</th> <td>".$info['firstname'] . "</td> "; 
   Print "<th>lname:</th> <td>".$info['lastname'] . " </td></tr>"; 
} 
Print "</table>"; 
?>

4 个解决方案

#1

If you only need to take the client_id from XXX table, use a subquery:

如果您只需要从XXX表中获取client_id,请使用子查询:

$data = mysql_query("SELECT firstname, lastname FROM YYY WHERE id IN (SELECT client_id FROM XXX WHERE id=8 )") .

If XXX.id is PRIMARY KEY, then that subquery will return just one row, so instead if using id IN (SEL...), use id = (SEL...

如果XXX.id是PRIMARY KEY,那么该子查询将只返回一行,所以如果使用id IN(SEL ...),则使用id =(SEL ...

#2

Why not this:

为什么不这样:

$data = mysql_query("SELECT firstname, lastname FROM YYY WHERE id ". $row['client_id']) ;
or die(mysql_error());

#3

use this

  SELECT firstname, lastname FROM YYY WHERE id IN (SELECT client_id FROM XXX WHERE id=8 )

#4

Just to answer the question:

只是回答这个问题:

$sql = "SELECT firstname, lastname FROM YYY WHERE client_id = ".$row['client_id']."";

But, as mentioned, do not use mysql_ functions, ase they are deprecated and will be removed in the future. This means that your code won't be running for a long time if you still use them.

但是,如上所述,不要使用mysql_函数,但是它们已被弃用,将来会被删除。这意味着如果您仍然使用它们,您的代码将不会长时间运行。

Also, I guess it would be more efficient and more elegant to solve this with MySQL JOINs. If you have difficulty in implementing this, just ask for help.

此外,我想用MySQL JOIN解决这个问题会更高效,更优雅。如果您在实施中遇到困难,请寻求帮助。

A simple example for a query with a JOIN is the following:

使用JOIN查询的简单示例如下:

$sql = "
SELECT 
  yy.firstname, yy.lastname 
FROM xx 
JOIN yy ON yy.client_id = xx.client_id 
WHERE xx.id = ".$yourID."
";

#1