CODEWITHSUNDEEP
phpmysql
Joel
Joel

Reputation: 577

PHP MySql query based off the results of another

I have 1 table in my mysql database called clubusers which lists users emails and has a column called "Verified" which is 1 or 0. Then I have another table with the clubs they have created which has a "created" column which is the email of the person who has created that club.

What I want to do is select the users from my first table who have verified as "1" and then select all their clubs in the second table so I can echo them.

I tried nesting 2 while(row = mysql_fetch_array() statements but that didnt work.

I think I might need to use a foreign key but I don't really know how they work so i'm not sure and I think there might be an easier way.

Upvotes: 0

Views: 224

Answers (1)

cdhowie
cdhowie

Reputation: 168988

Try an inner join:

SELECT user.*, club.* FROM user

INNER JOIN club
ON user.email = club.created

WHERE user.verified

Note that you should really consider using an identity autoincrement column as the foreign key instead of an email address. What happens when a user changes their email address? Do they lose the association to all of the clubs they've created?

Upvotes: 1

Related Questions