A neat way to save to MySQL if you have an array.

#1

I stumble across this on the internet and found a way where if you have an array that you want to save to a database table. Doing it this way is not only fast, but is also secure.

Here’s the function:
[php] function insertRecords(array $records) {

$db = Database::getInstance();
$pdo = $db->getConnection();
    
$name = "John Wayne";
$insertQuery = array(); // Creates the Query String
$insertData = array(); // Creates the Data VALUES
$n = 0;
$sql = 'INSERT INTO schedule (name, appDate, comments) VALUES ';
/* In this case it's the array $records that we are getting the data from */
foreach ($records as $innerArray) {
  $insertQuery[] = '(:name' . $n . ', :appDate' . $n . ', :comments' . $n . ')';
  $insertData['name' . $n] = $name;
  
  /* I could had used another foreach to access the associate array */
  /* but I save a few steps by converting the array to an object        */      
  $record = (object) $innerArray;
  $insertData['appDate' . $n] = $record->appDate;
  $insertData['comments' . $n] = $record->comments;
  $n += 1;
}
/** Actual Saving of the Data **/
if (!empty($insertQuery)) {
  $sql .= implode(', ', $insertQuery);
  $stmt = $pdo->prepare($sql);
  $stmt->execute($insertData);
}

return 'Data Successfully Saved!';

}[/php]

Obviously, you will have to modify it to fit your array format, but I thought it was neat for it uses PDO and prepared statements.

#2

Very Cool - Thanks for sharing.