php voting poll linked with a database

Question

I'm looking to design a piece of code for a poll that allows me to award points and store them into a table in order of preference e.g 1st= 10, 2nd = 8 and so on.

The options are being copied from a table into the form/poll however I am unsure as to how the actual form should be laid out e.g radio buttons, drop down.

$query = "INSERT INTO songsoftheweek
            SELECT * FROM tracks
            ORDER BY RAND() 
            LIMIT 5"; 
$dbResult=mysql_query($query,$db);    

As the songs generated are random, I'm unsure how to design the form around this.

Any guidance would be much appreciated.

Answer 1

Form:

If your poll is going to contain between 5-10 songs, radio buttons could be a good choice. Each song title should have a unique ID in the database, and each vote should include registration of song title ID, date/time (as I understand you want to sort the votes on weeks), and IP-address (the latter to avoid multiple votes).

Database:

I'd make two tables, one for the songs, and another for the votes:

CREATE TABLE `Songs` (
  `SongId` INT NOT NULL AUTO_INCREMENT,
  `SongTitle` VARCHAR(255) NULL,
  PRIMARY KEY (`SongId`));

CREATE TABLE `Votes` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `SongId` INT NULL,
  `Datetime` DATETIME NULL,
  `IP` VARCHAR(45) NULL,
  PRIMARY KEY (`Id`));