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

Question

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)

You can use union all, but at least you've learned something about bad database design. — Gordon Linoff
– Gordon Linoff, Commented Jan 31, 2017 at 13:18
use JOINS. This schema is very badly designed btw. sitepoint.com/understanding-sql-joins-mysql-database — Muhammad Saqlain
– Muhammad Saqlain, Commented Jan 31, 2017 at 13:19
Does MySQL have Views? or an equivalent? If so, create a view that includes all 14 tables, (use Union, or full joins). and drive your query against the View. But why is this data spread across 14 tables instead of being in just one with a column named city? Not that that is intrinsically wrong, but there are only a few good reasons to do that. — Charles Bretana
– Charles Bretana, Commented Jan 31, 2017 at 13:21
How big it is does not affect this. If you do not have the authority, and are unable or unwilling to approach the individual who does, well, then, my sympathies. — Charles Bretana
– Charles Bretana, Commented Jan 31, 2017 at 14:20

davejal · Accepted Answer · 2017-01-31 13:37:29Z

1

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

edited Jan 31, 2017 at 13:37

answered Jan 31, 2017 at 13:26

davejal

6,19110 gold badges45 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Prateek Anand Over a year ago

but it gives 2 or more rows of same author, like this: author | num_of_post bob 5 bob 2 peter 3

davejal Over a year ago

if you could add some dummy data I could write the exact query, but according to your comment I now understand that 1 author can have records in multiple tables

Prateek Anand Over a year ago

Yes all authors can post in all the tables, therefore it is giving result of same author multiple times. I want to know the total num of post by all authors in all tables. Suppose author1 has posted 1 post in table1, 5 post in table2 and 0 post in all other tables then total num of post by author1 is 6

davejal Over a year ago

could you either try the updated version or could you add some dummy data so I can write the exact query?

Prateek Anand Over a year ago

I think it is working, but let me check. Will reply you later

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related