Using an array in a PHP MySQL WHERE clause

Question

Hi I am creating an array of user id's with a query. With another query I would like to select from a given table where the user_id is one that is in the array created from my very first query. How can I use an array in my WHERE clause?

Just for reference: $row_interest is the array

My Code:

//Grabs the user id's of the users that have the queried interest
$interest_search_query= "SELECT DISTINCT user_id FROM interests WHERE interest LIKE   
'%".$search_term."%'";
$interest_search_result= mysqli_query($connect, $interest_search_query);
$row_interest= mysqli_fetch_array($interest_search_result);

//Grabs the user information with each user id
$search_query= "SELECT DISTINCT user_id, fname, lname, profile_pic, school FROM users   
WHERE user_id IN $row_interest";

I tried "WHERE user_id IN $row_interest", but it doesn't seem to work. What could I be doing wrong?

Thanks.

if you want to use an array in the `where` clause, you'll need to decide if it consists of separate `where` conditions or if your array refers to a list of values in a single condition. In the first case, you'll need to create a string of conditions separated with `and` (or `or`): `where field1=value1 and field2=value2`. In the second case, you simply need to include the values of the array in a comma-separated list: `where aField in (value1, value2, value3)` — Barranka, May 29 '13 at 22:48

score 3 · Answer 1 · answered May 29 '13 at 22:47

3

$search_query= "SELECT DISTINCT user_id, fname, lname, profile_pic, school FROM users   
WHERE user_id IN (".implode(',',$row_interest).")";

answered May 29 '13 at 22:47

steven

4,868
2
28
58

score 2 · Accepted Answer · answered May 29 '13 at 22:50

2

You can actually merge both queries.

SELECT distinct user_id, fname, lname, profile_pic, school
FROM users
WHERE user_id in 
    (SELECT distinct user_id from interests
    where interest like %{search_term}%)

answered May 29 '13 at 22:50

Achrome

7,773
14
36
45

+1 Do you know if joins or subqueries perform better? I find joins more concise, but subqueries more powerful. – Peter Wooster May 29 '13 at 23:19
Joins are normally faster, but in this case, you just needed a list of UNIQUE users to compare, so a subquery should perform very close to a join. http://stackoverflow.com/questions/2577174/join-vs-subquery – Achrome May 29 '13 at 23:23
Thanks, I know that some expensive databases like oracle would convert them both to the same code internally, but I'm never sure about MySQL. – Peter Wooster May 29 '13 at 23:25
MySQL is doing some overhauls in 6.0 is what I heard last. – Achrome May 29 '13 at 23:30

Peter Wooster · Answer 3 · 2013-05-29T23:31:38.887

1

You could build an IN() clause for your SQL in PHP, but since the set is coming from another query you could use a JOIN to do this.

Edit: I can't test this without your data, but the join would be something like

$search_query= "SELECT DISTINCT u.user_id, u.fname, u.lname, u.profile_pic, u.school 
FROM user u
INNER JOIN interests i ON u.user_id = i.user_id
WHERE i.interest like '%".$search_term."%'";

edited May 29 '13 at 23:31

answered May 29 '13 at 22:49

Peter Wooster

6,009
2
27
39

1

+1, Since OP is just querying from one table to use in another query with no modifications to that data at all, a `JOIN` or even a sub-query would be *much* better. It would be nice if you would put an example. – Jonathan Kuhn May 29 '13 at 22:55
I would but I'm on my iPad so typing is difficult and testing SQL is impossible. – Peter Wooster May 29 '13 at 22:59
@Jonathan +1 example join added to answer. – Peter Wooster May 29 '13 at 23:13

score 0 · Answer 4 · answered May 29 '13 at 22:49

0

It sounds like you could just use a join, but $row_interest is an array, and it is interpolated as "Array" in the query. It seems like you want to build the entire array first

$rows = array();
while ($row = mysqli_fetch_array($interest_search_result)) {
    $rows[] = $row['user_id'];
}

Then you can create the "IN" clause you need.

"WHERE user ID IN (" . implode(",", $rows) . ")"

Your code is vulnerable to injection. You should properly parameterize the queries using prepared statements. This is more difficult to do with a variable number of arguments in mysqli as I understand it, but it is something to keep in mind.

answered May 29 '13 at 22:49

Explosion Pills

188,624
52
326
405

Unfortunately you can't use prepared statements with the IN clause and arrays. But the JOIN solution I provided and the subquery solution that @ashwin provide both give a nice solution to this. I'm not sure which is faster, but I prefer joins since they are more concise. – Peter Wooster May 29 '13 at 23:18
@PeterWooster you can use `IN` and prepared statements; build the `?` in the query based on the number of array elements and pass the array as the argument (or merge with other arguments as needed). I'm not sure if this can be done in mysqli at this point, but definitely PDO – Explosion Pills May 29 '13 at 23:20
That should work in mysqli, but you are no longer using the array, rather it's a bunch or scalars, one parameter for each ?. – Peter Wooster May 29 '13 at 23:22
@PeterWooster you used to be able to map the `bind_param` method to a functional call, but I'm not sure you can do that anymore (hence the problem). Easy to do with `PDOStatement::execute` though – Explosion Pills May 29 '13 at 23:24

Using an array in a PHP MySQL WHERE clause

4 Answers4