使用PHP“插入多个"来同时插入所有4行 [英] Using PHP “insert multiple” to insert all 4 rows at the same time

问题描述

我正在尝试插入4个相同的表格.但是使用PHP与mysql的值不同.

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

当我提交数据时，数据库仅从最后一个表单中获取值并将其插入4次.我正在尝试从提交的所有4个值中获取值.

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);

}
}
}
?>

推荐答案

您可以通过添加[]将input name s转换为数组，然后在php循环中通过$_POST[]数组进行构建通过连接值来设置$sql，直到您完成所有值的循环并把INSERT当作多个值循环.

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

?>

通过这种方式，您无需为其赋予唯一的名称name="test1"，name="test2"，因此，要查看其实际效果，请检查此

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.

注意，上面的SQL只是有关如何构建它的演示，请勿在未经验证和清除的情况下使用它..也请停止以这种方式查询，而改用Prepared Statements与 PDO 或

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.

因此，对于MySQLi准备的语句，程序风格-我使用PDO -正如您在 PHP Fiddle 2 ，代码为:

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);
    }
?>

这篇关于使用PHP“插入多个"来同时插入所有4行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！