PHP MySql query based off the results of another

Question

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.

Nesting queries is always wrong. I would definitely recommand you to read a book on the subject, since your RDBMS is the most important factor in web development. — Vincent Savard
– Vincent Savard, Commented Aug 10, 2011 at 4:18

cdhowie · Accepted Answer · 2011-08-10 04:15:14Z

1

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?

answered Aug 10, 2011 at 4:15

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joel Over a year ago

Thank you for the amazing fast response and excellent reply, That worked really good, i'm trying to get my head around that inner join now but it seems to have worked for my needs. The emails really more like a username, i'm not going to use it or verify it I just think people might be more likely to remember an e-mail address rather than a username but yeah I will look into that.

Collectives™ on Stack Overflow

PHP MySql query based off the results of another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related