Here is my solution. It contains error reporting/display and all the proper steps involved in using PDO prepared statements in combination with exception handling.
The code is commented.
Try to accustom yourself with the sprintf function. With it you can build very complex sql statements in an elegant manner.
Good luck!
<?php
/*
* ============================================================
* Set error reporting level and display errors on screen.
* Use it ONLY ON A DEVELOPMENT SYSTEM, NEVER ON PRODUCTION!
* If you activate it on a live system, then the users will see
* all the errors of your system. And you don't want this!
* ============================================================
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);
/*
* User variables.
*/
// "table name" not "title" ;-)
$tableName = 'your_table';
// "Themes" not "theme" ;-)
$themes = array('t1', 't2', 't3', 't4', 't5');
// "Contents" not "content" ;-)
$contents = array('c1', 'c2', 'c3', 'c4', 'c5');
// Counter for building the sql statement's assignment list
// and the input parameters list for the sql statement's markers.
$count = 5;
try {
/*
* Create a PDO instance as db connection to a MySQL db.
*/
$connection = new PDO(
'mysql:host=localhost;port=3306;dbname=yourDb;charset=utf8'
, 'yourDbUsername'
, 'yourDbPassword'
);
/*
* Assign the driver options to the db connection.
*/
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$connection->setAttribute(PDO::ATTR_PERSISTENT, TRUE);
/*
* Build the sql statement and fill the input parameters list.
*
* ------------------------------------------------------------------------
* Notes:
*
* - Quote: "You cannot use a named parameter marker of the same name more
* than once in a prepared statement, unless emulation mode is on."
* See: http://php.net/manual/de/pdo.prepare.php
*
* - DON'T use semicolon at the end of sql statements in PHP!
* ------------------------------------------------------------------------
*/
// Sql statement's assignment list. Array of "column-name = :column-marker, ..." assignments.
$assignments = array();
// Input parameters list, e.g the values for the sql statement markers.
$bindings = array();
// Note: Use one iteration variable for both operations!
for ($i = 1; $i <= $count; $i++) {
// Add sql assignments to assignment list.
$assignments[] = sprintf('theme%s = :theme%s, content%s = :content%s', $i, $i, $i, $i);
// Add corresponding input parameter values to bindings list.
$bindings[':theme' . $i] = $themes[$i - 1];
$bindings[':content' . $i] = $contents[$i - 1];
}
// Final sql statement.
$sql = sprintf(
'UPDATE %s
SET header = %s
%s'
, $tableName
, $header
, $assignments ? ',' . implode(',', $assignments) : ''
);
/*
* Prepare and validate the sql statement.
*
* --------------------------------------------------------------------------------
* If the database server cannot successfully prepare the statement, PDO::prepare()
* returns FALSE or emits PDOException (depending on error handling settings).
* --------------------------------------------------------------------------------
*/
$statement = $connection->prepare($sql);
if (!$statement) {
throw new UnexpectedValueException('The sql statement could not be prepared!');
}
/*
* Bind the input parameters to the prepared statement.
*
* -----------------------------------------------------------------------------------
* Unlike PDOStatement::bindValue(), when using PDOStatement::bindParam() the variable
* is bound as a reference and will only be evaluated at the time that
* PDOStatement::execute() is called.
* -----------------------------------------------------------------------------------
*/
foreach ($bindings as $key => $value) {
// Get the name of the input parameter by its key in the bindings array.
$inputParameterName = is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));
// Get the PDO::PARAM_* constant, e.g the data type of the input parameter, by its value.
$inputParameterDataType = PDO::PARAM_STR;
if (is_int($value)) {
$inputParameterDataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$inputParameterDataType = PDO::PARAM_BOOL;
}
// Bind the input parameter.
$bound = $statement->bindValue($inputParameterName, $value, $inputParameterDataType);
if (!$bound) {
throw new UnexpectedValueException('An input parameter can not be bound!');
}
}
/*
* Execute the prepared statement.
*
* ------------------------------------------------------------------
* PDOStatement::execute returns TRUE on success or FALSE on failure.
* ------------------------------------------------------------------
*/
$executed = $statement->execute();
if (!$executed) {
throw new UnexpectedValueException('The prepared statement can not be executed!');
}
/*
* Get number of affected rows.
*/
$numberOfAffectedRows = $statement->rowCount();
/*
* Display results.
*/
echo $numberOfAffectedRows;
/*
* Close connection.
*/
$connection = NULL;
} catch (PDOException $exc) {
echo $exc->getMessage();
// Log the whole exception object to a file.
// $logger->log($exc);
exit();
} catch (Exception $exc) {
echo $exc->getMessage();
// Log the whole exception object to a file.
// $logger->log($exc);
exit();
}
If you wish, see my answer here (the "EDIT" part), where I posted a db adapter class. It takes care of all the db operations, validations and exception handling cases for you.
