Radio button array for football tipping app

[damo87]

I am creating a football tipping application, and I am stuck on the page where the user selects their tips (OK, so I havn't gotten very far!)

 

For a specified round of the football season, I need the form to pair up the two teams who are playing, show details of the match and allow the user to select one of the teams with a radio button. For example:

 

Home team | (radio select) | vs | (radio select) | Away team | Venue | Date

 

There will be 8 lines of the above, because there are 8 matches in a round.

 

I also need the form to pre-load with their selections.

 

The three tables at work here are called teams, games and selections (create table code is below).  The way I decided to attempt this was to only pass the teamid in the array to be processed and updated. I toyed with the idea of passing the gameid as well, but I figured this actually wasnt necessary.

 

I've been working on the code below, and although it is imcomplete, and could be totally wrong, it might show you what I am trying to acheive.  I usually don't like to post such incomplete code to a forum such as this, but in this case I'm completely stuck.  Even if someone can just point me in the right direction that would be great.

 

/*process the form - gets the team ids from the posted form and runs sql for each */

if($_POST['teams']) {
      foreach((array)$_POST['teams'] as $team) {
        $query = "Insert into selections (userid, round, team) Values ($user, '1', $team)";
        $result = mysql_query($query) or die("Query failed : " . mysql_error());
      }

?>
<form method="post" action="round1.php">
<?


  $result = mysql_query("select * from games where games.round=1");
  $myrow = mysql_fetch_array($result);

?>
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<? do {
$home_team_id = $myrow["hometeam"];
$away_team_id = $myrow["awayteam"];

/*assign the actual team name - not sure how to handle this, should be combined with first query above?*/


/* find all records for home teams that have already been selected*/

$checkedresults_home = mysql_query("Select * from selections where userid='$user' and team=$home_team_id and round=1");
$num_home = mysql_num_rows($checkedresults_home);


/* if a home team has already been checked append "checked" to the end of the input tag in the html */

if ($num_home==1) {
$home_team_id = str_replace($home_team_id, "$home_team_id checked", $home_team_id);
}

/* find all records for away teams that have already been selected*/

$checkedresults_away = mysql_query("Select * from selections where userid='$user' and team=$away_team_id and round=1");
$num_away = mysql_num_rows($checkedresults_away);


/* if an away team has already been checked append "checked" to the end of the input tag in the html */

if ($num_away==1) {
$away_team_id = str_replace($away_team_id, "$away_team_id checked", $away_team_id);
}

/* the form with radio buttons - incomplete! */

printf ("<input type=\"radio\" name=\"teams[]\" value=...................;
} while ($myrow = mysql_fetch_array($result));
?>
</td>
</tr>
</table>
<input type="hidden" name="uid" value="<? echo $user; ?>"><br>
<input type="submit" name="Submit" value="Submit">

</form>

 

 

The database structure:

CREATE TABLE `games` (

`id` int(11) NOT NULL auto_increment,

`round` int(11) NOT NULL,

`hometeam` int(11) NOT NULL,

`awayteam` int(11) NOT NULL,

`homescore` int(11) NOT NULL,

`awayscore` int(11) NOT NULL,

`homegoals` int(11) NOT NULL,

`awaygoals` int(11) NOT NULL,

`homepoints` int(11) NOT NULL,

`awaypoints` int(11) NOT NULL,

`venue` text NOT NULL,

`datetime` datetime NOT NULL,

PRIMARY KEY  (`id`),

KEY `round` (`round`),

KEY `FK_hometeam` (`hometeam`),

KEY `FK_awayteam` (`awayteam`),

CONSTRAINT `FK_awayteam` FOREIGN KEY (`awayteam`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `FK_games` FOREIGN KEY (`round`) REFERENCES `rounds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `FK_hometeam` FOREIGN KEY (`hometeam`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=utf8

 

CREATE TABLE `selections` (

`id` int(11) NOT NULL auto_increment,

`bookfaceuserid` bigint(20) NOT NULL,

`game` int(11) NOT NULL,

`team` int(11) NOT NULL,

PRIMARY KEY  (`id`),

KEY `FK_selectuser` (`bookfaceuserid`),

KEY `FK_game` (`game`),

KEY `FK_team` (`team`),

CONSTRAINT `FK_team` FOREIGN KEY (`team`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `selections_ibfk_1` FOREIGN KEY (`game`) REFERENCES `games` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `selections_ibfk_2` FOREIGN KEY (`bookfaceuserid`) REFERENCES `user` (`bookfaceuserid`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

CREATE TABLE `teams` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(20) NOT NULL,

PRIMARY KEY  (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

[damo87]

I've marked this as solved because I have found more success with a different method.