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
wouldn't using a GROUP BY work better?

http://dev.mysql.com/doc/refman/4.1/en/select.html

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