MySQL搜索不同的表，只获取特定的ID

Question

I am trying to create an algorithm to sort out the most relevant data for a specific user_id.

I imagine my end result to be an array with KEY as the found USER_ID and the VALUE to be the number of times the specific USER_ID has been found in the different rows.

So, I need to look through different rows in different tables and look for where CURRENT_USER_ID (lets say id: 30) exists, and then find the RECIEVER_ID, that is the user which was communicated to. This is pretty hard to explain, but lets take an example:

• I have a table called: edu_posts, which contains wallposts and comments to theese. The are different values in this table, but the ones we should focus on is: post_author and post_reciever. We then have to look for all the rows where post_author equals 30 (the test example; just needs to be the current users id) and then print out the post_reciever IDS. This would be easy enough with a single query, but lets say we have to find data in 5 or 10 different tables, that 10 different queries, which is a lot.
• We also have a table called edu_folowers. There we have to look for where follow_author equals 30 (the test example; just needs to be the current users id), and then print out the follow_user ID. Again, to find out who the current user have interest in.

I image the final mysql_fetch to look something like this:

user_id => 25
times_found => 5
user_id => 11
times_found => 3
user_id => 95
times_found => 1

etc.

Can this be done using a single query, maybe using JOIN? And even maybe count the results IN the query, so I don't have to do this manually in the PHP code.

Or should I create a mysql_query for every table I wish to get data from, and then manage the data afterwards using PHP? This sounds like the easiest way to me, but also the most inefficient relating to script optimization.

I have tried out with the following test-query:

SELECT
    u.user_id AS user_id,
    f.follow_user AS user_id_follow,
    p.post_reciever AS user_id_posts
FROM
    `edu_posts` u
LEFT JOIN `edu_followers` f ON f.follow_author = '30'
LEFT JOIN `edu_posts` p ON p.post_author = '30' && p.post_reciever != '30'
WHERE 
    u.user_id = '30'
GROUP BY
    f.follow_id, p.post_id

But the problem is that it outputs unexcepted results, and also I will have different values to look for, fx: user_id (not really needed, as we already know that it is 30), user_id_follow, user_id_posts, and so on.

I hope you understand my question, and please let me know, if you need additional information.

Many thanks in advance!

Answer 1

You could create a union view of all of the tables that you want to search, depending on your exact requirements you might only need to query that once.

e.g.

create view allPostTypesUnion as
select user_id, post_receiver
from   edu_posts
union
select user_id, post_receiver
from   different_edu_posts
union
select user_id, post_receiver
from   another_different_edu_posts

then:

select post_receiver, count(*)
from   allPostTypes
group by post_receiver