php - 将数组插入数据库

Question

我需要将数组插入数据库。我的表格看起来像

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

我还编写了有效的脚本，但我有太多不必要的表，我想将其优化为一个表

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

    }
}
?>

我应该如何将数组保存到表“服务”，如下所示
id |服务名称 |背景|背景价格 |计数|计数价格 |类型 |类型价格 |如何|价格如何|分辨率 |分辨率价格
我尝试了 Stack 中的很多解决方案，但没有任何帮助。问候 exported schema table 'services'

Answer 1

如果我理解您的问题以及您想要实现的目标，那么这是解决您的问题的一种方法:

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

这是对每个插入进行单个查询的第二种方法:

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

当发现重复行时，使用UPDATE而不是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
                        (: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();
        }
    }
}