data from few MySQL tables sorted by ASC

Posted by Andrew on Stack Overflow See other posts from Stack Overflow or by Andrew
Published on 2011-11-11T12:01:27Z Indexed on 2011/11/11 17:52 UTC
Read the original article Hit count: 202

Filed under:
|

In the dbase I 've few tables named as aaa_9xxx, aaa_9yyy, aaa_9zzz. I want to find all data with a specified DATE and show it with the TIME ASC.

First, I must find a tables in the dbase:

$STH_1a = $DBH->query("SELECT table_name 
                  FROM information_schema.tables
                 WHERE table_name
                  LIKE 'aaa\_9%'
               ");


foreach($STH_1a as $row)
{  
$table_name_s1[] = $row['table_name'];  
}

Second, I must find a data wit a concrete date and show it with TIME ASC:

foreach($table_name_s1 as $table_name_1)
{
$STH_1a2 = $DBH->query("SELECT * 
                      FROM `$table_name_1`
                     WHERE
                           date = '2011-11-11' 
                  ORDER BY time ASC
                     ");

while ($row = $STH_1a2->fetch(PDO::FETCH_ASSOC)) {

echo " ".$table_name_1."-".$row['time']."-".$row['ei_name']." <br>";
}
}

.. but it shows the data sorted by tables name, then by TIME ASC. I must to have all this data (from all tables) sorted by TIME ASC.


Thank You dev-null-dweller, Andrew Stubbs and Jaison Erick for your help. I test the Erick solution :

foreach($STH_1a as $row) { 

$stmts[] = sprintf('SELECT * 
                  FROM %s 
                 WHERE date="%s"', $row['table_name'], '2011-11-11'); 

                     }  

$stmt = implode("\nUNION\n", $stmts);  
$stmt .= "\nORDER BY time ASC";  

$STH_1a2 = $DBH->query($stmt);

while ($row_1a2 = $STH_1a2->fetch(PDO::FETCH_ASSOC)) {

echo " ".$row['table_name']."-".$row_1a2['time']."-".$row_1a2['ei_name']." <br>";
}

it's working but I've problem with 'table_name' - it's always the LAST table name.

//----------------------------------------------------------------------

end the ending solution with all fixes, thanks all for your help, :))

foreach($STH_1a as $row) { 

$stmts[] = sprintf("SELECT *, '%s' AS table_name 
                  FROM %s 
                 WHERE date='%s'", $row['table_name'], $row['table_name'], '2011-11-    11'); 

}


$stmt = implode("\nUNION\n", $stmts);  
$stmt .= "\nORDER BY time ASC";  

$STH_1a2 = $DBH->query($stmt);

while ($row_1a2 = $STH_1a2->fetch(PDO::FETCH_ASSOC)) {

echo " ".$row_1a2['table_name']."-".$row_1a2['time']."-".$row_1a2['ei_name']." <br>";
}

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql