I'm trying to duplicate THIS PAGE with a database I've created.
Here is my database structure:
CREATE TABLE IF NOT EXISTS `player` (
`playerid` int(10) NOT NULL AUTO_INCREMENT,
`fname` varchar(255) NOT NULL,
`lname` varchar(255) NOT NULL,
`jersey` tinyint(2) DEFAULT NULL,
`position` enum('Cornerback','Defensive End','Defensive Tackle','Fullback','Halfback','Inside Linebacker','Kicker','Long Snapper','Offensive Center','Offensive Guard','Offensive Tackle','Outside Linebacker','Punter','Quarterback','Safety','Tight End','Wide Receiver') DEFAULT NULL,
`height` enum('5-foot','5-foot-1','5-foot-2','5-foot-3','5-foot-4','5-foot-5','5-foot-6','5-foot-7','5-foot-8','5-foot-9','5-foot-10','5-foot-11','6-foot','6-foot-1','6-foot-2','6-foot-3','6-foot-4','6-foot-5','6-foot-6','6-foot-7','6-foot-8','6-foot-9','6-foot-10','6-foot-11','7-foot') DEFAULT NULL,
`weight` smallint(3) DEFAULT NULL,
`school` varchar(255) DEFAULT NULL,
`hometown` varchar(255) DEFAULT NULL,
`state` enum('AK','AL','AR','AS','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','ME','MD','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WI','WV','WY') DEFAULT NULL,
`birthdate` date DEFAULT NULL,
`class` enum('Freshman','Sophomore','Junior','Senior') DEFAULT NULL,
`recruitclass` smallint(4) DEFAULT NULL,
`onscholarship` enum('Yes','No') NOT NULL DEFAULT 'Yes',
`isactive` enum('Yes','No') NOT NULL DEFAULT 'Yes',
PRIMARY KEY (`playerid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=62 ;
Here is the query that I'm using to get me the results I want:
SELECT * FROM player WHERE onscholarship = 'Yes' AND isactive = 'Yes' ORDER BY FIELD(position,'Quarterback','Halfback','Fullback','Wide Receiver','Tight End','Offensive Center','Offensive Guard','Offensive Tackle','Defensive Tackle','Defensive End','Outside Linebacker','Inside Linebacker','Cornerback','Safety','Kicker','Punter'), FIELD(class,'Senior','Junior','Sophomore','Freshman'), lname
I just can't seem to come up with a way to output the data as the page I'm trying to duplicate does. Can anyone give me some guidance or ideas to go off of? Can it be done with just that query?
Another idea I had is to use one query to get the distint positions, and then inside run another query to do the players by class, but I couldn't get that to output like the original page, either. Additionally, I thought it would be a bad idea since it would create quite a few more queries.
