结合两个单独的MySQL查询的结果 [英] Combining the results of two separate MySQL queries

问题描述

我正在尝试执行两个单独的数据库查询，并将结果返回到表单.每个结果都写入表中.

I'm trying to do two separate database quires and return the results to a form. Each result is written to a table.

我希望能够将两个查询合并为一个，并按任务编号对结果进行排序.

I want to be able to combine the two queries into one and order the results by task number.

第一查询:

 //Booking  
    $Date= date("d/m/Y");
    $driver = $_SESSION['username'];
    $dbTaskRecords = "SELECT * FROM booking WHERE driver='$driver' AND Date= CAST('$Date_search' AS DATE) ORDER BY TaskNo ASC"; 
    $dbTaskRecords_result = mysql_query($dbTaskRecords);

第二查询:

//Return Booking    
    $dbTaskReturn = "SELECT * FROM returnbooking WHERE driver='$driver' AND Date= CAST('$Date_search' AS DATE) ORDER BY TaskNo ASC";    
    $dbTaskReturn_result = mysql_query($dbTaskReturn);

然后将结果通过while语句输出到页面.

The results are then outputted to the page through a while statement.

$i=0;
        while ($row = mysql_fetch_array($dbTaskRecords_result)){
        //Control Structure for Move Time on first Job of day           
        if ($i==0 ){
        $time = $row["Time"];
        //$time = 'N/A';

        }else{
            $time = 'N/A';
        }

        //Get Rego from trucks table        
        $truckID = $row["TruckID"];
        $Rego_select = mysql_query("SELECT VechicleRegistration FROM trucks WHERE TruckID = '$truckID'" )
        or die("Problem reading table: " . mysql_error());
        $Rego = mysql_result($Rego_select,0);

        //Get unregisted from trucks table
        $Unregisted_select = mysql_query("SELECT Unregistered FROM trucks WHERE TruckID = '$truckID'" )
        or die("Problem reading table: " . mysql_error());
        $Unregisted = mysql_result($Unregisted_select,0); 

        $id_note = $row["BookingID"];           


                echo    '<td><a href="taskpage.php?id='.$id_note.'"><button>'. $row['TaskNo']."</button><a/></td>";
                echo    "<td>". $time . "</td>";                           // Time Frame
                echo    "<td>". $Unregisted."</td>";                      // Pickup 
                echo    "<td>". $Rego."</td>";                           // Unregisted
                echo    "<td>".$row["PickupLocation"] . "</td>";        // Rego
                echo    "<td>".$row["DropOffLocation"] . "</td></tr>"; // Delivery
                $i=$i+1;//Control Set
                }
            echo'</tr>';

对于返回预订的结果，我重复相同的输出代码.

I repeat this same output code for the results from the return booking.

是否可以将两个查询合并为一个，以便两个表的结果集可以由ASC排序并由上述while语句输出.

Is it possible to combine both queries into one so that the result set from both tables can be ordered by ASC and outputted by the above while statement.

推荐答案

这是避免使用Select *的众多原因之一.您可以简单地使用联合

This is one of the many reasons to avoid Select *. You can simply use a union

Select Time, TruckId, TaskNo, PickupLocation, DropOffLocation
From booking 
Where driver='$driver' 
    And Date= CAST('$Date_search' AS DATE) 
Union All
Select Time, TruckId, TaskNo, PickupLocation, DropOffLocation
From returnbooking
WHERE driver='$driver' 
    And Date= CAST('$Date_search' AS DATE) 
Order By TaskNo Asc

在此解决方案中，您需要枚举列并确保列的类型(在两个Select子句中枚举的顺序相同).

In this solution, you need to enumerate the columns and ensure that the type of the columns, in the order in which they are enumerated in the two Select clauses are identical.

这篇关于结合两个单独的MySQL查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！