up vote 0 down vote favorite

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. :/

share|improve this question
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 2 days ago

5 Answers

up vote 0 down vote accepted

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()

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

        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.

share|improve this answer
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? – jayce 2 days 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. – nikc.org 2 days ago
up vote 0 down vote

What about this?

$q="";
foreach($your_array as $t) {
   $q.= "(".implode($t,",")."),";
}  

$q=substr($q,0,-1);

And to get the keys:

$keys="";
foreach($your_array as $k=>$l)
 $keys.=$k.",";

$keys=substr($keys,0,-1);

My test:

$v=array(2=> array("a","b","c"),
3=> array("a","b","c"),
4 => array("a","b","c")
);

$q="";

foreach($v as $t)
   $q.= "(".implode($t,",")."),";


$result=substr($q,0,-1);
echo $result;

Gets this:

(a,b,c),(a,b,c),(a,b,c)
share|improve this answer
up vote 0 down vote 
$columns = array_keys($insData[0]);
$values = "";
foreach($insData as $data)
   $values.= "(".$data['firstname'].",".$data['name'].",etc ."),";

omit the parentheses in your insert value section

share|improve this answer
up vote 0 down vote 
$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(); 
}
share|improve this answer
this worked for me too, thank you! – jayce 2 days ago
up vote -2 down vote

Please test with echo $sql and try in phpmyadmin/mysqlworkbench

Maybe the query is correct, but you don't see the primary key or something else

share|improve this answer
1  
-1 This is not an answer. If you have comments, write them in the "add comment" section above. – cillosis 2 days ago
Thanks for feed back – d.danailov 3 hours ago

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.