mySQL how to merge statements

Question

I have two statements

a.

while (($row_1 = $STH_1 -> fetch_assoc()) !== null){
    $table_name = $row_1['table_name']; 
    $stmts_1[] = sprintf("                       
        SELECT *                         
        FROM $table_name                         
        WHERE date_time = $today_date ");
}

$stmt_1 = implode("\nUNION\n", $stmts_1);   
$stmt_1 .= "\nORDER BY date_time ASC";    
$STH_5_2 = $DBH_R->query($stmt_1);  
while (($row_5_2 = $STH_5_2 -> fetch_assoc()) !== null) {   }

b.

$STH_e = $DBH_R->query("                         
    SELECT *                                                  
    FROM $table_name_2                                                  
    WHERE date_time = $today_date  ");   
while (($row_e = $STH_e -> fetch_assoc()) !== null) {   }

the source colums for query 1 and query 2 are differents
one column are similar -> date_time, but TIME(date_time) are different, DATE(date_time) will be the same
all I must merge into one list (ORDER BY TIME(date_time) ASC) to make source for ticker script

Q: how to merge this two statements? or maybe this two queries?

//------------------------------------------------------------- update - create table statement

table for query 1 - this type near 30 tables

CREATE TABLE IF NOT EXISTS v_c_ei_9001 (

id int(11) unsigned NOT NULL AUTO_INCREMENT,

ei_code int(10) NOT NULL DEFAULT '0',

date_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

index_year varchar(10) NOT NULL DEFAULT 'ndnc',

index_period varchar(10) NOT NULL DEFAULT 'nd',

index_period_2 varchar(10) NOT NULL DEFAULT 'nd',

index_unit varchar(10) NOT NULL DEFAULT 'nd',

index_comment text NOT NULL,

PRIMARY KEY (id), KEY date_time (date_time) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=608 ;
table for query 2 - this type is only one

CREATE TABLE IF NOT EXISTS v_c_e (

id int(11) unsigned NOT NULL AUTO_INCREMENT,

country_code int(10) NOT NULL DEFAULT '0',

country_name varchar(50) NOT NULL DEFAULT 'ndnc',

date_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

event_title varchar(100) NOT NULL DEFAULT 'ndnc',

event_released int(10) NOT NULL DEFAULT '0',

event_comment varchar(500) NOT NULL DEFAULT 'ndnc',

PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=168 ;

brgs

//--------------------------------------------------- UPDATE 2

pre-query code // from all tables in the dbase we query only tables with names like v_c_ei_9xxx

$STH_1 = $DBH_R->query("SELECT table_name                        
                         FROM information_schema.tables                        
                        WHERE table_name                        
                         LIKE 'v_c_ei\_9%'
                         ");
$stmts_1 = array();

while (($row_1 = $STH_1 -> ..... // as above

$table_name = $row_1['table_name']; indicates a BIG, BIG trouble. — Your Common Sense
the dbase table names are used in prequel query - the result is a collection of table_names's as source to first query (inside first while (){}) - and can you explain your comment? thnks — Andrew
all is working ok but as 2 different queries, but how make it work together? — Andrew

Lewis Wheeler Lewis Wheeler · Accepted Answer · 2012-02-01T11:42:19

  $stmts_1[] = sprintf("
SELECT * FROM (
   SELECT *                         
   FROM $table_name tn                     
   WHERE date_time = $today_date

UNION ALL

SELECT *                                                  
   FROM $table_name_2 tn2                                                  
   WHERE date_time = $today_date
)tmp;"                       

}

That should do what you want - I havn't tested it as depends on the variables being passed and you haven't provided SQL or code. This is a sub-select query. The UNION ALL part concatenates the results of both queries into one result. However both tables MUST have the same amount of columns in the same order otherwise this will throw a incompatible type or wrong amount of column error.

Its much better instead to define the columns and the order e.g.

  $stmts_1[] = sprintf("
SELECT * FROM (
SELECT col1, col2, col3, col4...... coln                          
FROM $table_name tn                     
WHERE date_time = $today_date

UNION ALL

SELECT col1, col2, col3, col4...... coln                                            
   FROM $table_name_2 tn2                                                 
   WHERE date_time = $today_date
)tmp;"

}

EDIT:

Applying the same logic I said above you can handle extra attributes that are in one table but not another like this:

SELECT * FROM(

SELECT
id as id,
ei_code as ei_code,
date_time as dt,
'' as country_name,
index_year as iy,
index_period as ip1,
index_period_2 as ip2,
index_unit as iu,
index_comment as ic,
'' as et,
'' as er,
'' as ec
  FROM `v_c_ei_9001` vce1

UNION ALL

SELECT
id as id,
country_code as country_code,
date_time as date_time,
country_name as country_name,
'' as iy,
'' as ip1,
'' as ip2,
'' as iu,
'' as ic,
event_title as et,
event_released as er,
event_comment as ec
FROM  `v_c_e` as vce2

)tmp

I've made a couple of assumptions based upon the create table you posted seeing as the input masks were very similar. If these are wrong in your context just create blank fields as i've done for ip1, ip2 etc. I've tested the above with your CREATE tables in MySQL using some sample data and its fine.

EDIT 2:

$start2 = "SELECT
id as id,
country_code as country_code,
date_time as date_time,
country_name as country_name,
'' as iy,
'' as ip1,
'' as ip2,
'' as iu,
'' as ic,
event_title as et,
event_released as er,
event_comment as ec
FROM "; 

$count = 0;

$start = "SELECT
id as id,
ei_code as ei_code,
date_time as date_time,
'' as country_name,
index_year as iy,
index_period as ip1,
index_period_2 as ip2,
index_unit as iu,
index_comment as ic,
'' as et,
'' as er,
'' as ec
  FROM ";
//Loop through all table names using the variable $start concatenated with $table_name to create the SQL statement you require
while (($row_1 = $STH_1 -> fetch_assoc()) !== null){
    $table_name = $row_1['table_name']; 
    $stmts_1[] = sprintf("                       
        $start $table_name                         
         WHERE date_time = $today_date ");
    $count = $count + 1;
}

//This statements adds an extra String to the above stmt_1 - the $count variable controls where the extra string is placed, should be at the end of the array. Shouldn't matter though.
//NOTE this is your second query the part 'b' of your above question: variable $table_name_2
    $stmt_1[count] sprintf("                       
            $start2 $table_name_2                         
             WHERE date_time = $today_date ");

//Implode all arrays into one long array adding 'Carriage Return (\n) UNION Carriage Return (\n)' between each statement.

    $stmt_1 = implode("\nUNION\n", $stmts_1);  
    //Add another line at the end for the order by
    $stmt_1 .= "\nORDER BY date_time ASC";    
    $STH_5_2 = $DBH_R->query($stmt_1);  
    while (($row_5_2 = $STH_5_2 -> fetch_assoc()) !== null) {   }

mySQL how to merge statements

1 Answers