Looking for php database operations like top 5 entries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
3dron
Forum Commoner
Posts: 40
Joined: Sat Feb 01, 2003 10:25 pm

Looking for php database operations like top 5 entries

Post by 3dron »

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
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Can you explain your DB structure more fully?
Are the entries a table and the names a related table? Or are the entries
one free-form text field? Makes a big difference!

Phil J.
3dron
Forum Commoner
Posts: 40
Joined: Sat Feb 01, 2003 10:25 pm

Post by 3dron »

My entries of each "animal" are under seperate fields animals1 thru animals20. Not one freeform text field.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

I think you may want to reconsider your database structure, so you do not have to refer to animal1,animal2,animal3....all the way to animal20.

Break it out into two tables

Table Entries
EntryID
...and other entry fields

and Animals
EntryID
AnimalID
AnimalName
3dron
Forum Commoner
Posts: 40
Joined: Sat Feb 01, 2003 10:25 pm

Post by 3dron »

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
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

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.
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

i'd do the query, then do a loop to get all the rows:

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);
try something like that.
3dron
Forum Commoner
Posts: 40
Joined: Sat Feb 01, 2003 10:25 pm

Post by 3dron »

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
ReDucTor
Forum Commoner
Posts: 90
Joined: Thu Aug 15, 2002 6:13 am

Post by ReDucTor »

Basic Example:

Code: Select all

$total_picks=5;
$picks=array();
for($p=1;$p<=$total_picks;$p++)
&#123;
$q = mysql_query("SELECT pick".$p.", COUNT(*) FROM table GROUP BY pick".$p);
while($pick=mysql_fetch_array($q))
$picks&#1111;$pick&#1111;0]]+=(($total_picks+1)-$p)*$pick&#1111;2];
&#125;
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 :)
Post Reply