CODEWITHSUNDEEP
phpmysqlsql
user1320260
user1320260

Reputation:

Two queries, two different tables, only one loop

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

Upvotes: 0

Views: 527

Answers (3)

paquino
paquino

Reputation: 464

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

Upvotes: 0

bjnr
bjnr

Reputation: 3437

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

Upvotes: 2

Bryan Elliott
Bryan Elliott

Reputation: 4095

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>";
}

Upvotes: 1

Related Questions