PDO insert array values

Question

I have a form which allows me to insert a domain to a 'domains' table in my database.

Part of the form includes a list of services that I provide for that domain which are presented as a series of checkboxes and handled as an array. These services are inserted into a marketing_lookup table that has two columns the has the domain id and the service id.

I'm trying to rewrite mysql insert statements using PDO.

I can code inserting the domain to the domains table.

I need help inserting the services array into the marketing_lookup table. The services

The html form on my page

<form ....>
...
<input type='checkbox' name='services[]' value='1'>Service 1<br>
<input type='checkbox' name='services[]' value='2'>Service 2<br>
...
</form>

I have copied and pasted and edited this so far

... 
code inserting the domain into the domain table here
...
//start inserting services here
if ($services == '') $services = array();
$services = $_POST['services']; 
$id = $conn->lastInsertId(); //obtained from above

if (!isset($_POST['services'])):
echo  'Nothing Selected';

else:
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 $stmt = $conn->prepare('INSERT IGNORE INTO marketing_lookup SET   
`domain_id` = :id,
`service_id` = :serviceid')

foreach ($services as $serviceid) {
$a = array  (':1'=>$serviceid['1'],
             ':2'=>$serviceid['2']);

 if ($stmt->execute($a)) {          
       //Query succeeded
            }
    else {
                // Query failed.
                echo $q->errorCode();
                }    
// close the database connection
$conn = null;   
} // end foreach
  } //end try

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

And what is the problem exactly? Have you tried var_dump($services) to see what keys you have there? — u_mulder
– u_mulder, Commented May 31, 2016 at 7:42

Mikey · Accepted Answer · 2016-05-31 09:53:50Z

First, your order of evaluation is wrong. You shouldn't be setting a variable with a POST value BEFORE checking if the POST value even exist. You should check its existence then set it into a variable only if it exist.

$id = $conn->lastInsertId(); // obtained from above (*)

if (!isset($_POST['services'])) {
    echo  'Nothing Selected';
} else {
    $services = $_POST['services']; // array(0 => 1, 1 => 2, ...)

Second, I'm assuming you already have a connection from before (*) -- so there's no need to reconnect. As your query is short, you can use ? to bind your parameters as shown in Example #3.

    try {
        $stmt = $conn->prepare('INSERT IGNORE INTO marketing_lookup SET domain_id = ?, service_id = ?');
        foreach ($services as $serviceId) {
            $stmt->execute(array($id, $serviceId));
        }
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

$conn = null; // pointless

You might want to look into transactions as you are doing multiple inserts.

Saty · Accepted Answer · 2016-05-31 07:43:48Z

0

Since you have create placeholder of :id and :serviceid .You must bind param using this instead of :1 or :2

Change

$a = array  (':1'=>$serviceid['1'],
             ':2'=>$serviceid['2']);

To

$a = array  (':id'=>$serviceid['1'],
             ':serviceid'=>$serviceid['2']);

answered May 31, 2016 at 7:43

Saty

22.5k7 gold badges35 silver badges52 bronze badges

2 Comments

Pete D Over a year ago

Like this @Saty? .... foreach ($services as $serviceid) { $a = array (':id'=>$id, ':service_id'=>$serviceid['1'], ':service_id'=>$serviceid['2'], .... I'm getting a syntax error

Saty Over a year ago

Post you syntax message error. and use it as $a = array (':id'=>$serviceid['1'], ':serviceid'=>$serviceid['2']);

Collectives™ on Stack Overflow

PDO insert array values

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related