data from few MySQL tables sorted by ASC
- by Andrew
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>";
}
