COUNT error

[aquatradehub]

Hi, I have 2 functions which are meant to return the number of items a user has for sale. 

These are 

function count_items_listed($user_id) {
	return mysql_result(mysql_query("SELECT COUNT(`items_listed`) FROM `user_stats` WHERE `user_id` = $user_id"), 0);
}

function count_live_items_listed($user_id) {
	return mysql_result(mysql_query("SELECT COUNT(`live_items_listed`) FROM `user_stats` WHERE `user_id` = $user_id"), 0);
}

To view this information, I am using

$count_items_listed   = count_items_listed($user_id);

AND

$count_live_items_listed   = count_live_items_listed($user_id);

<p>Number of live items currently listed: <?php echo $count_live_items_listed; ?></p>
<p>Number of items currently listed: <?php echo $count_items_listed; ?></p>

The database holds three columns which contain the following test information

user_id, items_listed, live_items_listed

12 1 0
12 0 1
12 1 0

But when I use:

<p>Number of live items currently listed: <?php echo $count_live_items_listed; ?></p>
<p>Number of items currently listed: <?php echo $count_items_listed; ?></p>

The results for both show a result of 3, instead of live_items_listed being equal to 2 and items_listed equal to 1.

Any help is much appreciated

Paul

[requinix]

COUNT() will count non-null values. Not distinct values.

 

Either you want a condition WHERE items_listed = 1 or you want a SUM() of the values, it depends how those values are managed.