如何在mysql数据库中插入多行

最后发布: 2015-10-01 07:43:10


问题

大家好,我想问一下如何在mysql数据库中插入多行,尽管我已经知道如何在mysql数据库中插入一行,但是我不知道如何插入多行。

这是我的布局示例 在此处输入图片说明

这是确切的情况

当我填写日期和备注时,我单击添加按钮,它应该插入我的数据库中。

这是我的PHP脚本,我知道它有问题。

<?php

require("config.inc.php");
if(!empty($_POST)){

        if(empty($_POST['date']) || empty($_POST['remark'])){
            $response["Success"] = 0;
            $response["Message"] = "Please complete all fields.";
            die(json_encode($response));
        }

        $query = "SELECT 1 FROM tb_attendance WHERE date = :date";
        $query_params = array(
                                ':date'=> $_POST['date'],
                                );

        try{
            $stmt = $db->prepare($query);
            $result = $stmt->execute($query_params);
        }
        catch(PDOException $ex){
            $response["Success"] = 0;
            $response["Message"] = "Database Error. Please Try Again...";
            die(json_encode($response));
        }

        $row = $stmt->fetch();
            if($row){
                $response["Success"] = 0;
                $response["Message"] = "I'm sorry, this Record is already existed.";
                die(json_encode($response));
            }

            $query = "INSERT INTO tb_attendance (date, remark) VALUES(:date, :remark)";
            $query_params = array(
                                    ':date' => $_POST['date'],
                                    ':remark' => $_POST['remark']

                                  );

        try{
                    $stmt = $db->prepare($query);
                    $result = $stmt->execute($query_params);
            }
            catch(PDOException $ex){
                $response["Success"] = 0;
                $response["Message"] = "Database Error1. Please Try Again...";
                die(json_encode($response));
            }
                $response["Success"] = 1;
                $response["Message"] = "Attendance Successfully Recorded.";
                echo json_encode($response);

}else{
?>

    <form action="addAttendance.php" method="post">
    Date: <input type="text" name="date"><br>
    Remark: <input type="text" name="remark"><br>
    <input type="submit">
    </form>
    <?php
}

?>
php android mysql
回答

只需以数组形式发送帖子数据,然后使用该数组来创建查询以插入一堆记录


回答

尝试按照以下结构进行多次插入

INSERT INTO `table_name` (`key1`, `key2`, `key3`, `key4`) VALUES
('val11', 'val12', 'val13', ''),
('val21', 'val22', 'val23', ''),
('val31', 'val32', 'val33', ''),
('val41', 'val42', 'val43', '');


回答

让我们考虑一下您有多个值的数组,例如

$array = array (
      0 => array(
          0=>"val11",
          1=>"val12",
          2=>"val13",
          3=>"val14"
      ),
      1 => array(
          0=>"val11",
          1=>"val12",
          2=>"val13",
          3=>"val14"
      ),
      2 => array(
          0=>"val11",
          1=>"val12",
          2=>"val13",
          3=>"val14"
      ),
      3 => array(
          0=>"val11",
          1=>"val12",
          2=>"val13",
          3=>"val14"
      )
)

然后,您可以像爆裂数组的每个索引

foreach($array as &$value){
    $value = "('".implode("', '",$value)."')"
}

这将返回一个数组

$array = array (
      0 => "('val11', 'val12', 'val13', 'val14')",
      1 => "('val11', 'val12', 'val13', 'val14')",
      2 => "('val11', 'val12', 'val13', 'val14')",
      3 => "('val11', 'val12', 'val13', 'val14')"
)

然后可以用逗号将数组内插

$array = implode (", "$array);

这将返回一个类似的字符串

('val11', 'val12', 'val13', ''), ('val21', 'val22', 'val23', ''), ('val31', 'val32', 'val33', ''), ('val41', 'val42', 'val43', '');

你可以用

INSERT INTO `table_name` (`key1`, `key2`, `key3`, `key4`) VALUES 

因此,您将获得完整的查询为

INSERT INTO `table_name` (`key1`, `key2`, `key3`, `key4`) VALUES ('val11', 'val12', 'val13', ''), ('val21', 'val22', 'val23', ''), ('val31', 'val32', 'val33', ''), ('val41', 'val42', 'val43', '');

正如您可以在数组中猜测的那样,您可以拥有任意数量的记录,记录可以是1或100。