Use MySQL result in another query [duplicate]

Question

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?

A perfect example where you's want to use JOIN operations (dev.mysql.com/doc/refman/5.0/en/join.html) — user188654
– user188654, Commented May 2, 2015 at 12:01

Eric · Accepted Answer · 2015-05-02 11:59:25Z

0

@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.

answered May 2, 2015 at 11:59

Eric

19k2 gold badges36 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Over a year ago

This did the trick, thanks - I'm new to PHP/SQL and always wondered when I'd need to start using joins

alexm · Accepted Answer · 2015-05-02 11:43:12Z

0

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

answered May 2, 2015 at 11:43

alexm

1537 bronze badges

Collectives™ on Stack Overflow

Use MySQL result in another query [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related