Inserting multiple values with a prepare statement can be a little tedious. This solution uses PDO rather than mysqli as you can loop around the bindValue statement.

include_once('includes/pdoconn.inc.php');
$colNames = $conn->prepare("DESCRIBE movies");
$colNames->execute();
$tableFields = $colNames->fetchAll(PDO::FETCH_COLUMN);
$noFields = count($tableFields);
$filmArray = array("Argo","Argo is a 2012 historical drama thriller film directed, co-produced by, and starring Ben Affleck. This dramatization is adapted from U.S. Central Intelligence Agency operative Tony Mendez's book","argo.jpg",4,5,"PG");
$arrLen = count($filmArray);
$cols = "";
$holders = "";

for($i=1;$i<$noFields;$i++){
	$cols .= ($cols == "") ? "" : ", ";
	$cols .= $tableFields[$i];
	$holders .= ($holders == "") ? "" : ", ";
	$holders .= ":".$tableFields[$i];
}

$sql = "INSERT INTO movies ($cols) VALUES ($holders)";

$stmt = $conn->prepare($sql);

for($i=1;$i<$noFields;$i++){
	$j = $i-1;
	$field = ":".$tableFields[$i];
	$val =  $filmArray[$j];
	$stmt->bindValue($field, $val);
}
 
$stmt->execute();

The code also assumes a table structure where the first field is an auto-increment thus the loop is pushed on one so as to ignore this field. You could easily adapt this to take values from $_GET or $_POST

One Thought to “Inserting Multiple Values with PDO and a Loop”

  1. SIMPLY EXCELLENT ..THANKS

Leave a Comment