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.

FIRST QUERY:

 //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);

SECOND QUERY:

//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);

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.

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.

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

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.

From what I can tell you have three options to accomplish what you are after.

You could use a join, assuming the two tables have a foreign key.

You could use a union to append the two result sets.

You could output both queries into an array and iterate through that array for your output. This is probably less efficient then a union, but gives you greater separation between the two result sets.

Using multiple smaller tables keep your data organized and ensures the column names are the same.

If we have for instance a company with a bunch of customers, and where customers could be private clients or companies, using a seperate table for those adresses and 2 reference tables with either client_id and adres_id or company_id and adres_id, the adres will always have the same column names.

Not only that, but if certain information is limited you don't run the risk of storing empty space...

In the end you should really stick to SQL for getting ALL the data you need in one go and use PHP (or other server side scripts) for formatting this data to the user. It isn't much of a problem for an internal or private website, but when you have more users you will want to limit the amount and size of your data transfers.

Using only 1 long query is often better than several smaller.