Display list of how many times a # appears in a database
Moderator: General Moderators
-
TheBrandon
- Forum Commoner
- Posts: 87
- Joined: Tue May 20, 2008 8:55 am
Display list of how many times a # appears in a database
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?
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?
- 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
Try :
Hope this helps.
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].')';
} ?>“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
Hmm, that just output "(x0)(x0)(x0)"
I had to modify it slightly to make it work with my database class:
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).
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].')';
} ?>- 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
My bad there, replace the first query with
It will now select all the records.
Code: Select all
<?php $select_query = mysql_query("SELECT * FROM table"); ?>“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
Hmm, I did and it still outputs this:
32578(x0)32578(x0)32578(x0)
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?
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].')';
}
?>Re: Display list of how many times a # appears in a database
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");
- 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
Code: Select all
<?php $count = $db->query("SELECT COUNT(zip_code) FROM surveys WHERE zip_code = '$select_array[32578]'"); ?>“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
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.')';
}
?>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
I made some more motifications. It's getting closer but it's still not there:
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.
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.'), ';
}
}
?>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
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
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
Got it.
This is the final code:
It will output:
32578( x3), 32579, 32767,
Thanks everyone. I really appreciate the help.
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'].', ';
}
}
?>32578( x3), 32579, 32767,
Thanks everyone. I really appreciate the help.
- 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
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