[SOLVED] Counting unique rows

[ShopMAster]

I have a script that counts how many members have given good feedback for this person, but how do I specify how many unique members have given this person good feedback.  Meaining I don't want to count member1 2 times becuase he left feeback twice, I want to only count him once.

 

 

$total_num = mysql_result ( mysql_query("select count(*) as nr from feedback where rec ='".$_SESSION['id']."'") ,0,"nr" );

 

The above code is how I got the total number of feedbacks

 

[per1os]

$total_num = mysql_result ( mysql_query("select count(*) as nr from feedback where rec ='".$_SESSION['id']."' GROUP BY memberid") ,0,"nr" );

 

replace memberid with the column name of how you track members.

 

--FrosT

[ShopMAster]

I track members in the field 'giver' but when I put that in there it still is giving me two rows for one person giving 2 feedbacks.

 

Also when no one has given a feedback I get : Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 16 in D:\Domains\mwchallenge.com\wwwroot\myfeedback.php on line 27

 

Shouldn't it return 0 using the code above?

[ShopMAster]

Here is the code I'm using.  The first one should give me the # of members who gave a positive feedback and the second for negative feedback.  Both should only give me a count for unique 'giver' fields, correct?

 

$nrfn = mysql_result ( mysql_query("select count(*) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 3 GROUP BY giver") ,0,"nr" );

$nrfp = mysql_result ( mysql_query("select count(*) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 1 GROUP BY giver") ,0,"nr" );

[per1os]

The reason being is the count(*).  Try this:

 

$nrfn = mysql_result ( mysql_query("select count(giver) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 3 GROUP BY giver") ,0,"nr" );

$nrfp = mysql_result ( mysql_query("select count(giver) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 1 GROUP BY giver") ,0,"nr" );

 

--FrosT

[Barand]

Instead of using COUNT(*) which gives a count of the rows you can use

 

COUNT(DISTINCT colname)

 

Which give a count of unique values in that column.

[ShopMAster]

I get the following error when the count is 0;

 

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 16 in D:\Domains\mwchallenge.com\wwwroot\myfeedback.php on line 27

 

how do I just display 0 when I do <?=$nrfn?>

[ShopMAster]

btw - Barand, that worked, except for the 0 thing.

 

[Orio]

Can you show the code up to now?

 

Orio.

[per1os]

I don't use mysql_result. I usually use mysql_fetch_array or mysql_fetch_assoc. That is pretty simple. Either way to avoid the warning do this:

 

$nrfn = @mysql_result ( mysql_query("select count(giver) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 3 GROUP BY giver") ,0,"nr" );

$nrfp = @mysql_result ( mysql_query("select count(giver) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 1 GROUP BY giver") ,0,"nr" );

 

That should work.

 

--FrosT

[Barand]

Don't do this, period

$nrfn = mysql_result ( mysql_query("select count(giver) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 3 GROUP BY giver") ,0,"nr" );

Do

$result = mysql_query("select count( distinct giver) as nr from feedback where rec ='".$_SESSION['id']."' and rating = 3 GROUP BY giver")  or die (mysql_error());

if (mysql_num_rows($result) > 0) {
   $nrfn = mysql_result($result, 0);
}
else $nrfn = 0;

 

[ShopMAster]

Thanks guys for the help.  Problem solved.