Using a WHERE clause with an array in MySQL

Question

I have a query for a news feed on my website that selects all of the posts from users where the logged in user has a relationship (following) with the user that posted the post. Sounds confusing, but here is my query:

SELECT DISTINCT * 
FROM    posts a 
        LEFT JOIN relations b
            ON a.user_id = b.user2
WHERE   b.user1 = $user_id AND 
    b.status IN (1) OR a.user_id = $user_id
ORDER BY a.post_id DESC LIMIT 300

Now, if the user has a filter set then the posts need to be filtered. The parameters are set in an array, but I am not sure how to set a condition in my query to test against the array I have created. How do I structure my WHERE clause? I am testing a.tag

The array containing the parameters: $array

SELECT DISTINCT * 
FROM    posts a 
        LEFT JOIN relations b
            ON a.user_id = b.user2
WHERE   b.user1 = $user_id AND

What I need help with:

        a.tag IN ARRAY $array AND

    b.status IN (1) OR a.user_id = $user_id
ORDER BY a.post_id DESC LIMIT 300

Thanks!

rid · Accepted Answer · 2013-03-19T20:22:50.927

3

You can use implode() to concatenate the values:

$escaped = array_map('mysqli_real_escape_string', $array);
$in = "IN ('" . implode("', '", $escaped) . "')";

Then, in the query:

a.tag $in AND ...

If the array contains strings, then they will have to be escaped, and this is something related to the driver you're using to run the query.

edited Mar 19 '13 at 20:22

answered Mar 19 '13 at 20:15

rid

61,078
31
152
193

what if the array is structure with only text? – user2096890 Mar 19 '13 at 20:19
1

@user2096890, then you need to escape the values. What are you using? `mysqli`? PDO? Something else? – rid Mar 19 '13 at 20:19
I am using mysqli. Why do I need to escape the values? – user2096890 Mar 19 '13 at 20:21
@user2096890 To prevent against injection. – Kermit Mar 19 '13 at 20:27
Now why do the values have to be concatenated how does this change the array? – user2096890 Mar 19 '13 at 20:28
1

@user2096890, and to make sure your query is syntactically correct. For example, if the string contained an apostrophe (even from your own input), then the resulting query would be an error. – rid Mar 19 '13 at 20:28
1

@user2096890, it doesn't change the array. You need to compose an SQL query. The syntax of SQL is: `IN (value1, value2, ...)`, and you need to write that, with values from a PHP array. In PHP, one way to do that is to use the `implode()` function. – rid Mar 19 '13 at 20:29
Oh, so I have to do it in a sort of list structure rather than the array variable just representing the list? – user2096890 Mar 19 '13 at 20:33
1

@user2096890, the array variable holds a collection of values, but you need to somehow convert these values to a query that the MySQL server can understand. You can't append the array variable to the query, because the query is just a string, so you have to get the values from the array and append them to the query string. Once the entire query string is complete, you can send it to MySQL to process it. – rid Mar 19 '13 at 20:35

Using a WHERE clause with an array in MySQL

1 Answers1