Multiple Tables, 1 select. Confused.

So this is the first time I wanted to join tables. I've read a few different ways to do it and I cannot seem to get it right.

I have 2 tables

Users (which holds userid, activation)

and

Users_stats (which holds userid, gender)

I want to be able to count how many male ID's have activated. Gender=1 is male and Gender=2 is female.

Any suggestions/pointers/tips? Thanks for the help.

 

SELECT * FROM Users, Users_stats WHERE Users.userid=Users_stats.userid AND gender=1 AND activation="YES"

Maybe try something like that.

 

Use the COUNT and GROUP BY functions

SELECT Users.userid, Users.activation, COUNT(Users_stats.userid) AS NumberOfUsers
FROM Users LEFT JOIN Users_stats on Users.userid = Users_stats.userid
WHERE Users.activation = 'Yes' AND Users_stats.gender = 1
GROUP BY Users.userid, Users.activation

or something like that

 

and

both did what I had hoped while running the sql in mysql, however in my php it was somewhat different, both returned a 1 (probably a fault of my own).

I changed some things but this is to check for a female...

or

is there a different way for me to display them? as both of those result in a 1. As you can tell, im definitely overthinking things :| Activation is blank when the person is activated.

 

With mysql_result you are fetching only one field from one row. Use "while loop" and mysql_fetch_array or similar function to retrieve all result set.