Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Need building CMS - Using form data to create multiple INSERTs

9:10 am on Dec 27, 2013 (gmt 0)

Junior Member

10+ Year Member

joined:Sept 20, 2007
posts: 69
votes: 0

I am building a CMS to enter sports scores of 'sets' which are part of a larger 'match'.

Here is a screenshot of a crude form that I built just so you get the idea:


I selected the list of team ids & names into the dropdowns. When a match date and venue are entered, a new entry is inserted into the table "matches". I then use $pdo->lastInsertId() to get the id of the row created so I can insert all the set scores using that match id. I should point out that I am using the same form for adding/editing/deleting match scores.

However, I'm not sure how to properly built the form with the <input> elements for the scores, so that I can easily retrieve the data from them and use the values in my SQL statements. I know arrays are somehow part of the answer, but I'm having a hard time coming up with the implementation. It would seem to me that I need to create an associative array that contains the two team ids, the scores and the set numbers. Again, no idea how to do that.

Right now the scores input part of the form looks like this, which I know is using the wrong techniques:

<legend>Team 2 Scores</legend>
<input class="score" type="number" name="set_number1_team2" id="set_number1_team2" value="<?php $setNumber1Team2 ?>"></input>
<input class="score" type="number" name="set_number2_team2" id="set_number2_team2" value="<?php $setNumber2Team2 ?>"></input>
<input class="score" type="number" name="set_number3_team2" id="set_number3_team2" value="<?php $setNumber3Team2 ?>"></input>
<input class="score" type="number" name="set_number4_team2" id="set_number4_team2" value="<?php $setNumber4Team2 ?>"></input>
<input class="score" type="number" name="set_number5_team2" id="set_number5_team2" value="<?php $setNumber5Team2 ?>"></input>
<input class="score" type="number" name="set_number6_team2" id="set_number6_team2" value="<?php $setNumber6Team2 ?>"></input>
<input class="score" type="number" name="set_number7_team2" id="set_number7_team2" value="<?php $setNumber7Team2 ?>"></input>

This is the SQL statement that I will need to run up to 14 times (once for each team's set score in the match):

$sql = 'INSERT INTO set_score(match_id, set_number, set_score, team_id) VALUES (:match_id, :set_number, :set_score, :team_id)';
$s = $pdo->prepare($sql);
$s->bindValue(':match_id', $newMatchId);
// Not sure how to bind to the other placeholder values

Here are the relevant table structures that are utilized in this script for reference:

CREATE TABLE `matches` (
`match_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`venue_id` tinyint(3) unsigned NOT NULL,
`match_date` date NOT NULL)

CREATE TABLE `set_score` (
`set_score_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`set_number` tinyint(3) unsigned NOT NULL,
`match_id` int(10) unsigned NOT NULL,
`set_score` tinyint(3) unsigned DEFAULT NULL,
`team_id` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`set_score_id`),
KEY `fk_match_id_idx` (`match_id`),
CONSTRAINT `fk_match_id` FOREIGN KEY (`match_id`) REFERENCES `matches` (`match_id`)

Any help you can give would be great!