Geek asks and answers

Combining the results of two separate MySQL queries

I am trying to make two separate database queries and return the results to the form. Each result is recorded in a table.

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

FIRST REQUEST:

 //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 QUESTION:

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

Then the results are displayed on the page through the 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 the same withdrawal code for booking results.

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

+3
source share
3 answers

This is one of many reasons to avoid Select *. You can just use 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, .

+8

, , , , .

, .

.

. , , , , .

+2

, .

, , , client_id adres_id company_id adres_id, adres .

, , ...

In the end, you should really stick with SQL to get ALL the data you need at a time, and use PHP (or other server side scripts) to format this data for the user. This is not a big 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.

Using only 1 long query is often better than a few smaller ones.

0
source

More articles:


All Articles