mysql_num_rows() does not work with SUM()?

Question

Using PHP mysql_num_rows() returns "1" when running the query below on the table below when there are no matching rows present.

Upon testing I found out that the problem happens when I use the SUM() function in the query. If I take SUM() out of the query mysql_num_rows() returns "0" like it should.

Is there something else I should use instead of mysql_num_rows() to find out if there is a matching row in the table?

Table:

name | students_money | grade
George | 5 | A
Bill | 10 | A
Dan | 7 | A

Code:

$sql = "SELECT SUM(students_money) AS sum_money FROM students_table WHERE name = 'Tom' AND name = 'Jack'";
$result = @mysql_query($sql, $con) or die(mysql_error());

$num_rows = mysql_num_rows($result);

if ($num_rows < 1) {

    echo "not everyone has paid";
    exit;

}

while($row = mysql_fetch_array($result)) {

    $sum_money = $row[sum_money];
    $total = $total + $sum_money;

}

Answer 1

SUM() is an aggregate function. It takes all the rows that are returned for a group and adds them up.

Since you do not have a GROUP BY clause, it is adding up the values of all rows, even if there are none. It is then returning the total as a single row, so there should only be 1 row.

If you clarify what you want returned, I can try to help you write a statement to return it.

Answer 2

mysql_num_rows() tells you the number of rows returned by the database query. There is always a one row return in your case because there is always one a sum. The sum may be 0 of course.

It may be a good idea to test your query in the mysql query browser. Perhaps you are looking for something like this?

SELECT name, SUM(students_money) AS sum_money 
FROM students_table
GROUP BY name;

This will group the sums on a per name basis. To skip 0 sums you can add this:

HAVING sum_money > 0;