Inserting data array into DB using PDO

[MM4D]

This started out as a problem with getting data from an array, into a MySQL DB, using PDO and a stored procedure.

I did manage to fix the problem I had with it not working, but I’m now curious as to whether my solution is the best way to do this. I suspect not, and I’m open to any suggestions to improve this.

My code.

   foreach($inRmName as $a => $b) {
	//Output array data for testing purposes
	echo $a+1;
	echo $inRmName[$a].'<br>'.$inLngDim[$a].'<br>'.$inShtDim[$a].'<br>'.$inRmDesc[$a].'<br>'.$inRmFlr[$a].'<br>'.$outID.'<br>';
	//Run PDO call on stored procedure
	$pdo = new PDO("mysql:dbname=$database_connBora;host=$hostname_connBora", $username_connBora, $password_connBora);
	$stmt = $pdo->prepare("CALL sp_newRms(:inRmName, :inLngDim, :inShtDim, :inRmDesc, :inRmFlr, :inPgID)");
	$stmt->bindParam(':inRmName', $inRmName[$a], PDO::PARAM_STR);
	$stmt->bindParam(':inLngDim', $inLngDim[$a], PDO::PARAM_STR);
	$stmt->bindParam(':inShtDim', $inShtDim[$a], PDO::PARAM_STR);
	$stmt->bindParam(':inRmDesc', $inRmDesc[$a], PDO::PARAM_STR);
	$stmt->bindParam(':inRmFlr', $inRmFlr[$a], PDO::PARAM_STR);
	$stmt->bindParam(':inPgID', $outID, PDO::PARAM_STR);
	$stmt->execute();
	$stmt->closeCursor();
	unset($stmt);
		
}

I think it’s self explanatory what’s happening there. Is there a better way to achieve what I’m doing? How would everyone else do this?

[felgall]

Move the new PDO() and prepare statements before the start of the loop. You only need to prepare the statement once in order to be able to use it as often as you need - that’s the main reason for prepare existing in the first place.

[MM4D]

Ok, that makes sense thinking about it.

Thanks for that. Will give that a go.