Hey all,
I have a competition set up on my site, and would like to produce a leage table (essentially a table with the person who has the most wins at the top, and then moving down the list..you know the deal).
I have built a function below, but I am struggling to create the code needed to produce the table.
First of all, here is the database structure;
CREATE TABLE IF NOT EXISTS cms_competition (
competition_id int(10) unsigned NOT NULL auto_increment,
start_date datetime default NULL,
word1 varchar(20) NOT NULL default '',
word2 varchar(20) NOT NULL default '',
word3 varchar(20) NOT NULL default '',
word4 varchar(20) NOT NULL default '',
word5 varchar(20) NOT NULL default '',
word6 varchar(20) NOT NULL default '',
word7 varchar(20) NOT NULL default '',
word8 varchar(20) NOT NULL default '',
word9 varchar(20) NOT NULL default '',
word10 varchar(20) NOT NULL default '',
status enum('0','1') NOT NULL default '0',
winner varchar(30) NOT NULL default '',
winner_id int(10) NOT NULL default '0',
PRIMARY KEY (competition_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=42 ;
CREATE TABLE IF NOT EXISTS cms_competitionentries (
entry_id int(10) unsigned NOT NULL auto_increment,
competition_id int(10) NOT NULL default '0',
entrant varchar(30) NOT NULL default '',
submit_date datetime default NULL,
activated enum('0','1') NOT NULL default '0',
is_member enum('0','1') NOT NULL default '0',
entry text NOT NULL,
entrant_id int(10) NOT NULL default '0',
PRIMARY KEY (entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4536 ;
So, I would like the league table to produce something like the following;
Jambo 12
Eric 12
Jim 10
Sarah 6
Kevin 4
Lucy 2
etc...
I know that I need to group the competition table by winner (or something like that). In fact, after looking at it here, I don't even think the competition_entries table will need to be used.
Any help will be much appreciated.
Thanks,
Jamie
