Looking for php database operations like top 5 entries
Moderator: General Moderators
Looking for php database operations like top 5 entries
I have a mySQL database that has 20 names per entry. I want to count all the entry names and see which entries come up the most often.
Example DB structure:
entry1-dog-cat-bird-pig
entry2-cat-horse-crow-fish
entry3-fish-dog-pig-snake
entry4-cow-dog-fish-beaver
entry5-dog-fly-fox-bug
I want the php to echo the top picks:
Dog 4
Fish 3
Pig 2
Where do I begin? I'm looking for website tutorials or books.
RR
Example DB structure:
entry1-dog-cat-bird-pig
entry2-cat-horse-crow-fish
entry3-fish-dog-pig-snake
entry4-cow-dog-fish-beaver
entry5-dog-fly-fox-bug
I want the php to echo the top picks:
Dog 4
Fish 3
Pig 2
Where do I begin? I'm looking for website tutorials or books.
RR
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
I though a different structure might be in order, but I am still confused as to what.
Can you populate your sample with a couple entries? I'm confused, do you mean like this.
I don't quite understand how animals can be split fromeach entryID as different entrants might pick some of the same animals.
I know you have my answer I just need a little more hand holding.
Thanks
Can you populate your sample with a couple entries? I'm confused, do you mean like this.
I don't quite understand how animals can be split fromeach entryID as different entrants might pick some of the same animals.
I know you have my answer I just need a little more hand holding.
Thanks
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
Can you explain in a little more detail what you are trying to do and what/how you are capturing this data?
Given that people can choose from say 20 different choices, you have designed around that. What if it changes to 25 different choices and you need the top 10? And - can the free-form enter anything they can imagine? i.e. skunk, African Ciclid, butterfly or tasmanian devil???
Phil J.
Given that people can choose from say 20 different choices, you have designed around that. What if it changes to 25 different choices and you need the top 10? And - can the free-form enter anything they can imagine? i.e. skunk, African Ciclid, butterfly or tasmanian devil???
Phil J.
- hob_goblin
- Forum Regular
- Posts: 978
- Joined: Sun Apr 28, 2002 9:53 pm
- Contact:
i'd do the query, then do a loop to get all the rows:
try something like that.
Code: Select all
$query = "SELECT * FROM table";
while($data = mysql_fetch_assoc($query)){
foreach($data as $key => $value){
if(!empty($value)){
$arrayї$key]++;
}
}
}
print_r($array);I tired your code but it just spits out the name of every column I have with the count of 17 for each entry. That is because I have 17 entrants.
Maybe I am explaining my situation wrong.
Let's say
you and I, and a friend are in competition and we pick the following
DB structure
player,pick1,pick2,pick3,pick4,pick5
3dron,dogs,cats,cars,trains,tv
hob_goblin,trains,box,desk,cheese,cats
friend,desk,cats,fire,web,paper
Your script tells me there are 3 players, 3 pick1's, 3 pick2's,etc
I am trying to get the count of cats=3, desk=2,train=2
I won't have a list or DB of choices to "select" from since each entrant could pick from thousands of undefiened entries.
AH HELP!!!!
Ron
Maybe I am explaining my situation wrong.
Let's say
you and I, and a friend are in competition and we pick the following
DB structure
player,pick1,pick2,pick3,pick4,pick5
3dron,dogs,cats,cars,trains,tv
hob_goblin,trains,box,desk,cheese,cats
friend,desk,cats,fire,web,paper
Your script tells me there are 3 players, 3 pick1's, 3 pick2's,etc
I am trying to get the count of cats=3, desk=2,train=2
I won't have a list or DB of choices to "select" from since each entrant could pick from thousands of undefiened entries.
AH HELP!!!!
Ron
Basic Example:
For pick1 it will use +5 for pick2 it will use +4 pick3 +3 pick4 +2 pick5 +1
although to do it with all just +1 use
$picks[$pick[0]]+=$pick[2];
hehe, good luck
Code: Select all
$total_picks=5;
$picks=array();
for($p=1;$p<=$total_picks;$p++)
{
$q = mysql_query("SELECT pick".$p.", COUNT(*) FROM table GROUP BY pick".$p);
while($pick=mysql_fetch_array($q))
$picksї$pickї0]]+=(($total_picks+1)-$p)*$pickї2];
}although to do it with all just +1 use
$picks[$pick[0]]+=$pick[2];
hehe, good luck