Insert data with associative array using PDO

问题:

Before you duplicate your question, I read all answers that it's has a relation with my question. I'm trying to insert data with associative array for example

<?php 
$data = array(
         'fname'=>'joe',
         'lname'=>'sina'
);
foreach ($data as $key=>$value) {

}
?>

I want to display data like this

/*insert into tblname($key)values($value);
 finally the query will appear correctly format */
insert into tblname('fname','lname') values('joe','sina');

回答1:

Do it like below:-

<?php 
$data = array(
         'fname'=>'joe',
         'lname'=>'sina'
);

$columns = "`".implode('`,`', array_keys($data))."`";
$values = "'".implode("','", array_values($data))."'";

 //NOW CHANGE QUERY CODE LIKE BELOW
 //"INSERT INTO tblname($columns) values($values);" 
?>

Now query will look like this:-https://eval.in/854152

Note:-

The above code will work but I will suggest you to use prepared statements to prevent from SQL Injection.Thanks

Reference for help:-

mysqli::prepare

PDO::prepare

回答2:

You don't need to use foreach here. If you just prepare and bind the query, you can pass $data to the execute() and get the keys by implode() on the keys.

$data = array(
         'fname'=>'joe',
         'lname'=>'sina'
);
$stmt = $pdo->prepare("INSERT INTO tblname (".implode(', ', array_keys($data)).") VALUES (:".implode(', :', array_keys($data)).")");
$stmt->execute($data);

The keys in the array must match the placeholders in the query (the ones with a colon in front of it). You also had a syntax error in the query, as columns cannot be quoted by singlequotes.