CODEWITHSUNDEEP
mysqlsql
Prateek Anand
Prateek Anand

Reputation: 39

How to fetch query from multiple tables in PHP and MySQL?

In the database I am working on, there are 14 news tables (table1, table2 .... table14) of different cities with exact same structure. Some of the columns are: id, author, date, news_title, news_body

There are different authors who post in relevant city table. Now I want to know how many news has been posted by each author today in all 14 tables.

Here is the format of output:

author | num_of_post
bob          5
peter        12

Remember, there are 14 tables (this is the problem)

Upvotes: 1

Views: 65

Answers (1)

davejal
davejal

Reputation: 6133

As @Grodon already stated you can use union like this:

select author, count(id) as num_of_post from table1 group by author
union
select author, count(id) as num_of_post from table2 group by author
union
select author, count(id) as num_of_post from table3 group by author

And so on

Update:

Because you have records from 1 author in multiple tables, you can group the union result again.

select author, sum(num_of_post) from (
 select author, count(id) as num_of_post from table1 group by author
 union
 select author, count(id) as num_of_post from table2 group by author
 union
 select author, count(id) as num_of_post from table3 group by author) a group by author

Upvotes: 1

Related Questions