CODEWITHSUNDEEP
phpmysql
user6362696
user6362696

Reputation: 51

Run 2 queries together but execute separately

I currently have 2 separate queries which i need to run together, but execute separately, and i was wondering what would be the best practice to do this.

I have a query that dynamically prints out restaurants each and every time one is added in the admin page, and i have a query that works out if the restaurant is open or closed. I thought this would be simple by executing the 2 separate queries then saving the opening times echo into a variable and putting this variable inside the loop, however, that does not seem to be working. By not working I mean the wrong id is being called as the opening hrs query is before the dynamic print out query, and when it is after the query works the plan, but then my echo/print out variable does not work. I am struck and have no idea how to move forward.

Opening hr query

$query = mysqli_query($dbc, "SELECT * FROM Opening_hrs 
  WHERE Restaurant_ID='$rest_id' AND Day_of_week = DATE_FORMAT(NOW(), '%w')
  AND CURTIME() BETWEEN Open_time AND Closing_time");

$run_qu = $dbc->query($query);

if($run_qu->num_rows>0){
    while($row_qu=$run_qu->fetch_assoc()){
        $message= "open" .$row_qu["Open_time"]."</br>";
    }
} else {
    $message=$message. "close".$row_qu["Closing_time"]."</br>";
}

Dynamic query

$sql = mysqli_query($dbc, "SELECT Rest_Details.Resturant_ID,  Rest_Details.Resturant_name,,Delivery_Pcode.Pcode 
 FROM Rest_Details INNER JOIN Delivery_Pcode
 ON Delivery_Pcode.Restaurant_ID=Rest_Details.Resturant_ID
 WHERE Delivery_Pcode.Pcode LIKE '%" . $pcode . "%'") or die("could not search!");
echo var_dump($sql);

$count = mysqli_num_rows($sql);
if ($count === 0) {
    $output = '<b>we dont deliver to ' . $pcode . '</b></br>';
} else {
    $i = 1;
}
while ($row_prods = mysqli_fetch_array($sql)) {
    $rest_id = $row_prods['Resturant_ID'];
    $rest_name = $row_prods['Resturant_name'];

    $output = $output . '<div id="products">' .
            ' <p id="rest_name">' . $rest_name . '</p>' .
            '<p> '.$message.' </p>' .;
    $i++;
}
}

Upvotes: 1

Views: 92

Answers (2)

maxhb
maxhb

Reputation: 8865

If you want to have a list of all restaurants which will deliver within a certain range of plzs and their opening hours then you could use this query:

$sql = '
SELECT
    Rest_Details.Resturant_ID,
    Rest_Details.Resturant_name,
    Delivery_Pcode.Pcode,
    EXISTS(
        SELECT *
        FROM Opening_hrs
        WHERE
            Restaurant_ID=' . $rest_id . ' AND
            Day_of_week = DATE_FORMAT(NOW(), "%w") AND
            CURTIME() BETWEEN Open_time AND Closing_time
    ) as isOpen
FROM Rest_Details INNER JOIN Delivery_Pcode
ON Delivery_Pcode.Restaurant_ID=Rest_Details.Resturant_ID
WHERE Delivery_Pcode.Pcode LIKE "%' . $pcode . '%"
';

Mysql exist() will return 1 if one or more rows match the the specified query and 0 otherwise. as isOpen will make this value accesibble in column with name isOpen.

Upvotes: 0

Barmar
Barmar

Reputation: 781004

You should join the two queries:

SELECT
    Rest_Details.Resturant_ID,
    Rest_Details.Resturant_name,
    Delivery_Pcode.Pcode,
    Opening_hrs.Open_time,
    Opening_hrs.Closing_time
FROM Rest_Details
JOIN Deliver_Pcode ON Delivery_Pcode.Restaurant_ID=Rest_Details.Restaurant_ID
LEFT JOIN Opening_hrs ON Opening_Hrs.Restaurant_ID=Rest_Details.Restaurang_ID
    AND Day_of_week = WEEKDAY(NOW()) AND CURTIME() BETWEEN Open_time AND Closing_time
WHERE Delivery_Pcode.Pcode LIKE '%$pcode%'

Because it's a LEFT JOIN, Open_time and Closing_time will be filled in if the restaurant is open, otherwise they will be NULL. So the PHP that displays the results can check this:

while ($row_prods = mysqli_fetch_array($sql)) {
    $rest_id = $row_prods['Resturant_ID'];
    $rest_name = $row_prods['Resturant_name'];

    $output .= '<div id="products">' .
            ' <p id="rest_name">' . $rest_name . '</p>';
    if ($row_prods['Open_time']) {
        $output .= '<p> open ' . $row_prods['Open_time'] . ' close ' . $row_prods['Close_time'];
    }
    $output .= "</div>";
    $i++;
}

Upvotes: 1

Related Questions