PDO更新声明不起作用

Question

I'm having an issue with the PDO Statement "Update". I want to update existing columns in my DB using this code:

<?php
$sql="UPDATE $title SET header = $header;";

for($co=1,$arr=0;$co<=$count;$co++,$arr++)
{
   $sql .= "UPDATE $title SET theme$co = :theme, content$co = :content;";
   $insert = array('theme' => $theme[$arr], 'content' => $content[$arr]); 

   $statement = $pdo->prepare($sql);
   $statement->execute($insert);
}
?>

The Problem is, that nothing happends in the DB when this code is done. The arrays "theme" & "content" just contain a bit of text which a user has written down to it. Depending on how many themes/content the user has entered the more arrays you get. For example:

$theme1="a";
$theme2="b";
$content1="abc";
$content2="def";

So in the DB, the colums were already created with the same names as the theme & content arrays. Now what I want to do is, insert the value of for example $theme1 to the column "theme1" and the same thing with $content1 to the column "content1" and so on...

I've already tried to change the

$sql .= to $sql =

and execute it as a single query. But that also didn't work.

I'm not using the "Insert" Statement, because it would always create a new line in the DB, so it would look like this.
As you can see on the screenshot this is completely wrong and I'm trying to get everything in one line. That's why I try Update instead of Insert. Since the creation of the table and all it's columns is working fine with PDO, I'm wondering why Update isn't working at all.

Edit: The variable "titel" isn't spelled wrong, I'm german and this is correct for me. Since you can name variables as you want, I'm allowed to do that in my mother language. But thanks anyway for telling me, i tried to replace all german variables with english words but I missed that one, so i've changed that now.

Answer 1

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.