CODEWITHSUNDEEP
phpmysql
user2320500
user2320500

Reputation: 169

Combining MySQL queries

I am trying to select rows of user data from one of my tables. I only want rows that have a primary_key (user_id) in an array. How can I do this?

Query That creates the array:

$suggestions_query= "
SELECT * 
  FROM relations 
 WHERE user1= '$user_id' 
   AND status = 1;
";
$suggestions_array= mysqli_query($connect, $suggestions_query);

Query to grab user data:

$search_query= "
SELECT * 
  FROM users 
 WHERE fname LIKE '%".$fname."%' 
   AND lname LIKE '%".$lname."%'" 
   AND IN ARRAY <!-- Code in Question

$search_result= mysqli_query($connect, $search_query)

Upvotes: 0

Views: 95

Answers (4)

jon.kerlinger
jon.kerlinger

Reputation: 15

If I understand the question correctly, it sounds like you want to use an inner join. You can read about them here.. http://www.w3schools.com/sql/sql_join.asp

The syntax would be something like..

"SELECT * FROM relations "
. "INNER JOIN users ON relations.user_id=users.user_id "
. "WHERE fname LIKE '%".$fname."%' AND lname LIKE '%".$lname."%' "
. "AND user1= '".$user_id."' AND status= 1"

The two tables must have parallel values for user_id, I.e., you're treating user_id like a foreign key.

Hope this helps

Upvotes: 1

DevlshOne
DevlshOne

Reputation: 8457

SELECT * FROM users WHERE fname LIKE '%".$fname."%' AND lname LIKE 
'%".$lname."%'" AND user_id IN (SELECT user_id FROM relations WHERE user1= '".$user_id."' AND STATUS= 1)

Upvotes: 0

Sean
Sean

Reputation: 12433

try doing it all in 1 query

SELECT * FROM users 
WHERE fname LIKE '%".$fname."%' 
AND lname LIKE '%".$lname."%' 
AND user_id IN (
               SELECT user_id FROM relations
               WHERE user1= '".$user_id."' 
               AND status= 1)

Upvotes: 1

Ezra Nugroho
Ezra Nugroho

Reputation: 224

You can use subquery.

You can combine both queries into something like:

$search_query= "SELECT * FROM users WHERE fname LIKE '%".$fname."%' AND lname LIKE 
'%".$lname."%'" AND id in (SELECT id FROM relations where user1= '".$user_id."' AND status= 1)"

Upvotes: 0

Related Questions