Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
 
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 at 22:48
add comment

4 Answers

up vote 2 down vote accepted

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}%)
share|improve this answer
 
+1 Do you know if joins or subqueries perform better? I find joins more concise, but subqueries more powerful. –  Peter Wooster May 29 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. stackoverflow.com/questions/2577174/join-vs-subquery –  Ashwin Mukhija May 29 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 at 23:25
 
MySQL is doing some overhauls in 6.0 is what I heard last. –  Ashwin Mukhija May 29 at 23:30
add comment
$search_query= "SELECT DISTINCT user_id, fname, lname, profile_pic, school FROM users   
WHERE user_id IN (".implode(',',$row_interest).")";
share|improve this answer
add comment

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."%'";
share|improve this answer
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 at 22:55
 
I would but I'm on my iPad so typing is difficult and testing SQL is impossible. –  Peter Wooster May 29 at 22:59
 
@Jonathan +1 example join added to answer. –  Peter Wooster May 29 at 23:13
add comment

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.

share|improve this answer
 
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 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 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 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 at 23:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.