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

Question

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...

Answer 1

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>