Mysql/PHP - How to add the array values into a database from a form

Question

Can anyone see what I am doing wrong? I am trying to add multiple values from an array into a sql table using the code below:

foreach($_POST['serialNumber'] as $i => $serialNumber) 
        { 
        // Get values from form.
            $request_id = mysql_real_escape_string($req_id);
            $serialNumber = mysql_real_escape_string($_POST['serialNumber'][$i]);
            $machineType = mysql_real_escape_string($_POST['machineType'][$i]);
            $machineModel = mysql_real_escape_string($_POST['machineModel'][$i]);

        // Add to database
        $sql = "INSERT INTO serialnumbers(
                request_id, serial_type, serial_model, serial_number
                ) VALUES (
                '".$request_id."','".$machineType."','".$machineModel."','".$serialNumber."'
                )";
        }

For whatever reason, only the last values is being stored in the table. What am I missing here?

That's because you aren't executing the insert query in each iteration of the loop. This means it builds all queries, but only the last one gets executed. On another note, you might want to look into using [PDO prepared statements](http://stackoverflow.com/questions/1457131/php-pdo-prepared-statements). They'll make your code look a lot cleaner. — NullUserException, Oct 25 '13 at 16:08
Within each iteration you are creating a single insert statement. At the end of the loop you will have an insert statement with the last values you want inserted. Then you execute the insert statement and you only get the last values inserted. — melc, Oct 25 '13 at 16:09

score 0 · Answer 1 · answered Oct 25 '13 at 16:31

With the usual caveats about the deprecation of this method (mysql_), something like this should work (I might have mucked up the syntax a tiny bit)

$values = array;

foreach($_POST['serialNumber'] as $i => $serialNumber) 
    { 
    // Get values from form.
        $request_id = mysql_real_escape_string($req_id);
        $serialNumber = mysql_real_escape_string($_POST['serialNumber'][$i]);
        $machineType = mysql_real_escape_string($_POST['machineType'][$i]);
        $machineModel = mysql_real_escape_string($_POST['machineModel'][$i]);

    // Add to database
    $values[] = "(
            '".$request_id."','".$machineType."','".$machineModel."','".$serialNumber."'
            )";
    }

$sql = "INSERT INTO serialnumbers(
            request_id, serial_type, serial_model, serial_number
            ) VALUES ".implode($values,',').";";

jacouh · Accepted Answer · 2013-10-25T16:51:12.433

As shown in comments, it's irresistible to propose you to test this, for prepared statements of PDO, as you loop:

//
// PDO
//
try {
  $pdo = new PDO("mysql:host=localhost;dbname=MyDatabase", $user, $pass);
} catch (PDOException $e) {
    echo $e->getMessage();
  exit;
}
//
$sql = "INSERT INTO serialnumbers (
    request_id, serial_type, serial_model, serial_number
  ) VALUES (
    :request_id, :serial_type, :serial_model, :serial_number
)";
//
$stmt = $pdo->prepare($sql);

$n = count($_POST['serialNumber']);

for($i = 0; $i < $n; $i++)
{ 
  // Get values from form.
  $stmt->execute(array(
    ":request_id" => $req_id,
    ":serial_type" => $_POST['machineType'][$i],
    ":serial_model" => $_POST['machineModel'][$i],
    ":serial_number" => $_POST['serialNumber'][$i]
  ));
}

Mysql/PHP - How to add the array values into a database from a form

2 Answers2