0

I am trying to insert multiple rows of data from my form into my sql database. I have been looking up arrays and been trying to apply what I have found but to no avail. I am beginner to programming never took any formal classes, been learning snipits of code as I need, so my fundamentals are nonexistent.

PlayerID is not auto-increment but an actually number provided by the user.

Here is my form code

<table border="1">
<tr>
<td align="center">Form Input Employees Data</td>
</tr>
<tr>
<td>
  <table>
    <form method="post" action="/wp/wp-content/plugins/FormSubmit/FSdotaArray.php">
      <tr>
      <td></td>
        <td><b>Captain</b></td>
      </tr>
      <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team" size="40">
      </td>
    </tr>
    <tr>
      <td>Game</td>
      <td><select name="game">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>          
      <td></td>
       <td><b>Player 2</b></td>
      </tr>
    <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team" size="40">
      </td>
    </tr>
<tr>
      <td>Game</td>
      <td><select name="game">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td></td>
      <td align="right"><input type="submit" name="submit" value="Sent"></td>
    </tr>
    </table>
  </td>
</tr>
</table>
</body>

Here is my php code, The header is needed because I am using wordpress and running the code as a plugin. 

<?php
/*
Plugin Name: FSdota 
Description: submit form data into sql database
Author: F. May
Version: 1.0
*/

//inserting data order
$con=mysqli_connect("XXXXXX","XXXXXXXX","XXXXXXXX","XXXXXXXX");
 if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 

mysqli_query($con,"XXXXXXXXXX");

$row_data = "($playerid,$firstname, $lastname, $position,$team,$Game)";

if (is_array($row_data));
{
    foreach($_POST['playerid'] as $row=>$playerid)  {

            $playerid = mysqli_real_escape_string('playerid');
            $firstname = mysqli_real_escape_string($con,($_POST['firstname'][$row]));
            $lastname = mysqli_real_escape_string($con,($_POST['lastname'][$row]));
            $position = mysqli_real_escape_string($con,($_POST['position'][$row]));
            $team = mysqli_real_escape_string($con,($_POST['team'][$row]));
            $game = mysqli_real_escape_string($con,($_POST['game'][$row]));

    }
}

if (!empty($row_data)) { 
$sql="INSERT INTO wp_es_fstest (PlayerID,FirstName,LastName,Position,Team,Game) VALUES".implode(',', $row_data); 

$query = mysqli_query($con, $sql);

if (mysql_query($query)) 
        echo 'Successful inserts: ' . mysql_affected_rows($con); 
    else 
        echo 'query failed'; 
        }

?>
Improve this question
1
  • 2
    You're mixing MySQL APIs, they don't; be consistent. Commented Aug 28, 2014 at 2:17

2 Answers 2

Reset to default
1

You are not sending multiple values for the same fields. Your name attributes are like name="playerid" so that means that every new entry with the same name will overwrite the previous one and only one gets sent to the server.

You should use arrays to group your fields:

<input type="text" name="playerid[SOME_ID]" size="20">
...
<input type="text" name="firstname[SOME_ID]" size="20">
...
// etc.

...
<input type="text" name="playerid[SOME_OTHER_ID]" size="20">
...
<input type="text" name="firstname[SOME_OTHER_ID]" size="20">
...
// etc.

For the SOME_ID, SOME_OTHER_ID, etc. values you could use a value from a database if it already exist or a simple counter, as long as the fields in a group use the same number / ID.

About the php-side, as mentioned by @Fred-ii- you should only use one sql API, in this case mysqli.

Also note that:

$playerid = mysqli_real_escape_string('playerid');

should be:

$playerid = mysqli_real_escape_string($con, 'playerid');

Personally I would also use a prepared statement with bound variables so that you can prepare your statement once and then run it several times with the different groups of values without having to worry about sql injection / escaping the values.

One additional remark:

This is going to fail:

$row_data = "($playerid,$firstname, $lastname, $position,$team,$Game)";

if (is_array($row_data));
{

You have just set your $row_data variable to be a string, probably with a bunch of undefined variables so checking one line after that whether it is an array is never going to work.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

In about 3 places too. Worms trap.
@Fred-ii- I've seen worse ;-)
Same here. Good catch on mysqli_real_escape_string('playerid') I hadn't seen that one. However, I had a quick glance at it earlier and something did seem odd about it.
I also noticed OP is repeating name="firstname" and name="lastname" etc. etc.
Alright thanks for reply guys, all the notes here make sense. I will give it a shot tonight and let you guys know. thanks for response!
0

Alright so it has been a while since the last point, life got busy and I wanted to do a little more research.

I have modified my form.html to specify that they are arrays and created a new php file to go with it. here is the code below.

<body>
<table border="1">
  <tr>
     <td align="center">Dota 2 Open Team Registration</td>
  </tr>
  <tr>
<td>
  <table>
    <form method="post" action="/wp/wp-content/plugins/FormSubmit/FSdotaArray.php">
      <tr>
      <td></td>
        <td><b>Captain</b></td>
      </tr>
      <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid[playerid_1]" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname[firstname_1]" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname[lastname_1]" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position[position_1]">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team[team_1]" size="40">
      </td>
    </tr>
    <tr>
      <td>Game</td>
      <td><select name="game[game_1]">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>

<!-- Player 2 -->   
      <td></td>
       <td><b>Player 2</b></td>
      </tr>
    <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid[player_2]" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname[firstname_2]" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname[lastname_2]" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position[position_2]">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team[team_2]" size="40">
      </td>
    </tr>
<tr>
      <td>Game</td>
      <td><select name="game[game_2]">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>

<!--Player 3 -->    
      <td></td>
       <td><b>Player 3</b></td>
      </tr>
    <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid[player_3]" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname[firstname_3]" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname[lastname_3]" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position[position_3]">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team[team_3]" size="40">
      </td>
    </tr>
<tr>
      <td>Game</td>
      <td><select name="game[game_3]">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>        

<!-- Player 4 -->   
      <td></td>
       <td><b>Player 4</b></td>
      </tr>
    <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid[player_4]" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname[firstname_4]" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname[lastname_4]" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position[position_4]">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team[team_4]" size="40">
      </td>
    </tr>
<tr>
      <td>Game</td>
      <td><select name="game[game_4]">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>       

<!-- Player 5 -->
      <td></td>
       <td><b>Player 5</b></td>
      </tr>
    <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid[player_5]" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname[firstname_5]" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname[lastname_5]" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position[position_5]">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team[team_5]" size="40">
      </td>
    </tr>
<tr>
      <td>Game</td>
      <td><select name="game[game_5]">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>       

<!-- Player Sub 1 [6] -->
      <td></td>
       <td><b>Sub 1 (Optional)</b></td>
      </tr>
    <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid[player_6]" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname[firstname_6]" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname[lastname_6]" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position[position_6]">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team[team_6]" size="40">
      </td>
    </tr>
<tr>
      <td>Game</td>
      <td><select name="game[game_6]">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>

<!-- Player Sub 2 [7] -->
      <td></td>
       <td><b>Sub 2</b></td>
      </tr>
    <tr>
      <td>Steam ID</td>
      <td><input type="text" name="playerid[player_7]" size="20">
      </td>
      </tr>
      <tr>
      <td>Fisrt Name</td>
      <td><input type="text" name="firstname[firstname_7]" size="20">
      </td>
      </tr>
      <tr>
      <td>last Name</td>
      <td><input type="text" name="lastname[lastname_7]" size="20">
      </td>
        </tr>
      <tr>
      <td>Position</td>
      <td><select name="position[position_7]">
        <option value="1:hardcarry">1: Hard Carry</option>
        <option value="2:mid">2: Mid</option>
        <option value="3:offlane">3: Offlane</option>
        <option value="4:support">4: Support</option>
        <option value="5:support">5: Support</option>
        </select>
      </td>
    </tr>        
      <tr>
      <td>Team</td>
      <td><input type="text" name="team[team_7]" size="40">
      </td>
    </tr>
<tr>
      <td>Game</td>
      <td><select name="game[game_7]">
        <option value="dota2">Dota 2</option>
        <option value="leagueoflegends">League of Legends</option>
        <option value="starcraft2">StarCraft 2: Heart of the Swarm</option>
        <option value="USFIV">USFIV</option>
        </select>
      </td>
    </tr>

      <tr>
      <td></td>
      <td align="right"><input type="submit" name="submit" value="Sent"></td>
    </tr>
    </table>
  </td>
</tr>
</table>
</body>

and then the PHP code

            <?php 
                /*
                Plugin Name: FSdota 
                Description: submit form data into sql database
                Author: F. May
                Version: 1.0
                */  

            //inserting data order
            $con=mysqli_connect("xxxxxxxx","xxxxxxx","xxxxxx!","xxxxx");
            // Check connection
            if (mysqli_connect_errno()) {
              echo "Failed to connect to MySQL: " . mysqli_connect_error();
            }

            // escape variables for security
            //define values

            $player_a = array($playerid_1,$playerid_2,$playerid_3,$playerid_4,$playerid_5,$playerid_6,$playerid_7);
            $firstname_a = array($firstname_1,$firstname_2,$firstname_3,$firstname_4,$firstname_5,$firstname_6,$firstname_7);
            $lastname_a = array($lastnanme_1,$lastnanme_2,$lastnanme_3,$lastnanme_4,$lastnanme_5,$lastnanme_6,$lastnanme_7);
            $position_a = array($position_1,$position_2,$position_3,$position_4,$position_5,$position_6,$position_7);
            $team_a = array($team_1,$team_2,$team_3,$team_4,$team_5,$team_6,$team_7);
            $game_a = array($game_1,$game_2,$game_3,$game_4,$game_5,$game_6,$game_7);

            $playerid = mysqli_real_escape_string($con, '$playerid_a');
            $firstname = mysqli_real_escape_string($con, '$firstname_a');
            $lastname = mysqli_real_escape_string($con, '$lastname_a');
            $position = mysqli_real_escape_string($con,'$position_a');
            $team = mysqli_real_escape_string($con, '$team_a');
            $game = mysqli_real_escape_string($con, '$game_a');


            for ($i = 1; $i < 7; $i++) {

            //define arrays into variables with counter
            //  $a = $playerid_a[$];
            //   $b = $firstname_a[$i]; 
            //  $c = $lastname_a[$i];
            //  $d = $position_a[$i];
            //  $e = $team_a[$i];
            //  $f = $game_a[$i];

                    $str[ ] = "('{$player_[$i]}','{$firstname_[$i]}','{$lastname_[$i]}','{$position_[$i]}','{$team_[$i]}','{$game_[$i]}')";
            }

            $s= implode(',', $str);

            $sql="INSERT INTO wp_es_fstest (playerid, firstname, lastname, position, team, game) VALUES $s";

            echo $sql;


            mysqli_close($con);

            ?>

I dont think I have defined everything correctly, in the echo i am null values.

INSERT INTO wp_es_fstest (playerid, firstname, lastname, position, team, game) VALUES     ('','','','','',''),('','','','','',''),('','','','','',''),('','','','','',''),('','','','','',''),    ('','','','','','')
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.