Insert Array data into a database using PDO

Question

Can someone please explain to me how I can import the array data I am outputting into rows on my database.

HTML

<form id="AddRecipeForm" method="post" action="includes/add-recipe.php" class="form-inline">    
    <input type="text" name="recipe[ingredient][1]" class="input-large" placeholder="Title 1"><input type="text" name="recipe[quantity][1]" class="input-large" placeholder="Quantity 1"><br /><br />   
    <input type="text" name="recipe[ingredient][2]" class="input-large" placeholder="Title 2"><input type="text" name="recipe[quantity][2]" class="input-large" placeholder="Quantity 2"><br /><br />   
    <input type="text" name="recipe[ingredient][3]" class="input-large" placeholder="Title 3"><input type="text" name="recipe[quantity][3]" class="input-large" placeholder="Quantity 3"><br /><br />
    <button type="submit" class="btn">Add Recipe</button>
</form>

This is passed to a php form:

foreach($_POST['recipe'] as $key=>$value)
    {   

    }

print_r($_POST);

and outputs the following array:

Array ( 
    [recipe] => Array ( 
        [ingredient] => Array ( 
            [1] => eggs 
            [2] => milk 
            [3] => flour
        ) [quantity] => Array ( 
            [1] => 12 
            [2] => 13 
            [3] => 14 
        ) 
    ) 
)

I need to import each of the individual ingredients and quantities to a new row in my database table. I am using PDO to connect to my database but I am unsure how I can insert the data from the array into the rows on my database.

Thanks.

Restructure your database, and have a separate ingredients table... don't store them all in a column in the recipe table — Mark Baker
– Mark Baker, Commented Mar 11, 2012 at 20:50
@Mark Baker thanks for the comment. I currently do have my database separated into an ingredients, recipe title and recipe items tables. I need to get the data from the array and insert this into the recipe items table but I'm unsure how I can do that using PDO. Any help would be much appreciated. — php_d
– php_d, Commented Mar 11, 2012 at 20:57

prodigitalson · Accepted Answer · 2012-03-11 22:33:43Z

2

Well i would stucture my form a bit differently so you get ingerdients assemebled as their own array, but with the stcuture you have:

$db = new PDO($dsn, $user, $pass);

$stmt = $db->prepare('INSERT INTO ingredient (name, quantity) VALUES (?,?)');

// youll want to verify that both arrays have the same number of elements before doing this
// as part of your validation 
$ingredients = array_combine($_POST['recipe']['ingredient'], $_POST['recipe']['quantity']);

$errors = array();

foreach($ingredients as $name => $quantity)
{
    try {
       $stmt->execute(array($name, $quantity));
    } catch (PDOException $e) {
       $errors[] = array(
          'message' => "Could not insert \"$name\", \"$quantity\".",
          'error' => $e
    }    
}

if(!empty($errors)) {
   //do something?
}

edited Mar 11, 2012 at 22:33

answered Mar 11, 2012 at 20:56

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

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

1 Comment

php_d Over a year ago

Thanks everyone for the posts @prodigitalson your code above did the trick. It's much appreciated!

dog · Accepted Answer · 2012-03-11 20:56:41Z

1

Simple example without error checking:

<?php

    $dbc = new PDO(/* ... */);

    $stmt = $dbc->prepare("INSERT INTO tbl(ingredient,quantity) VALUES(:ingredient,:quantity);");
    $numIngredients = count($_POST['recipe']['ingredient']);
    for ($i=1; $i <= $numIngredients; $i++) { 
        $stmt->execute(array(
            ':ingredient' => $_POST['recipe']['ingredient'][$i],
            ':quantity'   => $_POST['recipe']['quantity'][$i]
        ));
    }

?>

Note that normally you should start counting indexes from 0 and if you just write recipe[ingredient][] PHP will automatically create the indexes.

answered Mar 11, 2012 at 20:56

dog

4103 silver badges9 bronze badges

Comments

hakre · Accepted Answer · 2012-03-11 20:56:22Z

I assume your problem is the format of the $_POST array. You have:

[recipe][ingredient][...] and
[recipe][quantity][...]

However that's not the structure of your database which organized it in rows and columns:

[recipe][...][ingredient, quantity]

You can see how the [...] moves around. You need to map the array format to your database format. This is easiest done with a foreach:

$recipes = array(); # Zero rows to insert we start with.
$formRecipes = $_POST['recipe']; # The form recipes are located here.

# The [...] part is in ingredient:
foreach ($formRecipes['ingredient'] as $index => $ingredient)
{
    # the [...] part is $index now
    $recipes[$index]['ingredient'] = $ingredient;       
    $recipes[$index]['quantity'] = $formRecipes['quantity'][$index];
}

After you've run this, use print_r to verify everything went right:

print_r($recipes);

You can now use the $recipes array to insert your data into the database for each row (I assume you know how you do the insert SQL query so I don't put it into the answer).

Collectives™ on Stack Overflow

Insert Array data into a database using PDO

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related