Display list of how many times a # appears in a database

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
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Display list of how many times a # appears in a database

Post by TheBrandon »

Hello all,

I have a database of zip codes and I need to display each zip code and the amount of times it appears in the database.

For example, let's say my database consists of:

1, 1, 1, 3, 7, 1, 9, 4, 7

I want to print out:

1 (x4), 3, 4, 7 (x2), 9

What would be the most efficient way of doing this?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Display list of how many times a # appears in a database

Post by social_experiment »

Try :

Code: Select all

<?php
$select = mysql_query("SELECT * FROM table WHERE zip_code = 'value'");
while ( $select_array = mysql_fetch_array($select)) {
 echo $select_array['value'];
 // the next query counts the amount of times the value of 'zip_code'
 // appears in the database.
$count = mysql_query("SELECT COUNT(zip_code) FROM table WHERE zip_code = '$select_array['zip_code_value']'");
$rows = mysql_fetch_array($count);
$numRec = $rows[0];
 echo '(x'.$rows[0].')';
} ?>
Hope this helps.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Display list of how many times a # appears in a database

Post by TheBrandon »

Hmm, that just output "(x0)(x0)(x0)"

I had to modify it slightly to make it work with my database class:

Code: Select all

<?php
$select = $db->query("SELECT * FROM surveys WHERE zip_code = '32578'");
while ($select_array = mysqli_fetch_array($select)) {
 echo $select_array['32578'];
 // the next query counts the amount of times the value of 'zip_code'
 // appears in the database.
$count = $db->query("SELECT COUNT(zip_code) FROM surveys WHERE zip_code = '$select_array[32578]'");
$rows = mysqli_fetch_array($count);
$numRec = $rows[0];
 echo '(x'.$rows[0].')';
} ?>
I'm going to have to modify it to execute during a loop as well (so I don't have to manually feed it a zipcode).
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Display list of how many times a # appears in a database

Post by social_experiment »

My bad there, replace the first query with

Code: Select all

<?php $select_query = mysql_query("SELECT * FROM table"); ?>
It will now select all the records.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Display list of how many times a # appears in a database

Post by TheBrandon »

Hmm, I did and it still outputs this:
32578(x0)32578(x0)32578(x0)

Code: Select all

<?php
	$select = $db->query("SELECT * FROM surveys");

	while ($select_array = mysqli_fetch_array($select)) {

		echo $select_array['zip_code'];
		// the next query counts the amount of times the value of 'zip_code'
		// appears in the database.

		$count = $db->query("SELECT COUNT(zip_code) FROM surveys WHERE zip_code = '$select_array[32578]'");
		$rows = mysqli_fetch_array($count);
		$numRec = $rows[0];
	 
		echo '(x'.$rows[0].')';
	} 
?>
I think the (xRows) echo needs to be outside of the loop but it doesn't appear to be incremented at all. Also the $numRec variable doesn't seem to be doing anything?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Display list of how many times a # appears in a database

Post by mikosiko »

Humm... instead of do 2 selects is not easier to do just one select to do this?

Code: Select all

$select = mysql_query("SELECT zip_code, COUNT(zip_code) FROM table GROUP BY zip_code");

User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Display list of how many times a # appears in a database

Post by social_experiment »

Code: Select all

<?php $count = $db->query("SELECT COUNT(zip_code) FROM surveys WHERE zip_code = '$select_array[32578]'"); ?>
'$select_array[32578]' should be the value of the zip code that you get from '$select_array['zip_code']. Ill test the syntax to see where the error might be. Also, swop out '$rows[0]' with '$numrec' (though it shouldn't make a difference).
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Display list of how many times a # appears in a database

Post by TheBrandon »

Code: Select all

<?php
	$select = $db->query("SELECT * FROM surveys");

	while ($select_array = mysqli_fetch_array($select)) {

		echo $select_array['zip_code'];
		// the next query counts the amount of times the value of 'zip_code'
		// appears in the database.

		$count = $db->query("SELECT COUNT(zip_code) FROM surveys WHERE zip_code = '$select_array[zip_code]'");
		$rows = mysqli_fetch_array($count);
		$numRec = $rows[0];
	 
		echo '(x'.$numRec.')';
	} 
?>
Is this correct?

This outputs: 32578(x3)32578(x3)32578(x3)
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Display list of how many times a # appears in a database

Post by TheBrandon »

I made some more motifications. It's getting closer but it's still not there:

Code: Select all

<?php
	$select = $db->query("SELECT * FROM surveys");

	while ($select_array = mysqli_fetch_array($select)) {

		$count = $db->query("SELECT COUNT(zip_code) FROM surveys WHERE zip_code = '$select_array[zip_code]'");
		$rows = mysqli_fetch_array($count);
		$numRec = $rows[0];
	 
		if($numRec < 2){
			echo $select_array['zip_code'].', ';
		}
		
		if($numRec > 1){
			echo $select_array['zip_code'];
			echo '(x'.$numRec.'), ';
		}
	} 
?>
That outputs:
32578(x3), 32578(x3), 32578(x3), 32579, 32767,

(I added 2 more zip codes to the database)

So now it's properly adding the x3 when it needs to, it just only needs to display it one time.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Display list of how many times a # appears in a database

Post by Eran »

You should use mikosiko GROUP'ed query, it's much more efficient.
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Display list of how many times a # appears in a database

Post by TheBrandon »

I didn't see that post, my mistake. I'll give it a shot. Thank you.
TheBrandon
Forum Commoner
Posts: 87
Joined: Tue May 20, 2008 8:55 am

Re: Display list of how many times a # appears in a database

Post by TheBrandon »

Got it.

This is the final code:

Code: Select all

<?php
	$select = $db->query("SELECT zip_code, COUNT( zip_code ) FROM surveys GROUP BY zip_code");
	
	while ($select_array = mysqli_fetch_array($select)) {
		
		if($select_array['1'] > 1){
			echo $select_array['zip_code'];
			echo '( x'.$select_array['1'].'), ';
		}else{
			echo $select_array['zip_code'].', ';
		}
		
	}
	
?>
It will output:
32578( x3), 32579, 32767,

Thanks everyone. I really appreciate the help.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Display list of how many times a # appears in a database

Post by social_experiment »

My only issue was with the double entries my script left but the 'GROUP BY' solution solves that
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply