Adding array into sql database

Question

I am trying to add this array into the database correctly. I have 3 input boxes and when I echo $key all my info is there like this Jonesbass23 but when I run code each field gets a new row in the database it doesn't all go into 1 row and the values are going into the wrong columns as well. Also will add my $_POST array dynamically changes based on how many input fields the user wants so my database entries could be multiple. Thought I had a handle on it but guess not. Any help would be appreciated thank you.

    <?php
session_start();
$errmsg_arr = array();
$errflag = false;
require_once 'dbconfig.php';        // Database Connection

//Retrieve data input from addfish.php

$username = $_SESSION['username'];

print_r($_POST);
}

//Returns error message text on screen


if($errflag) {
$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
session_write_close();
header("location: addfish.php");
exit();
}

//Insert data into database


$statement = $dbc->prepare("INSERT INTO entries(username, species, length, weight)
    VALUES(:username, :species, :length, :weight)");

foreach($_POST as $key => $data) {

echo $key;
echo $data['species']; // The data of species1, species2, or species3, etc.
echo $data['length'];
echo $data['weight'];


try {
    $statement->execute(array(
        "username" => "$username",
        "species" => $data['species'],
        "length" => $data['length'],
        "weight" => $data['weight']
    ));
}

catch(PDOException $e) {
    echo "Exception caught: $e";
}
} 
?>  

Answer 1

The problem is that you're executing 1 insert for every key of the $_POST:

foreach($_POST as $key => $value)

So if your field has 3 inputs, your post would look like this:

$_POST => array (
   ['key1'] => 'value1'
   ['key2'] => 'value3'
   ['key3'] => 'value2'
)

And your foreach will perform one insert per every key, that's why you end up with a new row for every key, not to mention that you're inserting only one letter of the $key string: $key[1] will return the second letter of the $key string, for example.

Also, the reason you don't need to loop over $_POST is simply because each key contains a different value, and you can access them normally by $_POST['species'], for example, as FuzzyTree suggests.

Update:

You can group the information of your inputs by naming them as follows:

<input type="text" name="species1[species]" />
<input type="text" name="species1[length]" />
<input type="text" name="species1[weight]" />
<br />
<input type="text" name="species2[species]" />
<input type="text" name="species2[length]" />
<input type="text" name="species2[weight]" />

And so on, so you can loop over them on your php like this:

foreach($_POST as $key => $data) {
    echo $key; // This will print species1, species2, species3, etc.

    echo $data['species']; // The data of species1, species2, or species3, etc.
    echo $data['length'];
    echo $data['weight'];
}

Also, it's suggested that you prepare your statement before and not inside the loop. There's no problem in doing this, but that's the point of using a prepared statement. An example combining both sugestions:

$statement = $dbc->prepare("INSERT INTO entries(username, species, length, weight)
    VALUES(:username, :species, :length, :weight)");

foreach($_POST as $key => $data) {
    try {
        $statement->execute(array(
            "username" => $username,
            "species" => $data['species'],
            "length" => $data['length'],
            "weight" => $data['weight']));

    } catch(PDOException $e) {
        echo "Exception caught: $e";
    }
}

Answer 2

You have to serialize your array.

Here is the example from the documentation

$sqldata = array (serialize($session_data), $_SERVER['PHP_AUTH_USER']);

Basically it puts your array into a single line, assigns it to a variable and stores it so it can be 'unserialized' later (basically it parses through the characters).