Count rows on a SELECT FROM WHERE result

[slj90]

 $result = mysql_query("SELECT *  FROM `friend_system` WHERE `friend_system_accepter` = '$myusername' ");
echo mysql_num_rows($result);

This displays the total rows in the table.

$result = mysql_query("SELECT COUNT(friend_system_accepter)  FROM `friend_system` WHERE `friend_system_accepter` = '$myusername' ");
echo mysql_num_rows($result);

This displays '1', which is incorrect.

 

I want to echo out the number of rows where 'friend_system_accepter' = $myusername

 

Thanks

[requinix]

Your query will return to you a single number that counts how many rows there are.

 

Put another way, how do you expect COUNT() to work if the query has to return every single row?

[slj90]

So how would I count only the rows where the data = 'x'?

[Barand]

example

$sql = "SELECT COUNT(*) as tot 
        FROM mytable 
        WHERE data = 'x'";
$res = mysql_query($sql);
$row = mysql_fetch_assoc($res);
echo $row['tot'];                 // --> number of records

NB: Stop using mysql_xxx functions and change to mysqli_xxx or PDO