Problem! Multiple Simultaneous Database Write & Id

Cody

Hi there,

I'm writing a script where users will first sign up a team ( team name, group ) and then they'll have to enter 5 to 8 players ( player name, age, .. ).

How would i do it so the players get associated with the right team id on signup. I have two tables: Teams and Players, and i have assigned TeamID field to Players table. Now, since everything is done on the same page ( or two pages ) and team names and group can be the same, how would i associate the right players with the right team id ( note that two people can be signing up at the same time ).

The problem also arises with the 8 fields ( for 8 players ), which are getting written to the same columns at the same time. How would i do that ?

Thank you guys 🙂

ahundiak

First step would be to go through some of the excellent tutorials and articles on the basics of relational databases. There are several avalable from phpbuilder. A little study up front will save you tons of time downstream.

But basically your team table will probably have an autoincrement field for team_id. This field will automatically be set each time you insert a new record. It's value can then be retrieved with the poorly named mysql_insert_id() function.

So basically:
mysql_query("INSERT INTO team ...
$teamID = mysql_insert_id();
mysql_query("INSERT INTO player ... $teamID ...).

You do not need to worry about multiple inserts going on at the same time. mysql_insert_id is connection specific so you always get the correct id.

Cody

Thank you for your help ahundiak. 🙂 Yeah mysql_insert_id() is a bit poorly named hehe.

One more thing - is there a shorter way than doing it like this:

mysql_query("INSERT INTO team ...
$teamID = mysql_insert_id(); // Get the ID generated from the previous INSERT operation

$Player1_name = $POST['Player1_name'];
$Player2_name = $POST['Player2_name'];
//.......
mysql_query("INSERT INTO players SET player_name = '$Player1_name', TeamID = '$teamID' ....
mysql_query("INSERT INTO players SET player_name = '$Player2_name', TeamID = '$teamID'....

and one more if you dont mind 🙂

how could i limit the entires of players to min 5 (and max 8 )

Thank you 🙂

ahundiak

I generally use arrays when dealing with rows of data.

This results in an array being posted
foreach($_POST['player_names'] as $playerName) {
/ Build the insert /
}

Of course with multiple fields you will want to use an index to access the posted arrays.

I'd suggest not using SET while inserting. Using SET is a mysql non-standard extension to SQL. Use
INSERT INTO table VALUES () syntax.

This also allows you to insert multiple records with one statement.

As far as limiting the number of players, I think you would just have to count them. I don't know of any standard constraint you could apply. I suppose you could create a stored procedure but that's beyond the scope of php.

Cody

Thank you ahundiak,

but since i have alot of variables to be inserted at the same time for each player ( player_name, player_lastname, teamID,.. ), how would i do that with foreach() ?

Cody

liquorvicar

as ahundiak said, use an index to build your inserts:

foreach($_POST['player_names'] as $key=>$playerName) {
$lastName = $_POST['last_names'][$key];
$teamID = $_POST['team_IDs'][$key];
/* Build the insert */
}

Cody

Thank you for explaining liquorvicar 🙂

I have one more question:

For checking first 3 player entries ( out of 8 ), since i want them to fill in at least 3 players, without using foreach() i did the check, if the specific fields were left blank, like this:

if ($First_name1=="" || $First_name2=="" || $First_name3=="" || $Last_name1=="" || $Last_name2=="" || $Last_name3=="")
{ echo"You didn't fill in all the required fields."; }
else
{
// do the insert
}

How could i check, if specific fields were left blank while using foreach() ?

Thank you again 🙂

ahundiak

Build the SQL statement dynamically then only submit it if you have enough players.

$sql = "INSERT INTO player VALUES(";
$playerCnt = 0;
foreach($players as $player) {
  if (is_valid($player)) {
    $sql .= player info;  // Cat  player info
    $playerCnt++;
  }
}
if ($playerCnt >= 3) {
  mysql_query($sql);
}

You could also just do two foreach loops. Once to check then once more to insert.

Cody

thanks again,

btw what did you mean by this line:

$sql .= player info; // Cat player info

ahundiak

Cat is short for concatenate. The .= adds whatever is on the right to whatever is already in the variable. It would actually be something like:
$sql .= "(NULL,$teamID,'$playerName'),";

Cody

ah, i wasnt sure what you ment by player info.

The only thing bothering me now is ( from your posts ) the
$sql = "INSERT INTO player VALUES(";
and
$sql .= "(NULL,$teamID,'$playerName'),";

wouldnt that end up as
INSERT INTO player VALUES((NULL,$teamID,'$playerName'),

? There are two brackets in the begining and a comma at the end ?

Also, if i was to check if all other required fields as well besides playerName are not left blank, where would i put it?

I appreciate the help. The way you do it, is a way i've never before done myself, that's why i have to ask, thank you 🙂

liquorvicar

that was probbably a typo. you want to end up with an SQL string like this

INSERT INTO table (team_id,player_name) VALUES (1,'Joe') (1,'Mark') (1,'John') (2,'Fred') etc

Cody

ahh, crystal clear now, thank you liquorvicar 🙂

And one last thing, since i've never done it like this before, where would be the best way to check if if all other required fields as well besides playerName are not left blank, where and how would i put it?

liquorvicar

at the same place you validate player name.
go back to ahundiak's code above and where he does the foreach loop, in there put all your input validation.

Cody

Alright, this is what i came up with; tell me if everything is optimal or if anything needs improovement or correction 🙂

$sql = "INSERT INTO players (firstName,lastName,teamID) VALUES ";
$playerCount = 0;
foreach($_POST['first_names'] as $firstName) {

$lastName = $_POST['last_names'][$firstName];
$teamID = $teamID[$firstName];

if (is_valid($firstName) && is_valid($lastName) && is_valid($teamID)) {
$sql .= "('$firstName','$lastName','$teamID') ";
$playerCount++;
}

}
if ($playerCount >= 3) {
mysql_query($sql);
}

liquorvicar

please use the board's PHP tags. they make it so much easier for people to read your code.

anyway, your foreach loop is slightly wrong. try this

$sql = "INSERT INTO players (firstName,lastName,teamID) VALUES ";
$playerCount = 0;
foreach($_POST['first_names'] as $key=>$firstName) {

$lastName = $_POST['last_names'][$key];
$teamID = $_POST['teamID'][$key];

if (is_valid($firstName) && is_valid($lastName) && is_valid($teamID)) {
$sql .= "('$firstName','$lastName','$teamID') ";
$playerCount++;
}

}
if ($playerCount >= 3) {
mysql_query($sql);
}

and if i were you, i'd have some sort of input validation in there. even if it's just addslashes() for the name fields and intval for the teamID field

Cody

Thank you liquorvicar. Why would you use an intval(), and what other validations would you recommend ?

Thank you both for your help, liquorvicar and ahundiak 🙂

liquorvicar

http://uk.php.net/intval

it converts string to integers. if you know the input data is always an integer, it helps clean up the data and avoid any nasty surprises.

Cody

ic.

well the above code gives me an error :/
what's wrong with it ?

and i have another question 🙂

How would i edit the data ( the 8 players ) in the database using foreach() ?

TY 🙂