MySQL query malfunctioning?

I have the below MySQL query, which should return the t1.username, t1.website, in_count (WHERE t2.type = 'in'), out_count (WHERE t2.type = 'out') and WHERE the t1.website field is NOT EMPTY or NULL.

However its seemed to have malfunctioned (or I'm doing something wrong?), as it returns 1 result (via mysql_num_rows()) and when i print_r() (on the mysql_fetch_assoc()), the keys of the columns are their however all the values are empty/blank (see below).

The print_r() dump:

Array
(
    [username] => 
    [website] => 
    [in_count] => 
    [out_count] => 
)

The MySQL query:

SELECT t1.username,
       t1.website,
       SUM(IF(t2.type = 'in', 1, 0))  AS in_count,
       SUM(IF(t2.type = 'out', 1, 0)) AS out_count
FROM   users AS t1
       JOIN referrals AS t2
         ON t1.username = t2.author
WHERE  NOT Isnull(t1.website)
LIMIT  0, 10 

Hope all is clear, l开发者_JAVA技巧et me know if you need more information, appreciate all help. :B

Sounds like you might need a GROUP BY clause at the end? Perhaps

GROUP BY t1.username, t1.website

That way you'll get all the in and out counts for a particular user on a particular web site.

I was under the impression that with aggregate functions like SUM the query requires a GROUP BY clause... I'm surprised the server accepted your SQL.

The SUM() function is an aggregate, and there is no GROUP statement in your query, so it has nothing to do. You can always check the error condition of your query in PHP by outputting the contents of mysql_error(), which in this case will likely say that you are missing a GROUP statement.

Based on your query it looks like your intent is to have a one-to-many relationship between users and referrals, so you should be grouping by something like t1.username (or a user ID if your table has it).