harkly harkly - 1 year ago 109
MySQL Question

MySQL combining several select statements

Wondering how/if I can combine the 2 Select statements.

$searchCat = $mysqli->query("SELECT category_id, category.name FROM category WHERE category.name = '$searchTerm'");

while ($r=$searchCat->fetch_assoc()) {

$searchBus = $mysqli->query("SELECT business.name, business.street FROM business WHERE business.category_id = '$category_id'");

while ($r=$searchBus->fetch_assoc()) {

echo "$name, $street<br>";
} // end of while

For output I only need the data from the Business table, the 1st Select finds the correlating ID of the searched term, that Id is also housed in the Business table.

So can I do a join or union where the 1st Select gets the id and then it is passed to the 2nd Select allowing me to skip the 1st While?

Answer Source

You can make it into single query too.

$query = "SELECT `business`.`name`, `business`.`street` 
          FROM `business`, `category` 
             `business`.`category_id` = `category`.`category_id` 
             `category`.`name` = '$searchTerm'";

$searchBus = $mysqli->query($query);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download