Page 1 of 1
counting identical matches (postcodes/zipcodes) in an array
Posted: Wed May 24, 2006 6:04 pm
by robster
Hi all,
I have just parsed the database and thrown all the post codes from our client database into an array (around 1000 clients in total).
What I need to figuire out is how I can then parse through the array and find (count) the matching post codes.
Here is the code so far:
Code: Select all
//get the postcodes from the database and stick it in an array
//========================================================
mysql_select_db($dbname);
$sql = "SELECT * FROM clients ORDER BY add_pcode ASC";
$content = mysql_query($sql);
$Xcontent = mysql_fetch_array($content);
$cShowMax = mysql_num_rows($content);
for ($y=1; $y<=$cShowMax; $y++)
{
//Get the info from the database and smack it in variables for user later
$client_id = $Xcontent["id"]; //get the client id, just in case?
$add_pcode = $Xcontent["add_pcode"]; //get the clients Post Code
//stick the post code into an array
$pcode_array["$postcode"] = $add_pcode;
$Xcontent = mysql_fetch_array($content);
}
I guess, now I have it there, what can I do with it? A logic example would even be appreciated as I can work out the code from that I'm sure... Unless of course there's some super cool function out there?
Any help really appreciated.
Rob
Posted: Wed May 24, 2006 6:20 pm
by feyd
Posted: Wed May 24, 2006 6:31 pm
by robster
That looks like a wicked function, never seen it before. Perhaps I'm wrong, but it doesn't seem to allow me to show how many clients there are in each post code. Can it do that?
Currently if I try it, I just get back the individual postcodes (which is great) but i need to see how many clients share each post code. I need to see which areas my clients are coming from.
Thanks a tonne.
Rob
PS please don't tell me a join is needed. I for the life of me can't get them in my head

Posted: Wed May 24, 2006 6:36 pm
by feyd
no join, just a COUNT()
Posted: Wed May 24, 2006 7:21 pm
by PrObLeM
Code: Select all
SELECT COUNT(*) as cnt, add_pcode FROM clients ORDER BY add_pcode ASC GROUP BY add_pcode
they you will have
Code: Select all
add_pcode | cnt
------------------------
aaaaa | #
bbbbb | #
etc...
Posted: Thu May 25, 2006 2:23 am
by robster
Thanks everyone.
When I try this (and similar count queries) like so for example:
Code: Select all
155 mysql_select_db($dbname);
156 $sql = "SELECT COUNT(*) as cnt, add_pcode FROM clients ORDER BY add_pcode ASC GROUP BY add_pcode";
157 $content = mysql_query($sql);
158 $Xcontent = mysql_fetch_array($content);
159 $cShowMax = mysql_num_rows($content);
I get this:
Code: Select all
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in d:\sites\salon\report_type_postcode_breakdown.php on line 158
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in d:\sites\salon\report_type_postcode_breakdown.php on line 159
I'm not really sure what's wrong. I can show a bit of the CLIENTS table if this helps?:
Code: Select all
id int(11)
name_first varchar(50)
name_initial varchar(5)
name_last varchar(50)
phone_work varchar(50)
phone_home varchar(50)
phone_mobile varchar(50)
email varchar(100)
recieve_emails varchar(10)
confirmation varchar(50)
add_street varchar(150)
add_city varchar(50)
add_state varchar(50)
add_pcode varchar(20)
Posted: Thu May 25, 2006 4:49 am
by JayBird
your query is wrong
Code: Select all
"SELECT COUNT(*) as cnt, add_pcode FROM clients GROUP BY add_pcode ORDER BY add_pcode ASC";
GROUP comes before ORDER
Posted: Thu May 25, 2006 8:04 am
by robster
ok, no error now, but well, i presume i need an explination on how it all works. I really appreciate the code but i don't quite understand it (although it seems to work

)
I'm wondering if someone could sort of, spell out the meaning of that? If you have time. I understand people are busy though, but if there is someone out there, then thank you.
Also I'm wondering, how can I get the value of cnt to display the count with an echo. ie: echo "$cnt";
As you can see, I'm not quite comprehending what's happening here
Thanks again,
Rob
Posted: Thu May 25, 2006 10:35 am
by Weirdan
Posted: Thu May 25, 2006 5:51 pm
by robster
mmm, that tutorial seems to suffer the same that all the other tutorials do on the topic. They all give the example of calculating the sum of a particular field. What I'm after is finding out how many items are in each group after performing the GROUP BY.
my data looks more like:
Code: Select all
client_id add_pcode
1 4000
2 4000
3 4500
4 4090
5 4090
6 4500
7 4090
8 4000
9 4000
10 4000
What I'm looking for is this result:
Code: Select all
postcode number of clients in postcode
4000 5
4500 2
4090 3
None of the tutorials I've found so far show how to handle this, and as a newby on this topic I really don't know much about it to go and feel it out just yet.
Any help really appreciated.
Rob
Posted: Thu May 25, 2006 6:40 pm
by litebearer
try this where $a is your array of zipcodes
Code: Select all
<?php
$a=array("Cat","Dog","Horse","Dog");
print_r(array_count_values($a));
?>
Lite...
Posted: Fri May 26, 2006 5:17 am
by timvw
robster wrote:mmm, that tutorial seems to suffer the same that all the other tutorials do on the topic. They all give the example of calculating the sum of a particular field. What I'm after is finding out how many items are in each group after performing the GROUP BY.
Hmmz, that user seems to suffer the same that all users do on the topic: They don't make clear what they really want to do
What I need to figuire out is how I can then parse through the array and find (count) the matching post codes.
Do you want to count how many other clients have the same pcode?
Code: Select all
SELECT c1.client_id, count(*)
FROM clients AS c1
OUTER JOIN clients AS c2 ON c1.pcode = c2.pcode
WHERE c1.client_id <> c2.client_id
GROUP BY c1.client_id