homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Need building CMS - Using form data to create multiple INSERTs

5+ Year Member

Msg#: 4633816 posted 9:10 am on Dec 27, 2013 (gmt 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!


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved