The following code should insert each key-value pair in an array into a mathing column-value in a table. The script returns no errors but the the inserted row contains only the last value in the array
E.g.
array['one'=>1,'two'=>2,'three'=>3];
insert the row successfully in a table with columns one, two and three but insert the value 3 in all.
$columns = array[];
$bind = '';
foreach[$array as $key => $value]{
$columns[] = $key;
}
$columnString = implode[$columns,','];
$valueString = implode[$columns,',:'];
$valueString = ':' . $valueString;
$core = core::getInstance[];
$STH = $core->dbh->prepare["INSERT INTO table [" . $columnString . "] VALUES
[" . $valueString . "]"];
foreach[$array as $key => $value]{
$STH->bindParam[':' . $key,$value];
}
asked Aug 18, 2012 at 15:33
1
Forget about bindParam
, just use execute
and pass it the values of $array
:
$STH->execute[$array];
Alternatively, you could scratch the named parameters altogether to simplify your code a little:
$columnString = implode[',', array_keys[$array]];
$valueString = implode[',', array_fill[0, count[$array], '?']];
$STH = $core->dbh->prepare["INSERT INTO table [{$columnString}] VALUES [{$valueString}]"];
$STH->execute[array_values[$array]];
answered Aug 18, 2012 at 15:55
netcodernetcoder
64.9k17 gold badges120 silver badges142 bronze badges
0
maybe something like this:
$columns = array['one'=>1,'two'=>2,'three'=>3];
$columnString = implode[',', array_flip[$columns]];
$valueString = ":".implode[',:', array_flip[$columns]];
$core = core::getInstance[];
$STH = $core->dbh->prepare["INSERT INTO table [" . $columnString . "] VALUES [" . $valueString . "]"];
foreach[$columns as $key => $value]{
$STH->bindValue[':' . $key, $value];
}
answered Aug 18, 2012 at 15:55
Mihai IorgaMihai Iorga
38.6k15 gold badges107 silver badges106 bronze badges
foreach[$array as $key => $value]{
$STH->bindParam[':' . $key,$array[$key]];
}
Try that
answered Aug 18, 2012 at 15:42
MakitaMakita
1,80212 silver badges15 bronze badges
$conn = new PDO['mysql:host=' . $HOST . ';dbname=' . $DATABASE, $USERNAME, $PASSWORD];
$conn->exec["set names utf8"];
$conn->setAttribute[PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING];
$ins_query = 'INSERT INTO `' . $table_name . '` ';
$columns = array[];
$columns_bindings = array[];
foreach [$dataArray as $column_name => $data] {
$columns[] = $column_name;
$columns_bindings[] = ':' . $column_name;
}
$ins_query = $ins_query . '[' . implode[', ', $columns] . '] VALUES [' . implode[', ', $columns_bindings] . ']';
$stmt = $conn->prepare[$ins_query];
foreach [$dataArray as $column_name => $data] {
$stmt->bindValue[":" . $column_name, $data];
}
if [!$stmt->execute[]] {
print_r[$stmt->errorInfo[]];
} else {
echo "Insertd";
}
answered May 30, 2017 at 9:34
Umair AyubUmair Ayub
16.8k14 gold badges66 silver badges142 bronze badges
@netcoder 's reply is also good if want to achieve your goal using positional placeholders. However, You can use sprintf function to prepare dynamic sql query using named placeholder.
$sql = sprintf["INSERT INTO table [%s] VALUES[%s]",
implode[", ", array_keys[$array]],
":" . implode[", :", array_keys[$array]]];
$pdo->prepare[$sql];
$pdo->execute[$array];
answered Feb 26, 2021 at 16:17
Asif AliAsif Ali
681 silver badge6 bronze badges
0