PHP: WHERE in another table

Question

Yes so im building an query from the advanced search form.

I have this:

$query = "SELECT * FROM users WHERE 1 ";
 $query .= "AND sex = '$sex' ";

for now, next im going to have AND birthday.. but then i dont know how to do it, because users birthday is stored in users_profile

So please correct me, how can i:

 $query .= "AND birthday in users_profile = '1'";     

Is this possible even, or should i reconstruct it and put birthday in users instead..

update: in users, the id column there, is binded with users_profile´s uID.

So in users_profile´s uID column, there is the users id.

Answer 1

I assume your users_profile table is linked to the users table?

SELECT u.*, up.birthday 
FROM users u
INNER JOIN users_profile up
ON u.user_id = up.user_id
WHERE sex = '$sex'

Here an Inner Join is used. The reason we can use u instead of users and up instead of users_profile is because we have set up the aliases "users u" and "users_profile up"

Answer 2

You need to look at the syntax for JOIN.

Answer 3

You need a way to join individual related rows in the two tables, something like:

SELECT u.* FROM users u, users_profile p
WHERE u.sex = 'M'
  AND p.birthday = '1'
  AND u.userid = p.userid;

Answer 4

I don't understand why you have separate tables for user and for users_profile, but you need to JOIN the two tables:

SELECT U.*
  FROM users U
  LEFT JOIN users_profile P 
         ON P.uID = U.uID
        AND P.birthday = '1'
 WHERE U.sex = '$sex'

Answer 5

Very possible, given you have the foreign key to the users_profile table. Let's say the primary key in the users table is named 'id', and the users_profile table contain a field called 'uid' which point to the users table, you'd normally create the query like this:

SELECT * FROM users u, users_profile p WHERE u.id = p.uid 
AND u.sex = '$sex' AND u.birthday = 1