Combining the results of two separate MySQL queries

Question

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.

score 9 · Accepted Answer · answered May 05 '11 at 05:14

9

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.

answered May 05 '11 at 05:14

Thomas

63,911
12
95
141

Cheers. I am not sure how to out put the results into variables. I tried $time = mysql_result($dbTaskRecords,0); but that does not work. – Samuel Meddows May 05 '11 at 10:33
@Samuel Meddows - You should be able access the columns just as you did when there was a single select statement. – Thomas May 05 '11 at 15:11
Nothing wrong with using SELECT * if both sub-queries are on the same table... is there? – Rooster242 Mar 20 '14 at 20:53

score 2 · Answer 2 · answered May 05 '11 at 05:34

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.

score 0 · Answer 3 · answered Jul 18 '12 at 14:15

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.

Not always using one long query is better. It depends on the sceneario, you can never be sure wich option is the best until you test. Check this [SO anser](http://stackoverflow.com/questions/3910317/is-it-better-to-return-one-big-query-or-a-few-smaller-ones) to know more — Yaroslav, Oct 11 '12 at 07:03

Combining the results of two separate MySQL queries

3 Answers3

Linked