Using PHP “insert multiple” to insert all 4 rows a

Question

I am trying to insert 4 forms that are the same. but with different values to mysql using PHP.

When I submit my data, the database only takes the values from the last form and inserts it 4 times. I am trying to get the values from all 4 on submit.

<div class="req3">
<h1>Requirement 4</h1>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<br>
Enter info for 4 teams and it will inserted into the database<br><br>
<div class="sqlForm">
<p class="formHead">Team 1</p>
<label>Team Name:</label> <input type="text" name="teamname"><br>
<label>City:</label> <input type="text" name="city"><br>
<label>Best Player:</label> <input type="text" name="bestplayer"><br>
<label>Year Formed:</label> <input type="text" name="yearformed"><br>
<label>Website:</label> <input type="text" name="website"><br>
</div>

<div class="sqlForm">
<p class="formHead">Team 2</p>
<label>Team Name:</label> <input type="text" name="teamname"><br>
<label>City:</label> <input type="text" name="city"><br>
<label>Best Player:</label> <input type="text" name="bestplayer"><br>
<label>Year Formed:</label> <input type="text" name="yearformed"><br>
<label>Website:</label> <input type="text" name="website"><br>
</div>

<div class="sqlForm">
<p class="formHead">Team 3</p>
<label>Team Name:</label> <input type="text" name="teamname"><br>
<label>City:</label> <input type="text" name="city"><br>
<label>Best Player:</label> <input type="text" name="bestplayer"><br>       
<label>Year Formed:</label> <input type="text" name="yearformed"><br>
<label>Website:</label> <input type="text" name="website"><br>
</div>

<div class="sqlForm">
<p class="formHead">Team 4</p>
<label>Team Name:</label> <input type="text" name="teamname"><br>
<label>City:</label> <input type="text" name="city"><br>
<label>Best Player:</label> <input type="text" name="bestplayer"><br>
<label>Year Formed:</label> <input type="text" name="yearformed"><br>
<label>Website:</label> <input type="text" name="website"><br><br></div>
<input class="styled-button" type="submit" name="insert" value="Submit">

</form>



<?php 
if (isset($_POST['insert'])) {
  insertTable();
} else {
$conn->close(); 
}

function insertTable() {

$servername = "localhost:3306";
$username = "XXXXX";
$password = "XXXXX";
$dbname = "XXXXX";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
echo ("Connection failed: " . $conn->connect_error);
} else {


$varTname = $_POST['teamname'];
$varCity = $_POST['city'];
$varBplayer = $_POST['bestplayer'];
$varYearformed = $_POST['yearformed'];
$varWebsite = $_POST['website'];

$sql = "INSERT INTO Teams (teamname, city, bestplayer, yearformed, website)
VALUES ('$varTname', '$varCity', '$varBplayer', '$varYearformed',        '$varWebsite'),
   ('$varTname', '$varCity', '$varBplayer', '$varYearformed', '$varWebsite'),
   ('$varTname', '$varCity', '$varBplayer', '$varYearformed', '$varWebsite'),
   ('$varTname', '$varCity', '$varBplayer', '$varYearformed', '$varWebsite')";

    if ($conn->multi_query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }


mysql_query($sql);


function PrepSQL($value)

{

// Stripslashes

if(get_magic_quotes_gpc())

{

    $value = stripslashes($value);

}



// Quote

$value = "'" . mysql_real_escape_string($value) . "'";



return($value);

}
}
}
?>

Answer 1

use different name like teamname1,teamname2,teamname3,teamname4

<input type="text" name="teamname1">
<input type="text" name="teamname2">
<input type="text" name="teamname3">
<input type="text" name="teamname4">

For get values :-

$varTname1 = $_POST['teamname1'];
$varTname2 = $_POST['teamname2'];
$varTname3 = $_POST['teamname3'];
$varTname4 = $_POST['teamname4'];

For insert values :-.

$sql = "INSERT INTO Teams (teamname)
VALUES ('$varTname1'),
       ('$varTname2'),
       ('$varTname3'),
       ('$varTname4')

or you can try this:-

<input type="text" name="teamname[]">

Get value like :-

$_POST['teamname'][0]

Answer 2

chnage the names of your controls so they Post as Arrays

<input type="text" name="teamname[G1]">
<input type="text" name="teamname[G2]">

this why when you use $varTname = $_POST['teamname']; $varTname is an array and each of the 4 values of teamname are set as $varTname['G#'] where # matches the number you set for that group of input fields.

then use a for loop to get the data and execute your query, something like bellow. while you at it you can also fix up your SQL Injection vulnerability. you may also want to so some more sanitation to the data just to be sure

$varTname = $_POST['teamname'];
$varCity = $_POST['city'];
$varBplayer = $_POST['bestplayer'];
$varYearformed = $_POST['yearformed'];
$varWebsite = $_POST['website'];

$stmt = $mysqli->prepare('INSERT INTO Teams (teamname, city, bestplayer, yearformed, website) VALUES (?,?,?,?,?,?)');
$varTname1Bind = "";
$varTnameBind = "";
$varCityBind = "";
$varBplayerBind = "";
$varWebsiteBind = "";

 // assuming they are all strings, adjust where needed
 $stmt->bind_param('sssssss',
    $varTname1Bind,
    $varTnameBind,
    $varCityBind,
    $varBplayerBind,
    $varYearformedBind,
    $varWebsiteBind);

for($i = 1; i < 5; $i++)
{
    $varTname1Bind = $varTname['G'.$i];
    $varTnameBind = $varTname['G'.$i];
    $varCityBind = $varCity['G'.$i];
    $varBplayerBind = $varBplayer['G'.$i];
    $varYearformedBind = $varYearformed['G'.$i];
    $varWebsiteBind = $varWebsite['G'.$i];

    $stmt->execute();
}

will save you on how much code you need to do

Answer 3

You can convert your input names into arrays by adding [] then in your php loop through the array of the $_POST[] and built up your $sql by concatenating the values until you finish looping through all values and INSERT it as multiple values.

HTML:

<label>Team Name:</label> <input type="text" name="teamname[]"><br>
<label>City:</label> <input type="text" name="city[]"><br>
<label>Best Player:</label> <input type="text" name="bestplayer[]"><br>
<label>Year Formed:</label> <input type="text" name="yearformed[]"><br>
<label>Website:</label> <input type="text" name="website[]"><br>

PHP:

<?php
    $sql = "INSERT INTO Teams (teamname, city, bestplayer, yearformed, website) VALUES ";
        for($i = 0 ; $i < count($_POST['teamname']) ; $i++){
            $varTname = $_POST['teamname'][$i];
            $varCity = $_POST['city'][$i];
            $varBplayer = $_POST['bestplayer'][$i];
            $varYearformed = $_POST['yearformed'][$i];
            $varWebsite = $_POST['website'][$i];
            $sql .= "(" .$varTname. " , " .$varCity. " , " .$varBplayer. " , " .$varYearformed. " , " .$varWebsite. "),";   
        }
        $sql = rtrim($sql, ','); // omit the last comma

    // Then Excute your query

?>

This way you don't need to give them unique names name="test1", name="test2" and so, to see it in action check this PHP Fiddle in the bottom of the result page, I've already set the values of the input fields, just hit submit and go to the bottom of the result page to see the composed INSERT statement.

---

NOTE that the above SQL is just a demo on how to build it up, DO NOT use it like this without validation and sanitizing.. ALSO STOP querying this way and instead use Prepared Statements with PDO or MySQLi to avoid SQL Injection.

So for MySQLi prepared statements, procedural style - I work with PDO - as you see in this PHP Fiddle 2, the code is:

<?php

    // you validation goes here
    if (isset($_POST['insert'])) {

        insertTable();
    } else {
        $conn->close(); 
    }

    function insertTable() {
        // enter your credentials below and uncomment it to connect
        //$link = mysqli_connect('localhost', 'my_user', 'my_password', 'world');
        $sql = "INSERT INTO Teams (teamname, city, bestplayer, yearformed, website) VALUES";
        $s = '';
        $bind = '';
        for($i = 0 ; $i < count($_POST['teamname']) ; $i++){
            $sql .= " (?, ?, ?, ?, ?)"; 
            $s .= 's';
            $varTname = $_POST['teamname'][$i];
            $varCity = $_POST['city'][$i];
            $varBplayer = $_POST['bestplayer'][$i];
            $varYearformed = $_POST['yearformed'][$i];
            $varWebsite = $_POST['website'][$i];
            $bind .= " , " . $varTname. " , " .$varCity. " , " .$varBplayer. " , " .$varYearformed. " , " .$varWebsite;
        }

        $sql = rtrim($sql, ','); // omit the last comma
        $s = "'" .$s. "'";

        $stmt = mysqli_prepare($link, $sql);
        mysqli_stmt_bind_param($stmt, $s , $bind);
        mysqli_stmt_execute($stmt);
    }
?>

Answer 4

Normally this is done by creating arrays of form controller.

<input type="text" name="teamname[]"> 
<input type="text" name="city[]">

And then you can get an array in post request.

Hope this helps!

Answer 5

try this method

   $sql = "INSERT INTO Teams (teamname, city, bestplayer,yearformed,website)
   VALUES ('$varTname', '$varCity', '$varBplayer', '$varYearformed', '$varWebsite'),
   ";
   $sql.= query same as abov
   $sql.= query same as abov
   $sql.= query same as abov
   if (!$mysqli->multi_query($sql)) {
    echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
   }

note the . dot after the first query.
I think you should also use an auto increment key
This should work.