Count number of records

[lilgezuz]

I need help with a count query and displaying it.  Right now have the following query

$query = "SELECT COUNT(*) as num1 FROM spoiler WHERE id='$id";
$total_trophies = mysql_fetch_array(mysql_query($query));
$total_trophies = $total_trophies[num1];

 

So I have another query that pulls how many trophies there are.  That way I can do the following

 

?  echo $total_trophies; echo 'of'; echo $row[trophy_count];  ?>

 

But the problem is sometimes in my database I could have mulitple entrys per trophy.  For Trophy 1 I might have only one record so It counts it correctly for trophy 2 I could have 3 records so It counts each one of those records when I only one to count it once for that trophy

[requinix]

Then... you need to change the query?

 

With a name like "id" I would have expected it to be unique for the entire table, but I guess that's not the case. Does it have the same value for the same kind of trophy? So all Trophy 1s have one value while all Trophy 2s have a different one value?

[lilgezuz]

Then... you need to change the query?

 

With a name like "id" I would have expected it to be unique for the entire table, but I guess that's not the case. Does it have the same value for the same kind of trophy? So all Trophy 1s have one value while all Trophy 2s have a different one value?

 

Its suppose to be pid=$id.  So I could have

 

Id1 Pid1 Trophy 1

Id2 Pid2 Trophy 1

Id3 Pid2 Trophy 1

Id4 Pid2 Trophy 2

 

See how Pid2 records for Trophy 1 I need to count that as one result not two

[requinix]

Throw in a DISTINCT.

SELECT COUNT(DISTINCT the column about the trophy) as num1...

 

Side note: when doing stuff with strings in PHP, always use quotes. Field names from SQL queries count.

$total_trophies["num1"]

[lilgezuz]

Throw in a DISTINCT.

SELECT COUNT(DISTINCT the column about the trophy) as num1...

 

Side note: when doing stuff with strings in PHP, always use quotes. Field names from SQL queries count.

$total_trophies["num1"]

 

Thanks mate worked like a charm.  Not sure why I didn't have quotes around the num1 I know better then that