0

I would like to run two select statements for two different tables but list them in one loop. Currently I run them independently but this is not ideal as i would like the records listed in date order as a whole. The column names and column numbers are different.

Simplified Current setup

     $SQL = "SELECT * FROM table1 WHERE colA IS NOT NULL ORDER BY dateA";
     $DataOne = mysql_query($SQL);

     $SQL = "SELECT * FROM table2 WHERE colZ IS NOT NULL ORDER BY dateZ";
     $DataTwo = mysql_query($SQL);

     while ($row = mysql_fetch_assoc($DataOne)) {
         echo "<td>$row[colA]</td>"; 
     } 

        while ($row = mysql_fetch_assoc($DataTwo)) {
         echo "<td>$row[colZ]</td>"; 
     }

Desired setup (logically)

while ($row = mysql_fetch_assoc($DataOne, $DataTwo)) {
    // all returned rows from both tables in date order
    echo "<td>$row[EitherCol]</td>";
}

INFO: I understand i should be using mysqli or pdo but it isn't an option at the moment

Improve this question
4
  • 1
    What about UNION operator? Commented Dec 11, 2013 at 22:14
  • @SQLhint you should make that an answer. Commented Dec 11, 2013 at 22:17
  • 1
    @SQLhint thanks, but I've never used union before, how would i display the results in the sense that the column names are different? Commented Dec 11, 2013 at 22:19
  • @SQLhint UNION will not work as the column names/numbers do not match. Commented Dec 11, 2013 at 22:26

3 Answers 3

Reset to default
2

Use UNION operator in case the number of columns and their type is the same. Column names will be taken from first query in case they are different.

Not the last, avoid the usage of "SELECT *" and enumerate columns instead.

In your case I would do:

SELECT colA as EitherCol FROM table1 WHERE colA IS NOT NULL ORDER BY dateA
UNION
SELECT colZ as EitherCol FROM table2 WHERE colZ IS NOT NULL ORDER BY dateZ
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The column names are different as in the example unfortunately. Also i've reworded my question to state that the column names and number of columns are different. This seems difficult to find a solution to.
@TheApptracker: make them to be the same. See the updated answer.
I see what you mean now, that makes sense. I really need to brush up on my sql queries. Can this be applied to multiple columns? eg SELECT colA as EitherCol, colB as EitherColB, FROM table1 WHERE colA IS NOT NULL ORDER BY dateA
1

use UNION ... like so.

$SQL = "(SELECT * FROM table1 WHERE colA IS NOT NULL) UNION (SELECT * FROM table2 WHERE colZ IS NOT NULL) ORDER BY date";

$Data = mysql_query($SQL);

while ($row = mysql_fetch_assoc($Data)) {
// all returned rows from both tables in date order
echo "<td>$row[EitherCol]</td>";
}
Improve this answer

3 Comments

The requirement is that table1 and table2 have the exact number and type of fields in the same order.
Thanks, but how would i order the rows by date as the date named columns are different. Also $row[EitherCol] was an example, how would I echo out the column name of each one depending on what loop is being ran?
Oh yes, oops, I see now the column names are not the same. @SQLhint answer would seem to be correct in this case..
0

Only select the relevant columns and name them the same

i.e. 

SELECT colA1 as col1 , colA2 as col2 , colA3 as col3 , dateA as date FROM table1
UNION
SELECT colB1 as col1 , colB2 as col2 , '' as col3 , dateB as date FROM table2
ORDER BY date

If you look at col3 that's an example of what you can do if you absolutely need something from table1 and it doesn't exist in table 2

Lastly, aliasing the date lets you order the final table by date

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.