CODEWITHSUNDEEP
phpmysql
Gntvls
Gntvls

Reputation: 230

Mysql select from two different table with same structure

can you offer to me something, how to push two different tables but with the same structure, i means all info is the same, just table name is different, mailings and mailings_archive. for one table it is ok 

$query = sprintf("SELECT * FROM %smailings  WHERE hidden=0 AND draft=1 %sORDER BY category_id, mailing_segment", $this->dbprefix, $language);

$this->mailings[] = array('id' => $row->id,
                                      'mailing_naam' => $row->mailing_naam,
                                      'mailing_subject' => $row->mailing_subject,
                                      'mailing_from_name' => $row->mailing_from_name,
                                      'mailing_bounceaddress' => $row->mailing_bounceaddress,
                                      'mailing_body' => $row->mailing_body,
                                      'mailing_status' => $row->mailing_status,
                                      'mailing_segment' => $row->mailing_segment,
                                      'mailing_total_subscribers' => $row->mailing_total_subscribers,
                                      'mailing_total_sent' => $row->mailing_total_sent,
                                      'category_id' => $row->category_id,
                                      'utm_campaign' => $row->utm_campaign,
                                      'language' => $row->language);
        }

Now it looks like this, how to put in this array, another table data ? Dont forget, every field is the same, just table name different.

Upvotes: 0

Views: 882

Answers (2)

Tigra
Tigra

Reputation: 2631

(SELECT * FROM %smailings  WHERE hidden=0 AND draft=1 %sORDER BY category_id, mailing_segment)
UNION
(SELECT * FROM another_table WHERE hidden=0 AND draft=1 %sORDER BY category_id, mailing_segment)

(select * from table1)
union
(select * from table2)

But this will result in one unordered result set, even if you put "order by" inside of each request, but this one probably the fastest.

If you need to have everything ordered between tables (1....8), then this is a syntax:

select * from table1
union
select * from table2 order by id

If you want to have two separate orders (like 4...8 from one table, 1..4 from another):

select * from 
(select * from test1 order by id asc) as t1
union all
(select * from test2 order by id asc);

Upvotes: 4

Ludovit Scholtz
Ludovit Scholtz

Reputation: 84

use the union method..

try query: select 1 union select 2

Upvotes: 1

Related Questions