Using PHP to pull Count(*) information

drr37

I have Bug tracking database that tracks the email address and the name of the person assigned to fix the bug. Sometimes on each bug the name given for the person is different (full name vs. first name, nicknames, misspellings, etc.), even though their email might be the same. So, in order print a report, I need to find the most common name associated with each email address, which I am assuming will be the full name. (I won't go in to why this information isn't stored in another table and connected with an ID.) What would be the best MySQL query to do this? At this point I have this query:

SELECT AssignedTo, COUNT(AssignedTo) FROM Bugs WHERE AssignedEmail = '$email' GROUP BY AssignedTo;

This of course just gives me a list of the different names with the number of times used. How do I use PHP to extract the information from this output so I can analyze it and get the right name? Or, is there a better query that would give me exactly what I need?

Thanks.

Bunkermaster

hi,

your query is not bad but the count() part is useless, if you want to find the most comonly used name you'll have to loop through your records and count every instance of each name (case unsensitive match loop might be a good choice).
at first make first one the most common name and then count instances and each time you got a more common name change nickname variable...
should be easy.

That's how i'd do it now (1:30 AM here) after 12 hours of heavy coding BUT i may not have the best solution.

GL & HF

examancer

no, that SQL statement should work just find, with only a couple changes:

SELECT AssignedTo, COUNT(*) as name_count FROM Bugs WHERE AssignedEmail = '$email' GROUP BY AssignedTo ORDER BY name_count DESC LIMIT 1

this will return only the name that occurs the most, and the number of time it occurs. If you remove LIMIT 1 it will return all of the different names starting with the one that occurs the most.

the php code to use this query looks something like this:

$sql = "SELECT AssignedTo, COUNT(*) as name_count FROM Bugs WHERE AssignedEmail = '$email' 
GROUP BY AssignedTo ORDER BY name_count DESC LIMIT 1";
$q = mysql_query($sql);
$r = mysql_fetch_array($q);
if (!$r){
  echo("$email not found.");
}
echo("The most common name for " . $email . " is " . $r['AssignedTo'] . " which occurs " . 
$r['name_count'] . " times.");

Bunkermaster

oops had missed the group by...

am off to bed now

/me waves at the daylight side of the earth

drr37

Thanks, that works perfectly.

michael

If it were my db, I would do a select unique from the email, and then do a count on each one of those emails, and sort by that count.

Maybe that would be faster?

Michael