select, count, and then update with results

viveleroi0

What is the fastest way to count the number of matching records in a MySQL database for each single records?

For example, I have a database of area codes and prefixes of ten thousand calls coming in to my business. I want to count the number of times that each prefix and area code combination occurr.

Right now, I SELECT * that returns the fields AC and PRE

I then run a FOR loop that runs another SELECT * WHERE AC = $AC and PRE = $PRE and counts the number of rows returned.

Then I UPDATE table SET count = ROWS RETURNED FROM STEP 2 WHERE AC = $AC AND PRE = $PRE

So that is three queries for each of ten thousand rows, so it takes a LONG time.

How can I improve that? I simply want to identify all the different combinations and count the database the number of times each unique combination occurs.

Then update a database field with the number of occurrences.

devinemke

SELECT COUNT(*), AC, PRE FROM table GROUP BY AC, PRE

this will return the count for each AC PRE pair. then just do an INSERT query with the result.

viveleroi0

I have tried that type of query, but I have these issues:

A) It seems to count 303 unique area codes, which is right. But it only loops through 150 or so - why?

😎 It does not count the area codes correctly. When I do a test and echo the count, it displays incorrect counts for every area code. For example it says there are 9 counts of area code 602 (while there are really 438).

Here is my code:

$query		= mysql_query("SELECT " . $DB['field']['calls']['ac'] . ", COUNT(*) AS count 
FROM " . $DB['table']['calltracking'] . " 
GROUP BY " . $DB['field']['calls']['ac'] . "");

while($row = mysql_fetch_assoc($query)){

$query_row = mysql_fetch_array($query);
$phone_ac	=	$query_row[$DB['field']['calls']['ac']];


$save="UPDATE " . $DB['table']['calltracking'] . " 
SET " . $DB['field']['calls']['ac_ct'] . "=\"" . $row['count'] . "\" 
WHERE " . $DB['field']['calls']['ac'] . "=\"$phone_ac\"";



$Record_Item=mysql_query($save);


}

nemonoman

SELECT COUNT(concat(AC, PRE)), concat(AC, PRE) FROM table GROUP BY concat(AC, PRE)

Index AC and PRE.

nemonoman

PS if indexed, that query should be so fast you won't need to update a table to see the results; just run the query.