trapczyk trapczyk - 1 month ago 9
MySQL Question

Insert array to database

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

Answer

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] : '';
                $data[] = isset($count[$i]) ? $count[$i] : '';
                $data[] = isset($count_price[$i]) ? $count_price[$i] : '';
                $data[] = isset($type[$i]) ? $type[$i] : '';
                $data[] = isset($type_price[$i]) ? $type_price[$i] : '';
                $data[] = isset($how[$i]) ? $how[$i] : '';
                $data[] = isset($how_price[$i]) ? $how_price[$i] : '';
                $data[] = isset($resolution[$i]) ? $resolution[$i] : '';
                $data[] = isset($resolution_price[$i]) ? $resolution_price[$i] : '';
            }
            $query = rtrim($query, ',');
            $sth = $this->dbh->prepare($query);
            $sth->execute($data);
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }
}
Comments