Simone Venturas Simone Venturas - 3 months ago 9
MySQL Question

Concatenating conditionally queries in PHP, while trying to get rows from multiple tables

I have a really huge problem with my Social Network Plugin.
In my PHP script, I am concatenating SQL Where Queries, using conditional statements (if/else), because with the same function I'm getting the posts for the search bar and for the profile itselves.

I can't change, for this reason, the main statement.

I'm developing a Mention system, like Facebook's: When an user tags someone on its own profile, the post should be shown on the tagged user's profile too.

To achieve this, I'm editing the function that gets the posts, but can't figure out how to write the query.

Let's look the scenario:

I have two tables, one for posts, the other for the mentions linked to the posts' table.

Table: posts (user_id is the publisher, in_user_id is the profile where the post is published)

+---------+----------+------------+------------+------------+
| post_id | user_id | in_user_id | in_group | updated_at |
+---------+----------+------------+------------+------------+
| 1 | 12 | 12 | 0 | some_text |
| 2 | 1 | 1 | 0 | some_text2 |
| 3 | 2 | 2 | 0 | some_text3 |
| 4 | 5 | 12 | 0 | some_text4 |
| 5 | 5 | 5 | 0 | some_text4 |
| 6 | 5 | 5 | 0 | some_text4 |
+---------+----------+------------+------------+------------+


Table: posts_mentions (post_id is the post that contains the mention, user_id is the mentioned user)

+---------+----------+
| post_id | user_id |
+---------+----------+
| 2 | 12 |
| 3 | 12 |
| 6 | 1 |
+---------+----------+


What I need:
I need, when viewing the profile of user_id 12, to get the post_id of the posts published on 12's profile (posts.post_id 1 and 4), but also the posts where user_id 12 is tagged (posts.post_id 2 and 3), which are listed on posts_mentions.

What I tried:

The actual code is like this (this is an example):

$where="";

if($user_id->isFriend())
{
$where .= "WHERE (in_user_id = $user_id AND in_group = '0')";
}

...

$posts = $db->query(sprintf
("SELECT
*
FROM (SELECT posts.post_id
FROM posts ".$where.") posts
ORDER BY posts.post_id DESC");


(I used subqueries because otherwise I couldn't use conditional-concatenated queries)

I tried to add another sub-query in the $where statement, but it didn't work at all.

I'm really getting crazy. How can I resolve this problem?

Answer

You can add a subquery to get the post_ids from the post_mentions table. Something like:

$where .= "WHERE (in_user_id = $user_id AND in_group = '0')
    OR post_id IN (
        SELECT post_id FROM posts_mentions
        WHERE user_id = $user_id
    )
";