一起运行 2 个查询但单独执行 [英] Run 2 queries together but execute separately

问题描述

我目前有 2 个单独的查询需要一起运行，但分别执行，我想知道这样做的最佳做法是什么.

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.

我有一个查询，每次在管理页面中添加一个餐厅时，都会动态打印出餐厅，并且我有一个查询，可以确定餐厅是开放还是关闭.我认为通过执行 2 个单独的查询然后将打开时间 echo 保存到一个变量中并将这个变量放入循环中会很简单，但是，这似乎不起作用.不工作是指错误的 id 被调用，因为打开的 hrs 查询是在动态打印输出查询之前，当它在查询执行计划之后，但是我的 echo/print out 变量不起作用.我很震惊，不知道如何前进.

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.

打开人力资源查询

$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>";
}

动态查询

$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++;
}
}

推荐答案

你应该加入两个查询:

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%'

因为是LEFT JOIN，Open_time 和Closing_time 会在餐厅营业时填写，否则会是NULL.所以显示结果的PHP可以检查这个:

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++;
}

这篇关于一起运行 2 个查询但单独执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！