CODEWITHSUNDEEP
phpmysql
user2320500
user2320500

Reputation: 169

Using an array in a PHP MySQL WHERE clause

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.

Upvotes: 1

Views: 6078

Answers (4)

Peter Wooster
Peter Wooster

Reputation: 6089

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."%'";

Upvotes: 1

Achrome
Achrome

Reputation: 7821

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}%)

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191749

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.

Upvotes: 0

steven
steven

Reputation: 4875

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

Upvotes: 3

Related Questions