Insert _nested_ array into MySQL database with PHP

Question

I have an nested Array like this:

array(3) { 
[1]=> array(5) { 
    ["firstname"]=> string(2) "john" 
    ["name"]=> string(2) "dorian" 
    ["email"]=> string(2) "[email protected]" 
    ["sex"]=> string(1) "m" 
    ["size"]=> string(1) "L" } 
[2]=> array(5) { 
    ["firstname"]=> string(2) "Sam" 
    ["name"]=> string(2) "Stuard" 
    ["email"]=> string(2) "[email protected]" 
    ["sex"]=> string(1) "m" 
    ["size"]=> string(1) "S" } 
[3]=> array(5) { 
    ["firstname"]=> string(2) "vanessa" 
    ["name"]=> string(2) "sherbatzky" 
    ["email"]=> string(2) "[email protected]" 
    ["sex"]=> string(1) "w" 
    ["size"]=> string(3) "S" } }

The number of the outermost arrays ( [1], [2], [3] ) can change.

I tried with something like

$columns = implode(", ",array_keys($insData));
$escaped_values = array_map('mysql_real_escape_string', array_values($insData));
$values  = implode(", ", $escaped_values);
$sql = "INSERT INTO `user`($columns) VALUES ($values)";

but I didn't get it. :/

is $insdata that whole array? You'd need to LOOP on the array to get each sub-array, and THEN do your db stuff. — Marc B
– Marc B, Commented Mar 1, 2013 at 21:52

nikc.org · Accepted Answer · 2013-03-05 08:59:08Z

1

The mysql_ functions are deprecated. Please, don't use them for any new code. Use either mysqli or PDO.

You'll want to iterate over you array, and insert the rows one at a time. You could construct a (prepared) statement for a multiple row insert, but if you're new to databases, you might be better off starting with the basics and then building up from there.

// Create a new connection, see the manual on the details
$conn = new PDO(/* see PHP manual for the params*/);

// Start a new transaction. All MySQL storage engines don't support transactions,
// so this step might be superfluous. Check your schema definition and the 
// MySQL manual.
$conn->beginTransaction()

// Create a prepared statement
$stmt = $conn->prepare("INSERT INTO user (firstname, name, email, sex, size) VALUES (:firstname, :name, :email, :sex, :size)");

try {
    foreach ($insData as $dataset) {
        foreach ($dataset as $colname => $value) {
            // Bind values for the placeholders in the prepared statement
            $stmt->bindValue(":" . $colname, $value);
        }

        // Run your query
        $stmt->execute();
    }

    // Commit the changes pending in the transaction
    $conn->commit();
} catch (PDOException $e) {
    // Rollback all statements in the transaction on errors
    $conn->rollback();
}

If you need to create your entire statements dynamically, that's also possible. It just requires one extra step for creating the columns part of the query.

edited Mar 5, 2013 at 8:59

answered Mar 1, 2013 at 22:10

nikc.org

17k7 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jayce Over a year ago

Thank you, thats awesome! PDO seems to be a little bit complicated but when you say mysql_ functions are not up to date any more i'll give it a try. So, do I have to check the userinput (the array is from a $_POST) or is PDO sql injection safe?

nikc.org Over a year ago

PDO (actually prepared statements) is injection safer than vanilla-SQL, because the actual query and the parameters are kept separate. (The merging of the two happens in the DBMS.) But you should always, always check your input.

MIIB · Accepted Answer · 2013-03-01 22:00:54Z

0

$columns = array_keys($insData[0]);
$values = "";
foreach($insData as $data)
   $values.= "(".$data['firstname'].",".$data['name'].",etc ."),";

omit the parentheses in your insert value section

answered Mar 1, 2013 at 22:00

MIIB

1,84910 silver badges21 bronze badges

Comments

Jeremy Harris · Accepted Answer · 2013-03-01 22:02:36Z

0

$columns = array();
$values = array();    

foreach($insData as $row)
{
    foreach($row as $columnName => $columnValue)
    {
        $columns[] = $columnName;
        $values[] = $columnValue;
    }

    // Build query
    $query = "INSERT INTO `user` (" . implode(",", $columns) . ") VALUES (" . implode(",", $values) . ")";

    // DO INSERT HERE (PREFERABLY WITH PDO OR MYSQLI)   

    // Reset columns/values
    $columns = array();
    $values = array(); 
}

answered Mar 1, 2013 at 22:02

Jeremy Harris

24.7k13 gold badges84 silver badges133 bronze badges

Collectives™ on Stack Overflow

Insert _nested_ array into MySQL database with PHP

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related