找到你要的答案

Q:Use MySQL result in another query

Q:在另一个查询使用MySQL的结果

I have this code which lists everything in tbl_inventory for a given company which works fine:

$result_inventory = mysqli_query($con,"SELECT * FROM tbl_inventory WHERE company_id='$company_id' ORDER BY ip_address")

while($row = mysqli_fetch_array($result_inventory))
echo "<td>{$row['type']} </td>";
echo "<td>{$row['ip_address']} </td>";
echo "<td>{$row['site']} </td>";

The site column gives a number which correlates to an ID in another table which I want to use to run another query such as:

SELECT name FROM tbl_sites WHERE site_id='$site'

However I'm not sure on how to define $site from the previous result

Could someone point me in the right direction?

我有这个代码,列出所有tbl_inventory给定公司的优秀作品:

$result_inventory = mysqli_query($con,"SELECT * FROM tbl_inventory WHERE company_id='$company_id' ORDER BY ip_address")

while($row = mysqli_fetch_array($result_inventory))
echo "<td>{$row['type']} </td>";
echo "<td>{$row['ip_address']} </td>";
echo "<td>{$row['site']} </td>";

该站点列提供了与另一个表中的id相关的数字,我希望使用它来运行另一个查询,例如:

SELECT name FROM tbl_sites WHERE site_id='$site'

然而,我不知道如何定义美元的网站从以前的结果

有人能指出我的方向吗?

answer1: 回答1:

@Rayblade's answer seems legit, however, you can also perform a JOIN-statement between the two tables:

SELECT tbl_sites.name FROM tbl_inventory 
LEFT JOIN tbl_sites ON tbl_inventory.site = tbl_sites.site_id 
WHERE tbl_inventory.company_id='$company_id' 
ORDER BY tbl_inventory.ip_address

This will successfully get the tbl_sites.name column, for every company that matches a site. Here you can of course get any arbitrary number of columns, just make sure to use the table name as prefix, since we now have two tables in the query.

“Rayblade的答案看起来是合法的,但是,你也可以做一个联合声明的两个表之间:

SELECT tbl_sites.name FROM tbl_inventory 
LEFT JOIN tbl_sites ON tbl_inventory.site = tbl_sites.site_id 
WHERE tbl_inventory.company_id='$company_id' 
ORDER BY tbl_inventory.ip_address

这将获得tbl_sites.name柱,每个公司和网站。在这里你当然可以得到任意数量的列,只要确保使用表名为前缀,因为我们现在在查询中有两个表。

answer2: 回答2:

You could use something like:

$result_inventory = mysqli_query($con,"SELECT * FROM tbl_inventory WHERE company_id='$company_id' ORDER BY ip_address")

while($row = mysqli_fetch_array($result_inventory)){
  echo "<td>{$row['type']} </td>";
  echo "<td>{$row['ip_address']} </td>";
  $result2 = mysqli_query($con,"SELECT name FROM tbl_sites WHERE site_id='$row[site]'")
  if ($row2 = mysqli_fetch_array($result2))
    echo "<td>{$row2['name']} </td>";
}

but i think it's a so inefficient

你可以使用类似的东西:

$result_inventory = mysqli_query($con,"SELECT * FROM tbl_inventory WHERE company_id='$company_id' ORDER BY ip_address")

while($row = mysqli_fetch_array($result_inventory)){
  echo "<td>{$row['type']} </td>";
  echo "<td>{$row['ip_address']} </td>";
  $result2 = mysqli_query($con,"SELECT name FROM tbl_sites WHERE site_id='$row[site]'")
  if ($row2 = mysqli_fetch_array($result2))
    echo "<td>{$row2['name']} </td>";
}

但我认为这是一个效率低下

php  mysql