Updating more than one data table with a form

[Welshsteve]

Hi everyone. Still getting my head around MySQL and PHP.

I run a snooker league website, and with the league now finished for the season, I want to start adding/re-developing sections for the site for next season.

Currently I have a HTML form for submitting match and frame score results to me (http://www.ldbsa.co.uk/s_submitscore.php). This form when submitted gets emailed to me using php's mail function.

What I would prefer is to have this form add data into the MySQL database behind the scenes. However, this is made difficult by the fact that the form would need to update more than one data table. It would need to update the following.

1. The score of the match would need to update the team records in the "LeagueTable" table.
* 1 would need to be added to the "Played" field
* 1 would need to be added to the "Won" field if the team's score is higher than the opponents, or
* 1 would need to be added to the "Lost" field if not

2. The frames players and scores records would need to update a table called "Matches".

3. Each player entered and the frame points for and against would need to update the relevant players averages record in a table called "PlayerStats"

4. The breaks entered would need to update a table called "Breaks".

I'm not sure if this is possible is it?

The only way I can think of doing it is as follows:

1. When the form is filled out and submitted, all the data is entered into a temporary table, either one I truncate every so often, or one created dynamically by the script.

2. Then the script can create a "data store" of all this data by running a SQL statement such as SELECT * FROM MatchTemp1.

3. Then a series of separate SQLs could be run to update the individual tables using data from the "data store".

4. Drop the temp table (if dynamically created).


See, I know the theory, but how to go about it I don't know. http://www.htmlforums.com/images/smilies/FrownyBulb.gifhttp://www.htmlforums.com/images/smilies/ConfusedBulb.gif

I am going to search Google to see if I can get an answer, but if anyone here has a quick example of how to do this, I would appreciate it greatly.


EDIT: Just realised this should have been posted in the MySQL forum. Sorry about that.

[ptmuldoon]

Do a little search on form submission. This is easier than you may think.

In your form, you'll want to pass that data to a file that will process all of the submitted data using $_POST . Some of that data may either be a "hidden" or inputed by the user.

In your form, you'll need items such as:

<input name="played" type="hidden" size="5" maxlength="5" value="played"/></p>
<input name="won" type="hidden" size="5" maxlength="5" value="loss"/></p>
<input name="loss" type="hidden" size="5" maxlength="5" value="loss"/></p>

And the script your data is passed to will look for the $_POST['played'] information and process it according to your script, sql queries.

Hope that made some sense. Others can likely explain it better.

[Welshsteve]

Hi. Thanks for the reply.

I've started to devise something.

Here's my HTML form.


<form method="post" action="scripts/dosubmitscore.php">
Division: <select name="division" id="division">
<option value="Premier">Premier Division</option>
<option value="A">A Division</option>
</select>
Week: <select name="week" id="week">
<option value="1"> Week 1 - 13 September</option>
<option value="2"> Week 2 - 20 September</option>
<option value="3"> etc. </option>
</select>
<table style="border:0;">
<tr>
<th>HOME TEAM</th>
<th>&nbsp;</th>
<th>AWAY TEAM</th>
<th>&nbsp;</th>
</tr>
<tr>
<td colspan="2">
<select name="teamhome" id="teamhome">
<option value="Team 1">Team 1</option>
<option value="Team 2">Team 2</option>
<option value="Team 3">etc</option>
</select>
</td>
<td colspan="2">
<select name="teamaway" id="teamaway">
<option value="Team 1">Team 1</option>
<option value="Team 2">Team 2</option>
<option value="Team 3">etc</option>
</select>
</td>
</tr>
<tr>
<th>PLAYER NAME</th>
<th>SCORE</th>
<th>PLAYER NAME</th>
<th>SCORE</th>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="player1home" id="player1home" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score1home" id="score1home" /></td>
<td><input type="text" class="fieldtxt" size="28" name="player1away" id="player1away" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score1away" id="score1away" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="player2home" id="player2home" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score2home" id="score2home" /></td>
<td><input type="text" class="fieldtxt" size="28" name="player2away" id="player2away" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score2away" id="score2away" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="player3home" id="player3home" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score3home" id="score3home" /></td>
<td><input type="text" class="fieldtxt" size="28" name="player3away" id="player3away" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score3away" id="score3away" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="player4home" id="player4home" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score4home" id="score4home" /></td>
<td><input type="text" class="fieldtxt" size="28" name="player4away" id="player4away" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score4away" id="score4away" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="player5home" id="player5home" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score5home" id="score5home" /></td>
<td><input type="text" class="fieldtxt" size="28" name="player5away" id="player5away" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score5away" id="score5away" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="player6home" id="player6home" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score6home" id="score6home" /></td>
<td><input type="text" class="fieldtxt" size="28" name="player6away" id="player6away" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score6away" id="score6away" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="player7home" id="player7home" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score7home" id="score7home" /></td>
<td><input type="text" class="fieldtxt" size="28" name="player7away" id="player7away" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="score7away" id="score7away" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td><input type="text" class="fieldtxtsml" size="3" name="scorehome" id="scorehome" /></td>
<td>&nbsp;</td>
<td><input type="text" class="fieldtxtsml" size="3" name="scoreaway" id="scoreaway" /></td>
</tr>
<tr>
<th>PLAYER NAME</th>
<th>BREAK</th>
<th>PLAYER NAME</th>
<th>BREAK</th>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="brk1playerh" id="brk1playerh" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk1scoreh" id="brk1scoreh" /></td>
<td><input type="text" class="fieldtxt" size="28" name="brk1playera" id="brk1playera" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk1scorea" id="brk1scorea" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="brk2playerh" id="brk2playerh" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk2scoreh" id="brk2scoreh" /></td>
<td><input type="text" class="fieldtxt" size="28" name="brk2playera" id="brk2playera" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk2scorea" id="brk2scorea" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="brk3playerh" id="brk3playerh" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk3scoreh" id="brk3scoreh" /></td>
<td><input type="text" class="fieldtxt" size="28" name="brk3playera" id="brk3playera" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk3scorea" id="brk3scorea" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="brk4playerh" id="brk4playerh" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk4scoreh" id="brk4scoreh" /></td>
<td><input type="text" class="fieldtxt" size="28" name="brk4playera" id="brk4playera" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk4scorea" id="brk4scorea" /></td>
</tr>
<tr>
<td><input type="text" class="fieldtxt" size="28" name="brk5playerh" id="brk5playerh" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk5scoreh" id="brk5scoreh" /></td>
<td><input type="text" class="fieldtxt" size="28" name="brk5playera" id="brk5playera" /></td>
<td><input type="text" class="fieldtxtsml" size="3" name="brk5scorea" id="brk5scorea" /></td>
</tr>
<tr>
<th>PTS DEDUCTED</th>
<th><input type="text" class="fieldtxtsml" size="3" name="deductedhome" id="deductedhome" /></th>
<th>PTS DEDUCTED</th>
<th><input type="text" class="fieldtxtsml" size="3" name="deductedaway" id="deductedaway" /></th>
</tr>
</table>
<input type="submit" name="btnSubmit" id="btnSubmit" class="btn" value="Submit Score" />
<input type="reset" name="btnReset" id="btnReset" class="btn" value="Reset Form" />
</form>
And here's the PHP for inserting into a temp table

<?php
// Make a MySQL Connection
mysql_connect("myhostname", "myusername", "mypassword") or die(mysql_error());
mysql_select_db("mydatabasename") or die(mysql_error());

// Insert data into temp table

mysql_query("INSERT INTO TempMatches (Week, Division, TeamHome, TeamAway, Player1Home, Score1Home, Player1Away, Score1Away, Player2Home, Score2Home, Player2Away, Score2Away, Player3Home, Score3Home, Player3Away, Score3Away, Player4Home, Score4Home, Player4Away, Score4Away, Player5Home, Score5Home, Player5Away, Score5Away, Player6Home, Score6Home, Player6Away, Score6Away, Player7Home, Score7Home, Player7Away, Score7Away, ScoreHome, ScoreAway, Brk1PlayerH, Brk1ScoreH, Brk1PlayerA, Brk1ScoreA, Brk2PlayerH, Brk2ScoreH, Brk2PlayerA, Brk2ScoreA, Brk3PlayerH, Brk3ScoreH, Brk3PlayerA, Brk3ScoreA, Brk4PlayerH, Brk4ScoreH, Brk4PlayerA, Brk4ScoreA, Brk5PlayerH, Brk5ScoreH, Brk5PlayerA, Brk5ScoreA, DeductedHome, DeductedAway) VALUES ('$_POST[week]','$_POST[division]','$_POST[teamhome]','$_POST[teamaway]','$_POST[player1home]','$_POST[score1home]','$_POST[player1away]','$_POST[score1away]','$_POST[player2home]','$_POST[score2home]','$_POST[player2away]','$_POST[score2away]','$_POST[player3home]','$_POST[score3home]','$_POST[player3away]','$_POST[score3away]','$_POST[player4home]','$_POST[score4home]','$_POST[player4away]','$_POST[score4away]','$_POST[player5home]','$_POST[score5home]','$_POST[player5away]','$_POST[score5away]','$_POST[player6home]','$_POST[score6home]','$_POST[player6away]','$_POST[score6away]','$_POST[player7home]','$_POST[score7home]','$_POST[player7away]','$_POST[score7away]','$_POST[scorehome]','$_POST[scoreaway]','$_POST[brk1playerh]','$_POST[brk1scoreh]','$_POST[brk1playera]','$_POST[brk1scorea]','$_POST[brk2playerh]','$_POST[brk2scoreh]','$_POST[brk2playera]','$_POST[brk2scorea]','$_POST[brk3playerh]','$_POST[brk3scoreh]','$_POST[brk3playera]','$_POST[brk3scorea]','$_POST[brk4playerh]','$_POST[brk4scoreh]','$_POST[brk4playera]','$_POST[brk4scorea]','$_POST[brk5playerh]','$_POST[brk5scoreh]','$_POST[brk5playera]','$_POST[brk5scorea]','$_POST[deductedhome]','$_POST[deductedaway]')")
or die(mysql_error());

It's how to increment to played, won and lost fields in the league table that I'm stuck with as well. Not sure how you'd do this, as it'll require an IF statement for checking whether a team has won or lost.

I'll continue my research.

[tomws]

First, some unsolicited advice. Trying to navigate through that near-infinite length query can be cumbersome. Eggheads like parsing those as a skill exercise, but some of us use a string and add in some newlines to make it easy to read.

<?php
...
$query = "
INSERT INTO TempMatches
(
Week, Division,
TeamHome, TeamAway,
Player1Home, Score1Home, Player1Away, Score1Away,
Player2Home, Score2Home, Player2Away, Score2Away,
Player3Home, Score3Home, Player3Away, Score3Away,
Player4Home, Score4Home, Player4Away, Score4Away,
Player5Home, Score5Home, Player5Away, Score5Away,
Player6Home, Score6Home, Player6Away, Score6Away,
Player7Home, Score7Home, Player7Away, Score7Away,
ScoreHome, ScoreAway,
Brk1PlayerH, Brk1ScoreH, Brk1PlayerA, Brk1ScoreA,
Brk2PlayerH, Brk2ScoreH, Brk2PlayerA, Brk2ScoreA,
Brk3PlayerH, Brk3ScoreH, Brk3PlayerA, Brk3ScoreA,
Brk4PlayerH, Brk4ScoreH, Brk4PlayerA, Brk4ScoreA,
Brk5PlayerH, Brk5ScoreH, Brk5PlayerA, Brk5ScoreA,
DeductedHome, DeductedAway
)
VALUES
(
'$_POST[week]', '$_POST[division]', '$_POST[teamhome]', '$_POST[teamaway]',
'$_POST[player1home]', '$_POST[score1home]', '$_POST[player1away]', ' $_POST[score1away]',
'$_POST[player2home]', '$_POST[score2home]', '$_POST[player2away]', '$_POST[score2away]',
'$_POST[player3home]', '$_POST[score3home]', '$_POST[player3away]', '$_POST[score3away]',
'$_POST[player4home]', '$_POST[score4home]', '$_POST[player4away]', '$_POST[score4away]',
'$_POST[player5home]', '$_POST[score5home]', '$_POST[player5away]', '$_POST[score5away]',
'$_POST[player6home]', '$_POST[score6home]', '$_POST[player6away]', '$_POST[score6away]',
'$_POST[player7home]', '$_POST[score7home]', '$_POST[player7away]', '$_POST[score7away]',
'$_POST[scorehome]', '$_POST[scoreaway]',
'$_POST[brk1playerh]', '$_POST[brk1scoreh]', '$_POST[brk1playera]', '$_POST[brk1scorea]',
'$_POST[brk2playerh]', '$_POST[brk2scoreh]', '$_POST[brk2playera]', '$_POST[brk2scorea]',
'$_POST[brk3playerh]', '$_POST[brk3scoreh]', '$_POST[brk3playera]', '$_POST[brk3scorea]',
'$_POST[brk4playerh]', '$_POST[brk4scoreh]', '$_POST[brk4playera]', '$_POST[brk4scorea]',
'$_POST[brk5playerh]', '$_POST[brk5scoreh]', '$_POST[brk5playera]', '$_POST[brk5scorea]',
'$_POST[deductedhome]','$_POST[deductedaway]')
)
";

$result = mysql_query($query);



Attractive, no?

Also, you shouldn't insert data directly from $_POST as the data could be dirty. Validate and sanitise before before putting into your db. Google "php mysql_real_escape_string" and you should see a link going to php.net that has a fair treatment of why this is important.


Now, to the original point of your post, why are you thinking of using a temp table? I'm confused as to why it's considered necessary. If you assign you post vars to variables in your php, then you can build as many different queries (SELECT, INSERT, UPDATE, etc) as you want.

Something like:

$someVar = $_POST['someVar'];
$someID = $_POST['someID'];
$someScore1 = $_POST['someScore1'];
$someScore2 = $_POST['someScore2'];

$query = "
INSERT INTO `PlayerStats` (`someVar `)
VALUES ( '".mysql_real_escape_string($someVar )."' )
";
$result = mysql_query($query);
// do some error checks here


$query = "
UPDATE `Matches`
SET `someVar`='".mysql_real_escape_string($someVar )."'
WHERE `ID`='".mysql_real_escape_string($someID)."'

";
$result = mysql_query($query);
// do some error checks here


$query = "
UPDATE `PlayerStats`
SET `Won`=`Won`+'".($someScore1 > $someScore2 ? "1" : "0" )."',
`Lost`=`Lost`+'".($someScore1 < $someScore2 ? "1" : "0" )."'
WHERE `someVar`='".mysql_real_escape_string($someVar )."'
";
$result = mysql_query($query);
// do some error checks here


Obviously, these are just skeletons, but maybe it gives an idea. ...unless I'm totally misunderstanding what you're asking.

[Andrew Johnson]

Normalize Your Database@~

[Welshsteve]

First, some unsolicited advice. Trying to navigate through that near-infinite length query can be cumbersome. Eggheads like parsing those as a skill exercise, but some of us use a string and add in some newlines to make it easy to read.

<?php
...
$query = "
INSERT INTO TempMatches
(
Week, Division,
TeamHome, TeamAway,
Player1Home, Score1Home, Player1Away, Score1Away,
Player2Home, Score2Home, Player2Away, Score2Away,
Player3Home, Score3Home, Player3Away, Score3Away,
Player4Home, Score4Home, Player4Away, Score4Away,
Player5Home, Score5Home, Player5Away, Score5Away,
Player6Home, Score6Home, Player6Away, Score6Away,
Player7Home, Score7Home, Player7Away, Score7Away,
ScoreHome, ScoreAway,
Brk1PlayerH, Brk1ScoreH, Brk1PlayerA, Brk1ScoreA,
Brk2PlayerH, Brk2ScoreH, Brk2PlayerA, Brk2ScoreA,
Brk3PlayerH, Brk3ScoreH, Brk3PlayerA, Brk3ScoreA,
Brk4PlayerH, Brk4ScoreH, Brk4PlayerA, Brk4ScoreA,
Brk5PlayerH, Brk5ScoreH, Brk5PlayerA, Brk5ScoreA,
DeductedHome, DeductedAway
)
VALUES
(
'$_POST[week]', '$_POST[division]', '$_POST[teamhome]', '$_POST[teamaway]',
'$_POST[player1home]', '$_POST[score1home]', '$_POST[player1away]', ' $_POST[score1away]',
'$_POST[player2home]', '$_POST[score2home]', '$_POST[player2away]', '$_POST[score2away]',
'$_POST[player3home]', '$_POST[score3home]', '$_POST[player3away]', '$_POST[score3away]',
'$_POST[player4home]', '$_POST[score4home]', '$_POST[player4away]', '$_POST[score4away]',
'$_POST[player5home]', '$_POST[score5home]', '$_POST[player5away]', '$_POST[score5away]',
'$_POST[player6home]', '$_POST[score6home]', '$_POST[player6away]', '$_POST[score6away]',
'$_POST[player7home]', '$_POST[score7home]', '$_POST[player7away]', '$_POST[score7away]',
'$_POST[scorehome]', '$_POST[scoreaway]',
'$_POST[brk1playerh]', '$_POST[brk1scoreh]', '$_POST[brk1playera]', '$_POST[brk1scorea]',
'$_POST[brk2playerh]', '$_POST[brk2scoreh]', '$_POST[brk2playera]', '$_POST[brk2scorea]',
'$_POST[brk3playerh]', '$_POST[brk3scoreh]', '$_POST[brk3playera]', '$_POST[brk3scorea]',
'$_POST[brk4playerh]', '$_POST[brk4scoreh]', '$_POST[brk4playera]', '$_POST[brk4scorea]',
'$_POST[brk5playerh]', '$_POST[brk5scoreh]', '$_POST[brk5playera]', '$_POST[brk5scorea]',
'$_POST[deductedhome]','$_POST[deductedaway]')
)
";

$result = mysql_query($query);

Attractive, no?

Also, you shouldn't insert data directly from $_POST as the data could be dirty. Validate and sanitise before before putting into your db. Google "php mysql_real_escape_string" and you should see a link going to php.net that has a fair treatment of why this is important.


Now, to the original point of your post, why are you thinking of using a temp table? I'm confused as to why it's considered necessary. If you assign you post vars to variables in your php, then you can build as many different queries (SELECT, INSERT, UPDATE, etc) as you want.

Something like:

$someVar = $_POST['someVar'];
$someID = $_POST['someID'];
$someScore1 = $_POST['someScore1'];
$someScore2 = $_POST['someScore2'];

$query = "
INSERT INTO `PlayerStats` (`someVar `)
VALUES ( '".mysql_real_escape_string($someVar )."' )
";
$result = mysql_query($query);
// do some error checks here


$query = "
UPDATE `Matches`
SET `someVar`='".mysql_real_escape_string($someVar )."'
WHERE `ID`='".mysql_real_escape_string($someID)."'

";
$result = mysql_query($query);
// do some error checks here


$query = "
UPDATE `PlayerStats`
SET `Won`=`Won`+'".($someScore1 > $someScore2 ? "1" : "0" )."',
`Lost`=`Lost`+'".($someScore1 < $someScore2 ? "1" : "0" )."'
WHERE `someVar`='".mysql_real_escape_string($someVar )."'
";
$result = mysql_query($query);
// do some error checks here
Obviously, these are just skeletons, but maybe it gives an idea. ...unless I'm totally misunderstanding what you're asking.



Thanks. That's some good advice, which I will take on board and take a good look at over the weekend. Many thanks.