MySQL write multiple rows at once

madmarco

Hi @ all

I'm writing a script for entering game statistics for a hockey team. There are three tables:

[FONT=courier new]

tbl_game_records

rec_id
game_id
player_id
tore
assist
strafmin

tbl_games

game_id
saison

tbl_players

player_id
name
[/FONT]

[FONT=arial]

What i try to do is following: I want someone to be able to edit statistic details for each game and for every player. I expect it to work like that if you click on a link "edit game statistics" a script is called (with ?game_id=2019 e.g.) which grabs all data from the table tbl_game_records where game_id is like the variable game_id. I've managed to achieve a display of the list with following sql-query:

SELECT G.*,R.tore,R.assist,R.strafmin,P.name FROM tbl_games G LEFT OUTER JOIN tbl_game_records R ON (G.game_id = R.game_id) RIGHT OUTER JOIN tbl_players P ON (R.player_id = P.player_id) WHERE G.game_id = $game_id

That works quite good and I can display a list wich contains rows with every player and their corresponding records. But I want to create a Form which does not only display data, but also creates one big form which 1. already contains entered data in input fields and 2. allows me to update and/or insert all rows at once. The Form should looke something like this:
[/FONT]

[FONT=courier new]

.
.
.

[/FONT]
[FONT=arial]

I've done inserts through a form for just one record a lot of times, but i haven't got any idea how to name my multiple input fields, because i always referenced this with their corresponding name.

Any suggestions, any tutorials, whatever?

Hope that somebody understands my isse, don't worry, i'm confused too :rolleyes:
[/FONT]

jweissig

Hey,

You can't create one insert statement that will insert data into multiple tables. So you are going to have to create some type of a loop that will run through all the data on the page and create insert statements for each table. I should also point out that INSERT and UPDATE are very different and I would split these out into different pages.

In the following example I will show you how you can display the data and then update the said data using HTML. You should note that the following example probably will not run and I just wanted you give you an idea.

Here is the page that will display the data.

<html>
<head>
<title>Update Player Stats</title>
</head>
<body>
<p>player stats</p>
<form name="form1" method="post" action="">
<input type="hidden" name="id" value="something">
<table width="1%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>name</td>
    <td>tore?</td>
    <td>assist</td>
    <td>strafmin</td>
  </tr>
  <tr>
    <td><input name="textfield" type="text" size="15"></td>
    <td><input name="textfield" type="text" size="3"></td>
    <td><input name="textfield" type="text" size="3"></td>
    <td><input name="textfield" type="text" size="4"></td>
  </tr>
</table>
</form>
</body>
</html>

The above basically would allow you to UPDATE the data. You would have to add some code that would allow you to connect to that database and then input the data in the page. With something like value="$name" in the <input> form.

You will want to include something like a hidden form value that will allow you to post the information to a script that will say foreach (hidden form key) {
Update tbl_game_records set tore = "$tore", assist = "$assist", strafmin = "$etc" where the hidden form value = something;

Hope this helps,
- Justin