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
– Barranka, Commented May 29, 2013 at 22:48

steven · Accepted Answer · 2013-05-29 22:47:59Z

3

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

answered May 29, 2013 at 22:47

steven

4,8952 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Achrome · Accepted Answer · 2013-05-29 22:50:44Z

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, 2013 at 22:50

Achrome

7,82114 gold badges38 silver badges45 bronze badges

4 Comments

Peter Wooster Over a year ago

+1 Do you know if joins or subqueries perform better? I find joins more concise, but subqueries more powerful.

Achrome Over a year ago

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. stackoverflow.com/questions/2577174/join-vs-subquery

Peter Wooster Over a year ago

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.

Achrome Over a year ago

MySQL is doing some overhauls in 6.0 is what I heard last.

Peter Wooster · Accepted Answer · 2013-05-29 23:31:38Z

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, 2013 at 23:31

answered May 29, 2013 at 22:49

Peter Wooster

6,0882 gold badges29 silver badges39 bronze badges

3 Comments

Jonathan Kuhn Over a year ago

+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.

Peter Wooster Over a year ago

I would but I'm on my iPad so typing is difficult and testing SQL is impossible.

Peter Wooster Over a year ago

@Jonathan +1 example join added to answer.

Explosion Pills · Accepted Answer · 2013-05-29 22:49:04Z

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, 2013 at 22:49

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

4 Comments

Peter Wooster Over a year ago

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.

Explosion Pills Over a year ago

@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

Peter Wooster Over a year ago

That should work in mysqli, but you are no longer using the array, rather it's a bunch or scalars, one parameter for each ?.

Explosion Pills Over a year ago

@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

Collectives™ on Stack Overflow

Using an array in a PHP MySQL WHERE clause

4 Answers 4

Comments

4 Comments

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related