使用 PHP“插入多个"同时插入所有 4 行

2022-01-09 00:00:00 forms insert php mysql sql-insert

我正在尝试插入 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 转换为数组> 然后在你的 php 循环中遍历 $_POST[] 的数组，并通过连接值来构建你的 $sql 直到你完成循环所有值和 将其作为多个值插入.

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" 等等，查看它在行动中检查PHP Fiddle 在结果页面的底部，我'已经设置好输入字段的值了，直接点击提交，然后到结果页面的底部就可以看到合成的INSERT语句了.

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 只是一个关于如何构建它的演示，不要在没有验证和清理的情况下像这样使用它.. 也停止以这种方式查询而是使用带有 PDO 或 MySQLi 以避免 SQL 注入.

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

相关文章