Page 1 of 1

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

Posted: Wed Apr 28, 2010 9:25 am
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?

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

Posted: Wed Apr 28, 2010 9:50 am
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.

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

Posted: Wed Apr 28, 2010 9:59 am
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).

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

Posted: Wed Apr 28, 2010 10:07 am
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.

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

Posted: Wed Apr 28, 2010 10:29 am
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?

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

Posted: Wed Apr 28, 2010 11:20 am
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");


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

Posted: Wed Apr 28, 2010 11:27 am
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).

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

Posted: Wed Apr 28, 2010 11:47 am
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)

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

Posted: Wed Apr 28, 2010 12:11 pm
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.

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

Posted: Wed Apr 28, 2010 12:14 pm
by Eran
You should use mikosiko GROUP'ed query, it's much more efficient.

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

Posted: Wed Apr 28, 2010 12:20 pm
by TheBrandon
I didn't see that post, my mistake. I'll give it a shot. Thank you.

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

Posted: Wed Apr 28, 2010 12:41 pm
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.

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

Posted: Wed Apr 28, 2010 1:05 pm
by social_experiment
My only issue was with the double entries my script left but the 'GROUP BY' solution solves that