php - 在php mysql中插入数据库

Question

我正在用 php 插入数据库，以便提交多行，因为要提交的信息是动态生成的。问题是它只提交最后的数据而忽略其余的。我该怎么做?这是我的代码:

<?php
include('includes/conn.php');
$row="SELECT name,refNo,department FROM profile WHERE     department='$getid'";
$query=mysqli_query($conn,$row)  or die(mysqli_error($conn));
echo "<div class='col-md-4 col-md-offset-1' >";
echo "<table class='table table-hover table-striped table-bordered'>
<tr class='info'>
</tr>";
while($row=mysqli_fetch_array($query))
{
    $name=$row['name'];
    $job=$row['refNo'];
    echo "<tr>";
    echo "<td>".$row['name']."</td>";
    echo "<td>".$row['refNo']."</td>";
    echo "<form method='post' action='selection.php'>";
    echo "<td>"."<input type='text' name='essential' class='form-control' width='100%' required>"."</td>";
    echo "<td>"."<input type='text' name='desirable' class='form-control' width='100%' required>"."</td>";
    echo "<input type='hidden' name='name' value='".$name."'>";
    echo "<input type='hidden' name='job' value='".$job."'>";
    echo "</tr>";
    if(isset($_POST['submit']))
    {
        $name=$_POST['name'];
        $job=$_POST['job'];
        $essential=$_POST['essential'];
        $desirable=$_POST['desirable'];
        $insert="INSERT INTO shortlist(name,job,points) VALUES('$name','$job','$essential' + '$desirable')";
        $query=mysqli_query($conn,$insert) or die(mysqli_error($conn));
        if($query)
        {
            header("location:index.php");
        }
    }
}
echo "</table>";
echo "<input type='submit' class='btn btn-success' name='submit' value='Submit'>";
"</form>";
echo "</div>";
mysqli_close($conn);
?>

Answer 1

您应该只在 while 循环外插入一条语句。试试这个。

$query = mysqli_query ( $conn, $row ) or die ( mysqli_error ( $conn ) );
echo "<div class='col-md-4 col-md-offset-1' >";
echo "<table class='table table-hover table-striped table-bordered'>

<tr class='info'>

</tr>";
//insert outside while loop
$insert = "INSERT INTO shortlist(name,job,points) VALUES ";
$values = array();
while ( $row = mysqli_fetch_array ( $query ) ) {
    $name = $row ['name'];
    $job = $row ['refNo'];
    echo "<tr>";
    echo "<td>" . $row ['name'] . "</td>";
    echo "<td>" . $row ['refNo'] . "</td>";
    echo "<form method='post' action='selection.php'>";
    echo "<td>" . "<input type='text' name='essential' class='form-control' width='100%' required>" . "</td>";
    echo "<td>" . "<input type='text' name='desirable' class='form-control' width='100%' required>" . "</td>";
    echo "<input type='hidden' name='name' value='" . $name . "'>";
    echo "<input type='hidden' name='job' value='" . $job . "'>";
    echo "</tr>";
    if (isset ( $_POST ['submit'] )) {
        $name = $_POST ['name'];
        $job = $_POST ['job'];
        $essential = $_POST ['essential'];
        $desirable = $_POST ['desirable'];
        //fetch values
        $values[] = "('" . $name . "','" . $job . "','" . ($essential + $desirable) . "')";
        if ($query) {
            header ( "location:index.php" );
        }
    }
}
echo "</table>";
echo "<input type='submit' class='btn btn-success' name='submit' value='Submit'>";
"</form>";

echo "</div>";
// implode and append all the values, will generate a sql like 'insert into table_name (column_group) values (val_group_1),(val_group_2)'
$insert .= implode(',',$values); 
//insert multiple rows at one time
$query = mysqli_query ( $conn, $insert ) or die ( mysqli_error ( $conn ) );
mysqli_close ( $conn );
?>