There's no magic bullet query for this.
You have not set up your data to match your problem.
Ideally, you'd create a table "activity"
That would have columns like:
record_type ('article' or 'review')
record id
visits
And update and select off that.
But this situation is pretty common: You set up your data based on one definition of the problem and later realize you have a different problem.
Instead you're forced to scramble somewhat, just like the rest of us imperfect programmers:
Some ideas:
go back and reprogram a table like the one above, and change your visit increment logic to address that table.
Create / recreate that table on the fly when you want to do a query. I do this sometimes, using the userid to prevent conflicts:
record_type ('article' or 'review')
record id
visits
userid (//Who set this query in motion?"
DELETE from activity WHERE userid='myuserid'
INSERT INTO activity (record_type, record_id, visits, userid) SELECT 'article', id, visits, 'myuserid' FROM article ORDER BY visits DESC LIMIT 10
INSERT INTO activity (record_type, record_id, visits, userid) SELECT 'review', id, visits, 'myuserid' FROM review ORDER BY visits DESC LIMIT 10
SELECT record_type, record_id, visits FROM activity ORDER BY visits DESC LIMIT 10
DELETE from activity WHERE userid='myuserid'
- Use PHP:
SELECT 'article', id, visits FROM article ORDER BY visits DESC LIMIT 10
SELECT 'review ', id, visits FROM review ORDER BY visits DESC LIMIT 10
Push all the select values into PHP arrays and use PHP to find the top 10 items.
