Page 1 of 1

Looking for php database operations like top 5 entries

Posted: Mon Feb 03, 2003 5:00 pm
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

Posted: Mon Feb 03, 2003 11:38 pm
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.

Posted: Tue Feb 04, 2003 1:58 am
by 3dron
My entries of each "animal" are under seperate fields animals1 thru animals20. Not one freeform text field.

Posted: Tue Feb 04, 2003 10:24 am
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

Posted: Tue Feb 04, 2003 9:15 pm
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

Posted: Wed Feb 05, 2003 9:56 pm
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.

Posted: Wed Feb 05, 2003 10:01 pm
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.

Posted: Thu Feb 06, 2003 12:59 am
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

Posted: Thu Feb 06, 2003 4:35 am
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 :)