Count as 1 on same name

[queryguy]

This code will count the numbers of rows each name have and add up.

$pqfinal=0
$pq="select * from cmp where cid='$cid'";
$pqresult = mysql_query ($pq);

while($pqlist = mysql_fetch_array($pqresult)) {

$q="select * from cmp where pid='$pqlist[pmid]'";
$results = mysql_query ($q);
$pqcount = mysql_num_rows($results); //I have multiple rows from these results I want to add up
the rows from these results.
echo $pqlist['pmid'].&quot; = have &quot;.$pqcount.&quot; rows<br>\n&quot;;
$pqfinal=$pqfinal+$pqcount;

}
echo &quot;<br>total &quot;.$pqfinal.&quot; rows&quot;;

My problem was, if the results have 2 same name how can I count as 1 results (mean only 7 rows instead of 14 rows based on the example).

name1 = have 7 rows
name1 = have 7 rows
name3 = have 6 rows
name4 = have 9 rows

Desire result will be
name1 = have 7 rows
name3 = have 6 rows
name4 = have 9 rows

 

[sleipnir214]

Why not just let MySQL do the work for you?

A query like:

SELECT pmid, count(*) from cmp group by pmid


will return a count of records, broken down by pmid.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[queryguy]

$pq=&quot;pmid, count(*) from cmp group by pmid&quot;;
$pqresult = mysql_query ($pq);
while($pqlist = mysql_fetch_array($pqresult)) {


echo &quot;$pqlist[]pmid]&quot;;

}

hmm.. I got no results display, any helps?

 

[sleipnir214]

$pq=&quot;pmid, count(*) from cmp group by pmid&quot;;

You seem to be missing the MySQL keyword &quot;SELECT&quot; in that statement.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[jimoblak]

Consider using a MySQL client to test your queries before scripting into PHP.

See:

http://sourceforge.net/projects/phpmyadmin/

Testing queries outside of your script will help pinpoint if the error is in your MySQL query or your PHP script.

- - picklefish - -
Why is everyone in this forum responding to me as picklefish?

 

[sleipnir214]

Or at the very least performing some minimal error-catching in your code:

$pqresult = mysql_query ($pq) or die(mysql_error());




Also, your code should have given you a &quot;not a valid MySQL resource&quot; error in this line:

while($pqlist = mysql_fetch_array($pqresult)) {

What are your error-display settings, specifically your php.ini setting for error_reporting?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!