在另一个查询中使用MySQL结果

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

我有这个代码列出了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>";

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:

站点列给出一个数字,该数字与另一个表中的ID相关,我想用它来运行另一个查询,例如:

SELECT name FROM tbl_sites WHERE site_id='$site'

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

但是,我不确定如何从以前的结果定义$ site

Could someone point me in the right direction?

有人能指出我正确的方向吗?

2 个解决方案

#1

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

@ Rayblade的答案似乎是合法的,但是,你也可以在两个表之间执行JOIN语句:

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.

这将成功获取与网站匹配的每家公司的tbl_sites.name列。在这里你当然可以获得任意数量的列,只需确保使用表名作为前缀,因为我们现在在查询中有两个表。

#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

但我认为这是一个非常低效的

#1