CODEWITHSUNDEEP
phpmysqlsql
Josip Gòdly Zirdum
Josip Gòdly Zirdum

Reputation: 717

Help construct a simple query Using 3 tables

Hey guys need some more help

I have 3 tables USERS, PROFILEINTERESTS and INTERESTS

profile interests has the two foreign keys which link users and interests, they are just done by ID.

I have this so far

$statement = "SELECT
  InterestID
FROM
  `ProfileInterests`
WHERE
  userID = '$profile'";

Now I want it so that it selects from Interests where what it gets from that query is the result.

So say that gives out 3 numbers

1
3
4

I want it to search the Interests table where ID is = to those...I just don't know how to physically write it in PHP...

Please help.

Upvotes: 0

Views: 59

Answers (3)

Oliver Spryn
Oliver Spryn

Reputation: 17358

You are on the right track, lets say you execute the query above using this PHP code:

$statement = mysql_query("SELECT InterestID FROM `ProfileInterests` 
WHERE userID = '$profile'");

Then you can use a PHP loop to dynamically generate an SQL statement that will pull the desired IDs from a second table. So, for example, continuing the code above:

$SQL = "";

while ($statementLoop = mysql_fetch_assoc($statement)) {
//Note the extra space on the end of the query
  $SQL .= "`id` = '{$statementLoop['InterestID']}' OR ";
}

//Trim the " OR " off the end of the query
$SQL = rtrim($SQL, " OR ");

//Now run the dynamic SQL, using the query generated above
$query = mysql_query("SELECT * FROM `table2` WHERE {$SQL}")

I haven't tested the code, but it should work. So, this code will generate SQL like this:

SELECT * FROM `table2` WHERE `id` = '1' OR `id` = '3' OR `id` = '4'

Hope that helps,
spryno724

Upvotes: 1

Paul Creasey
Paul Creasey

Reputation: 28854

Most likely you want to join the tables

select
    i.Name
from
    ProfileInterests p
inner join
    interests i
on 
    p.interestid = i.interestid
where 
    p.userid = 1

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332641

Using a JOIN:

Best option if you need values from the PROFILEINTERESTS table.

SELECT DISTINCT i.*
  FROM INTERESTS i
  JOIN PROFILEINTERESTS pi ON pi.interests_id = i.interests_id
 WHERE pi.userid = $profileid

Using EXISTS:

SELECT i.*
  FROM INTERESTS i
 WHERE EXISTS (SELECT NULL 
                 FROM PROFILEINTERESTS pi 
                WHERE pi.interests_id = i.interests_id
                  AND pi.userid = $profileid)

Using IN:

SELECT i.*
  FROM INTERESTS i
 WHERE i.interests_id IN (SELECT pi.interests_id
                            FROM PROFILEINTERESTS pi 
                           WHERE pi.userid = $profileid)

Upvotes: 2

Related Questions