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