doushouhe7072
doushouhe7072
2016-10-15 12:13

如何在MySQL中插入多行而不使用预准备语句插入空行

已采纳

I want to insert multiple rows with the single query using PDO prepared statement but I don't want to insert any blank row.

Actually, I have four row in my form you can see in HTML below and when I fill only one row it insert three blank row also but I don't to insert any blank row

 <form method="post">
    <table>
      <tr>
        <td><input type="text" name="col1"></td>
        <td><input type="text" name="col2"></td>
      </tr>
      <tr>
        <td><input type="text" name="col1"></td>
        <td><input type="text" name="col2"></td>
      </tr>
      <tr>
        <td><input type="text" name="col1"></td>
        <td><input type="text" name="col2"></td>
      </tr>
      <tr>
        <td><input type="text" name="col1"></td>
        <td><input type="text" name="col2"></td>
      </tr>
    </table>
  </form>

my code is like this

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:col1, :col2)');
foreach($data as $item)
{
    $stmt->bindValue(':col1', $item[0]);
    $stmt->bindValue(':col2', $item[1]);
    $stmt->execute();
}

help me please...

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • doushen4719 doushen4719 5年前

    As your col1 and col2 is multiple then you should use array: Must change database credential and change table name. Testing purpose i have use table name test.

    index.php

    <?php
        if(isset($_POST['submit'])){
            $dbhost = "localhost";  //Set your hostname
            $dbname = "dbname";     //set your dbname
            $dbusername = "root";   //set your db username
            $dbpassword = "";       //set your db password
            $link = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbusername,$dbpassword);
    
            for ($i=0; $i <count($_POST['col1']) ; $i++) {
                $rowNum = $i+1;
                if($_POST['col1'][$i]!='' && $_POST['col2'][$i]!=''){
                    $statement = $link->prepare("INSERT INTO test(col1, col2) VALUES(:col1, :col2)");
                    $res = $statement->execute(array(
                        "col1" => $_POST['col1'][$i],
                        "col2" => $_POST['col2'][$i],
                    ));
                    if($res)
                        echo "Row no: $rowNum Successfully inserted.<br>";
                    else
                        echo "Row no: $rowNum Failed to insert.<br>";
                }else
                    echo "Row no: $rowNum single or double field  empty.<br>";
            }
        }
    ?>
    
    <form method="post">
        <table>
          <tr>
            <td><input type="text" name="col1[]"></td>
            <td><input type="text" name="col2[]"></td>
          </tr>
          <tr>
            <td><input type="text" name="col1[]"></td>
            <td><input type="text" name="col2[]"></td>
          </tr>
          <tr>
            <td><input type="text" name="col1[]"></td>
            <td><input type="text" name="col2[]"></td>
          </tr>
          <tr>
            <td><input type="text" name="col1[]"></td>
            <td><input type="text" name="col2[]"></td>
          </tr>
          <tr>
            <td colspan="2"><input type="submit" name="submit" value="submit"></td>
          </tr>
        </table>
    </form>
    
    点赞 评论 复制链接分享