使用PDO prepared语句将空变量插入MySQL

您可以检查以下内容:

1. 允许所有列接受 NULL
2. 如果 id AUTO_INCREMENT

编辑:

像 是架构中的主键,无法设置“”。而且似乎每一列都不允许接受 无效的 因为用户没有提供变量的值 $form

试着找出确切的错误。无论是SQL还是PHP。

可以使用设置显示错误 link

我认为您遇到的问题是由于数据被绑定为 bindParam()

SQL语句中的占位符,用于准备 陈述

所以我建议使用 bindValue()

与PDOStatement::bindValue()不同,变量被绑定为引用 打电话。

$stmt = $db->prepare("INSERT INTO students
    (classid, userid, firstname, surname, form, gender)
    VALUES (:classid, :userid, :firstname, :surname, :form, :gender)");

$stmt->bindValue(':classid',$classid);//If this is a must enter or not entered by user then you can use bindParam()
$stmt->bindValue(':userid',$userid);//This as well if this is a must enter then you can use bindParam()
if($firstname == "" || $firstname == NULL){
$stmt->bindValue(':firstname', NULL, PDO::PARAM_INT);//_NULL seems to give issues
}else{
$stmt->bindParam(':firstname', $firstname);
}
$stmt->bindValue(':surname',$surname);
$stmt->bindValue(':form',$form);
$stmt->bindValue(':gender',$gender);

$stmt->execute();

谢谢你的评论和回答。几个不同的用户建议允许空值@我建议将这些列的默认值设置为NULL。它成功了

几个月来(使用一个未经准备的MySQLi语句),我一直对它如何愉快地工作感到困惑,但拒绝接受PDO语句中的这些插入。

,则不关心变量是否为空。

罗伯,这不是你问题的答案!我读了你的评论,我只是想告诉你如何正确使用错误报告、准备好的语句和异常处理。

我的建议是:一定要阅读俞正在使用的函数的文档。尤其是返回的值。通过这种方式,您将知道如何正确处理所有可能的易出错情况。对于PDO,这是一个强制性步骤;-)

祝你好运

<?php

/*
 * Set error reporting level and display the errors on screen.
 * --------------------------------------------------------------
 * DON'T DISPLAY ERRORS ON PRODUCTION, DO IT ONLY ON DEVELOPMENT!
 * --------------------------------------------------------------
 */
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    /*
     * Create a PDO instance as db connection.
     */

    // Create PDO instance.
    $connection = new PDO(
            'mysql:host=localhost;port=3306;dbname=yourDb;charset=utf8'
            , 'yourDbUsername'
            , 'yourDbPassword'
    );

    // Assign driver options.
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
    $connection->setAttribute(PDO::ATTR_PERSISTENT, TRUE);

    /*
     * 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).
     * --------------------------------------------------------------------------------
     */
    $sql = 'INSERT INTO students (
                classid,
                userid,
                firstname,
                surname,
                form,
                gender
            ) VALUES (
                :classid,
                :userid,
                :firstname,
                :surname,
                :form,
                :gender
            )';

    $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.
     * -----------------------------------------------------------------------------------
     */

    $bindings = array(
        ':classid' => $classid,
        ':userid' => $userid,
        ':firstname' => $firstname,
        ':surname' => $surname,
        ':form' => $form,
        ':gender' => $gender,
    );

    foreach ($bindings as $key => $value) {
        $bound = $statement->bindValue(
                getInputParameterName($key)
                , $value
                , getInputParameterDataType($value)
        );

        if (!$bound) {
            throw new UnexpectedValueException('An input parameter could 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 could not be executed!');
    }

    /*
     * Get last insert id.
     */
    $lastInsertId = $connection->lastInsertId();

    if (!isset($lastInsertId)) {
        throw new UnexpectedValueException('The last insert id could not be read!');
    }

    /*
     * Close connection.
     */
    $connection = NULL;

    /*
     * Print results.
     */
    echo 'Provided data successfully added with the id: ' . $lastInsertId;
} catch (PDOException $pdoException) {
    echo $pdoException->getMessage();
    exit();
} catch (Exception $exception) {
    echo $exception->getMessage();
    exit();
}

/**
 * Get the name of an input parameter by its key in the bindings array.
 *  
 * @param int|string $key The key of the input parameter in the bindings array.
 * @return int|string The name of the input parameter.
 */
function getInputParameterName($key) {
    return is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));
}

/**
 * Get the PDO::PARAM_* constant, e.g the data type of an input parameter, by its value.
 *  
 * @param mixed $value Value of the input parameter.
 * @return int The PDO::PARAM_* constant.
 */
function getInputParameterDataType($value) {
    $dataType = PDO::PARAM_STR;
    if (is_int($value)) {
        $dataType = PDO::PARAM_INT;
    } elseif (is_bool($value)) {
        $dataType = PDO::PARAM_BOOL;
    }
    return $dataType;
}