将数组插入数据库

I need to insert array into database. I've form which looks like

<form action="" method="post">
<p>servicename</p>
<input type="text" name="service_name"><br>

<p>bgname</p>
<input type='text' name="background[]">
<input type='text' name="background[]">
<input type='text' name="background[]">

<p>bg price:</p>
<input type='text' name="background_price[]">
<input type='text' name="background_price[]">
<input type='text' name="background_price[]">

<p>resolution:</p>
<input type='text' name="resolution[]">
<input type='text' name="resolution[]">

<p>resolution price</p>
<input type='text' name="resolution_price[]">
<input type='text' name="resolution_price[]">

<p>count</p>
<input type='text' name="count[]">
<input type='text' name="count[]">
<input type='text' name="count[]">
<input type='text' name="count[]">
<input type='text' name="count[]">

<p>count price</p>
<input type='text' name="count_price[]">
<input type='text' name="count_price[]">
<input type='text' name="count_price[]">
<input type='text' name="count_price[]">
<input type='text' name="count_price[]">

<p>Type:</p>
<input type='text' name="type[]">
<input type='text' name="type[]">
<input type='text' name="type[]">

<p>Type price</p>
<input type='text' name="type_price[]">
<input type='text' name="type_price[]">
<input type='text' name="type_price[]">

<p>how</p>
<input type='text' name="how[]">
<input type='text' name="how[]">

<p>how price</p>
<input type='text' name="how_price[]">
<input type='text' name="how_price[]">
<input type="submit" name="submit">
</form>

Also I wrote script which works, but I have too many unnecessary tables and I want to optimize it to one table

<?php
class Service extends Connection {
public function addService($service_name, $background, $background_price, $resolution, $resolution_price, $count, $count_price, $type, $type_price, $how, $how_price) {
        try {
            $sth = $this->dbh->prepare("INSERT IGNORE INTO services (id, name) VALUES ('', ?)");
            $sth->bindParam(1, $service_name, PDO::PARAM_STR);
            $sth->execute();
        } catch (PDOException $e) {
            echo $e->getMessage();
        }

        foreach($background as $index1 => $backgroundItem) {
            try {
                $sth = $this->dbh->prepare("INSERT IGNORE INTO background (id, service_name, background, background_price) VALUES ('', ?, ?, ?)");
                $sth->bindParam(1, $service_name, PDO::PARAM_STR);
                $sth->bindParam(2, $backgroundItem, PDO::PARAM_STR);
                $sth->bindParam(3, $background_price[$index1], PDO::PARAM_INT);
                $sth->execute();
            } catch (PDOException $e) {
                echo $e->getMessage;
            }
        }

        foreach($resolution as $index2=> $resolutionItem) {
            try {
                $sth = $this->dbh->prepare("INSERT IGNORE INTO resolution (id, service_name, resolution, resolution_price) VALUES ('', ?, ?, ?)");
                $sth->bindParam(1, $service_name, PDO::PARAM_STR);
                $sth->bindParam(2, $resolutionItem, PDO::PARAM_STR);
                $sth->bindParam(3, $resolution_price[$index2], PDO::PARAM_INT);
                $sth->execute();

            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }

        foreach($count as $index3=> $countItem) {
            try {
                $sth = $this->dbh->prepare("INSERT IGNORE INTO counts (id, service_name, count, count_price) VALUES ('', ?, ?, ?)");
                $sth->bindParam(1, $service_name, PDO::PARAM_STR);
                $sth->bindParam(2, $countItem, PDO::PARAM_STR);
                $sth->bindParam(3, $count_price[$index3], PDO::PARAM_INT);
                $sth->execute();

            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }

        foreach($type as $index4=> $typeItem) {
            try {
                $sth = $this->dbh->prepare("INSERT IGNORE INTO type (id, service_name, type, type_price) VALUES ('', ?, ?, ?)");
                $sth->bindParam(1, $service_name, PDO::PARAM_STR);
                $sth->bindParam(2, $typeItem, PDO::PARAM_STR);
                $sth->bindParam(3, $type_price[$index4], PDO::PARAM_INT);
                $sth->execute();

            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }

        foreach($how as $index5=> $howItem) {
            try {
                $sth = $this->dbh->prepare("INSERT IGNORE INTO how (id, service_name, how, how_price) VALUES ('', ?, ?, ?)");
                $sth->bindParam(1, $service_name, PDO::PARAM_STR);
                $sth->bindParam(2, $howItem, PDO::PARAM_STR);
                $sth->bindParam(3, $how_price[$index5], PDO::PARAM_INT);
                $sth->execute();

            } catch (PDOException $e) {
                echo $e->getMessage();
            }
      }

    }
}
?>

写回答
好问题 0 提建议
关注问题
分享
邀请回答
编辑收藏删除结题
收藏举报

1条回答默认最新

duanqiao1947 2016-10-31 11:48

关注

If I understand your question and what you want to achieve then this is one approach for your problem:

class Service extends Connection {
    public function addService($service_name, $background, $background_price, $resolution, $resolution_price, $count, $count_price, $type, $type_price, $how, $how_price) {
        $max = max(count($background), count($background_price), count($resolution), count($resolution_price), count($count), count($count_price), count($type), count($type_price), count($how), count($how_price));

        try {
            $query = "INSERT INTO services (`service_name`, `background`, `background_price`, `count`, `count_price`, `type`, `type_price`, `how`, `how_price`, `resolution`, `resolution_price`) VALUES ";
            $data = [];
            for ($i = 0; $i < $max; $i++) {
                $query .= "(?,?,?,?,?,?,?,?,?,?,?),";
                $data[] = isset($service_name) ? $service_name : '';
                $data[] = isset($background[$i]) ? $background[$i] : '';
                $data[] = isset($background_price[$i]) ? $background_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($count[$i]) ? $count[$i] : '';
                $data[] = isset($count_price[$i]) ? $count_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($type[$i]) ? $type[$i] : '';
                $data[] = isset($type_price[$i]) ? $type_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($how[$i]) ? $how[$i] : '';
                $data[] = isset($how_price[$i]) ? $how_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($resolution[$i]) ? $resolution[$i] : '';
                $data[] = isset($resolution_price[$i]) ? $resolution_price[$i] : 0; // OR '' depending on the column type
            }
            $query = rtrim($query, ',');
            $sth = $this->dbh->prepare($query);
            $sth->execute($data);
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
}

This is the second approach with single query for every insert:

class Service extends Connection {
    public function addService($service_name, $background, $background_price, $resolution, $resolution_price, $count, $count_price, $type, $type_price, $how, $how_price) {
        $max = max(count($background), count($background_price), count($resolution), count($resolution_price), count($count), count($count_price), count($type), count($type_price), count($how), count($how_price));

        try {
            $query = "INSERT INTO services (`service_name`, `background`, `background_price`, `count`, `count_price`, `type`, `type_price`, `how`, `how_price`, `resolution`, `resolution_price`) VALUES (?,?,?,?,?,?,?,?,?,?,?)";

            for ($i = 0; $i < $max; $i++) {
                $data = [];
                $data[] = isset($service_name) ? $service_name : '';
                $data[] = isset($background[$i]) ? $background[$i] : '';
                $data[] = isset($background_price[$i]) ? $background_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($count[$i]) ? $count[$i] : '';
                $data[] = isset($count_price[$i]) ? $count_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($type[$i]) ? $type[$i] : '';
                $data[] = isset($type_price[$i]) ? $type_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($how[$i]) ? $how[$i] : '';
                $data[] = isset($how_price[$i]) ? $how_price[$i] : 0; // OR '' depending on the column type
                $data[] = isset($resolution[$i]) ? $resolution[$i] : '';
                $data[] = isset($resolution_price[$i]) ? $resolution_price[$i] : 0; // OR '' depending on the column type

                $sth = $this->dbh->prepare($query);
                $sth->execute($data);
            }

        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
}

And for UPDATE instead of INSERT when duplicate row is found:

class Service extends Connection {
    public function addService($service_name, $background, $background_price, $resolution, $resolution_price, $count, $count_price, $type, $type_price, $how, $how_price) {
        $max = max(count($background), count($background_price), count($resolution), count($resolution_price), count($count), count($count_price), count($type), count($type_price), count($how), count($how_price));

        try {
            $query = "INSERT INTO services (`service_name`, `background`, `background_price`, `count`, `count_price`, `type`, `type_price`, `how`, `how_price`, `resolution`, `resolution_price`) VALUES
                        (:service_name, :background,:background_price,:count,:count_price,:type,:type_price,:how,:how_price,:resolution,:resolution_price)
                      ON DUPLICATE KEY UPDATE
                        `background`=:background2, `background_price`=:background_price2, `count`=:count2, `count_price`=:count_price2,
                        `type`=:type2, `type_price`=:type_price2, `how`=:how2, `how_price`=:how_price2, `resolution`=:resolution2, `resolution_price`=:resolution_price2";

            for ($i = 0; $i < $max; $i++) {
                $data = [];
                $data[':service_name'] = isset($service_name) ? $service_name : '';
                $data[':background'] = isset($background[$i]) ? $background[$i] : '';
                $data[':background2'] = isset($background[$i]) ? $background[$i] : '';
                $data[':background_price'] = isset($background_price[$i]) ? $background_price[$i] : 0; // OR '' depending on the column type
                $data[':background_price2'] = isset($background_price[$i]) ? $background_price[$i] : 0; // OR '' depending on the column type
                $data[':count'] = isset($count[$i]) ? $count[$i] : '';
                $data[':count2'] = isset($count[$i]) ? $count[$i] : '';
                $data[':count_price'] = isset($count_price[$i]) ? $count_price[$i] : 0; // OR '' depending on the column type
                $data[':count_price2'] = isset($count_price[$i]) ? $count_price[$i] : 0; // OR '' depending on the column type
                $data[':type'] = isset($type[$i]) ? $type[$i] : '';
                $data[':type2'] = isset($type[$i]) ? $type[$i] : '';
                $data[':type_price'] = isset($type_price[$i]) ? $type_price[$i] : 0; // OR '' depending on the column type
                $data[':type_price2'] = isset($type_price[$i]) ? $type_price[$i] : 0; // OR '' depending on the column type
                $data[':how'] = isset($how[$i]) ? $how[$i] : '';
                $data[':how2'] = isset($how[$i]) ? $how[$i] : '';
                $data[':how_price'] = isset($how_price[$i]) ? $how_price[$i] : 0; // OR '' depending on the column type
                $data[':how_price2'] = isset($how_price[$i]) ? $how_price[$i] : 0; // OR '' depending on the column type
                $data[':resolution'] = isset($resolution[$i]) ? $resolution[$i] : '';
                $data[':resolution2'] = isset($resolution[$i]) ? $resolution[$i] : '';
                $data[':resolution_price'] = isset($resolution_price[$i]) ? $resolution_price[$i] : 0; // OR '' depending on the column type
                $data[':resolution_price2'] = isset($resolution_price[$i]) ? $resolution_price[$i] : 0; // OR '' depending on the column type

                $sth = $this->dbh->prepare($query);
                $sth->execute($data);
            }

        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
}

本回答被题主选为最佳回答 , 对您是否有帮助呢?

报告相同问题？

关注问题

将数组对象插入数据库 php
2016-03-30 13:03

回答 1 已采纳 Try this ;) $bony = "INSERT INTO log_notifications(sent_at,multicast_id,reg_id,message_id,success
在Golang中将数组插入Postgresql数据库 postgresql
2014-11-03 03:55

回答 2 已采纳 Implement database/sql/driver.Valuer with a custom type: type int64array []int64 func (a int64ar
如何循环三维数组以将数据插入数据库？ mysql php
2018-05-06 19:29

回答 2 已采纳 You have to echo the sql variable inside the foreach loop like this. foreach ($_POST as $data =&g
c#实现几种数据库的大数据批量插入
2020-08-31 13:05

在C#中，进行大数据批量插入到不同的数据库如SQL Server、Oracle、SQLite和MySQL是常见的需求，这可以显著提高数据处理效率。以下将详细介绍如何在这些数据库中实现批量插入。首先，我们关注C#中一个关键接口`...
数组对象如何插入数据库？ sql 其他前端
2022-08-01 21:28

回答 2 已采纳第二种写法，建立循环去一个个插入肯定是可以的，把数组中的每一个元素作为插入的对象
在golang中将数组插入json对象 json
2018-03-18 06:13

回答 1 已采纳 The solution is not to unescape the string, but to marshal the complete structure you want to seri
将数组插入sql数据库中的列 mysql php
2014-05-20 02:29

回答 1 已采纳 According to the manual, if you want an insert batch you need to use $this->db->insert_batch
批量数据插入数据库
2023-07-18 11:19

冷血闯天下的博客 1.常规操作就是直接一次性直接批量插入，这种情况如果数据量超级大会超出数据库最大数据包限制。2.循环一条一条插入，会使磁盘io一直处于高位，不可取。3.大数据量分割循环插入最可取，一般1000条数据一分隔。
php数组分割插入mysql数据库 php
2019-12-11 15:24

回答 2 已采纳看代码分割这些都是没什么问题的，不过你上边的代码可能是少复制了一个 } 闭括号，那问题应该在你这两个插库的方法里边。
如何将数组值插入数据库表的列？ database php sql
2012-08-27 11:40

回答 2 已采纳 if I remember correctly, an UPDATE statement should have a WHERE clause. Like: UPDATE table SET
将数组插入MYSQL字段 mysql php
2013-12-10 21:33

回答 3 已采纳 Use the serialize() and unserialize() functions. See this question on how to store an array in My
Laravel批量插入：提升数据库性能的秘诀
2024-07-06 05:28

2401_85439108的博客批量插入是将多条记录作为一批发送到数据库，而不是单独插入每条记录。在某些情况下，你可能需要自定义批量插入的行为，比如设置特定的时间戳。// 或者使用Carbon实例// ...]);
一维数组插入数据运行时错误 c++
2022-12-19 18:03

回答 2 已采纳应该是因为当处理要插入的数小于等于第一个元素时出错了，加入对此种情况的处理即可，可以打印j在修改前后的值观察一下即可知道代码的运行过程，修改如下: #include <iostream>
asp实现批量插入表单中的数据到数据库的方法
2021-01-02 22:24

以下将详细介绍在ASP中实现批量插入表单数据到数据库的方法及相关知识点。 ### 批量插入表单数据的概念与方法批量插入是指将多个数据记录一次性地插入到数据库表中。这种方法相比于逐条插入数据的方式，在处理...
C#:几种数据库的大数据批量插入
2020-09-08 16:24

Diamond 、的博客 /// <summary> /// 提供数据批量处理的方法。 /// </summary> public interface IBatcherProvider : IProviderService { /// <summary>... /// 将 <see cref="DataTable"/&gt...
没有解决我的问题, 去提问

码龄粉丝数原力等级 --

将数组插入数据库

1条回答默认最新

码龄粉丝数原力等级 --

将数组插入数据库

1条回答 默认 最新

1条回答默认最新