counting identical matches (postcodes/zipcodes) in an array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

counting identical matches (postcodes/zipcodes) in an array

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

wouldn't using a GROUP BY work better?

http://dev.mysql.com/doc/refman/4.1/en/select.html
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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 :(
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

no join, just a COUNT()
User avatar
PrObLeM
Forum Contributor
Posts: 418
Joined: Sun Mar 07, 2004 2:30 pm
Location: Mesa, AZ
Contact:

Post 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...
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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)
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
litebearer
Forum Contributor
Posts: 194
Joined: Sat Mar 27, 2004 5:54 am

Post 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...
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply