Menu
Community /Pin to ProfileBookmark
@NogDogFeb 10.2014 — #For one thing, you'll need quotes around each non-numeric value in the VALUES() list, and you'll want to sanitize them against SQL injection errors/attacks.
Better yet, use PDO or MySQLi, and make use of prepared statements and bound parameters, which are especially useful for repeating tasks like this. Assuming PDO, it might be something like:
Insert Multidimensional array into Mysql Database
Hi Guys,
I’m having difficulties inserting Multidimensional php array into mysql database. Could you please see what am I doing wrong?
[code=php]$array = array(
array(
‘id’ => 1,
‘firstName’ => ‘Paul’,
‘surname’ => ‘Crowe’,
‘age’ => 28,
‘gender’ => ‘male’,
‘connections’ => array(2),
‘cities’ => array(‘Dublin’ => 80, ‘New York’ => 100, ‘Paris’ => 95, ‘Madrid’ => 100, ‘London’ => 80, ‘Barcelona’ => 100, ‘Moscow’ => 20)
),
array(
‘id’ => 2,
‘firstName’ => ‘Rob’,
‘surname’ => ‘Fitz’,
‘age’ => 23,
‘gender’ => ‘male’,
‘connections’ => array(1, 3),
‘cities’ => array(‘Dublin’ => 40, ‘New York’ => 100, ‘Paris’ => 65, ‘Madrid’ => 90)
),
array(
‘id’ => 3,
‘firstName’ => ‘Ben’,
‘surname’ => “O’Carolan”,
‘age’ => null,
‘gender’ => ‘male’,
‘connections’ => array(2, 4, 5, 7),
‘cities’ => array(‘Paris’ => 90, ‘Madrid’ => 40, ‘London’ => 85, ‘Barcelona’ => 90, ‘Moscow’ => 80)
),
array(
‘id’ => 4,
‘firstName’ => ‘Victor’,
‘surname’ => ”,
‘age’ => 28,
‘gender’ => ‘male’,
‘connections’ => array(3),
‘cities’ => array(‘Paris’ => 80, ‘Madrid’ => 80, ‘London’ => 80, ‘Barcelona’ => 80, ‘Moscow’ => 40)
),
array(
‘id’ => 5,
‘firstName’ => ‘Peter’,
‘surname’ => ‘Mac’,
‘age’ => 29,
‘gender’ => ‘male’,
‘connections’ => array(3, 6, 11, 10, 7),
‘cities’ => array(‘Dublin’ => 60, ‘New York’ => 100, ‘Paris’ => 75)
),
);
foreach($array as $data){
$sql = “INSERT INTO people (firstName, surname, age, gender, connections, cities)
VALUES ($data[firstName], $datas[surname], $data[age], $data[gender], $data[connections], $data[cities])”;
}
Sign in
to post a comment3 Comments(s) ↴
0
@NogDogFeb 10.2014 — #For one thing, you'll need quotes around each non-numeric value in the VALUES() list, and you'll want to sanitize them against SQL injection errors/attacks.Better yet, use PDO or MySQLi, and make use of prepared statements and bound parameters, which are especially useful for repeating tasks like this. Assuming PDO, it might be something like:
[code=php]
$sql = "
INSERT INTO people (firstName, surname, age, gender, connections, cities)
VALUES (':firstName', ':surname', ':age', ':gender', ':connections', ':cities')
";
$stmt = $pdo->prepare($sql);
if($stmt == false) {
throw new Exception(print_r($pdo->errorInfo,1).PHP_EOL.$sql);
}
foreach($array as $data) {
$values = array(
':firstName' => $data['firstName'],
':surname' => $data['surname'],
':age' => $data['age'],
':gender' => $data['gender'],
':connections' => $data['connections'],
':cities' => $data['cities']
);
if($stmt->execute($values) == false) {
throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
}
}
[/code]