duanquezhan7268 2016-10-31 10:29
浏览 55
已采纳

将数组插入数据库

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();
            }
      }

    }
}
?>

How should I do to save array to table 'services' which looks like
id | service_name | background | background_price | count | count_price | type | type_price | how | how_price | resolution | resolution_price
I tried a lot of solutions from Stack but nothing didn't helped me.Regards exported schema table 'services'

  • 写回答

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();
            }
        }
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 spring后端vue前端
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿