I have several tables with same structure. All I want is get all results from all tables from a database.
For example:
Table1:
id | Name
-----------
1 | John
2 | Alex
3 | Patrick
4 | Donald
5 | Jane
id | Name
-----------
1 | Ben
2 | Dale
3 | Kane
4 | Tom
5 | George
Name
-------
John
Alex
Patrick
Donald
Jane
Ben
Dale
Kane
Tom
George
$dbc1 = $db->query("SELECT group_concat(table_name) AS tables FROM information_schema.tables WHERE table_schema='dbname'");
$dbc1_ftchd = $dbc1->fetch(PDO::FETCH_ASSOC);
foreach(explode(',',$dbc1_ftchd['tables']) as $next_tb_name){
echo $next_tb_name.'<br>';
}
This should work:
SELECT `Name` FROM `Table1`
UNION
SELECT `Name` FROM `Table2`
UNION
SELECT `Name` FROM `Table3`
EDIT: Sorry, I didn't saw the last paragraph of your question. You can create a view or temporary table if you don't like UNIONs that much.
Create View: https://dev.mysql.com/doc/refman/5.7/en/create-view.html
Create Temporary Table: CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table AS (SELECT * FROM table1 UNION ...)