harkly harkly - 1 month ago 9
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()) {
$category_id=$r["category_id"];

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

while ($r=$searchBus->fetch_assoc()) {
$name=$r["name"];
$street=$r["street"];

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

You can make it into single query too.

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

$searchBus = $mysqli->query($query);