CODEWITHSUNDEEP
phpmysqlsqlperformancequery-optimization
FooBar
FooBar

Reputation: 6128

MySQL search different tables and only fetch specfic IDs

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 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!

Upvotes: 1

Views: 138

Answers (1)

paul
paul

Reputation: 22001

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

Upvotes: 1

Related Questions