Inserting empty variables into MySQL with PDO prepared statement

You can check with following:

1. Allow all columns to accept NULL
2. If id is primary key, remove it from query. You can set it to AUTO_INCREMENT

EDIT:

As id is primary key in your schema, you can't set ''. Also it seems every column is not allowed to accept NULL and because user is not providing the value the variables like $form could be undefined or NULL so your INSERT statement won't execute.

EDIT 2:

Try to find out exact error. Whether it's for SQL or PHP.

You can set display error by using link

I think the problem you're having is due to your data getting bind as bindParam(). According to PHP doc's.

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.

So I like to suggest using bindValue() instead. To explain why I'm referring back to PHP manual.

Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

So my answer would be,

$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();

Rob, this is not an answer to your question! I read your comments and I just wanted to show you how to properly use error reporting, prepared statements and exception handling.

You can structure the code in OOP or procedural functions, if you wish. But the structure is of no relevance here.

My advice: always read the documentation of the functions yu are using. Especially the returned values. This way you'll know how to properly handle all possible error-prone situations. In regard of PDO, this is a mandatory step ;-)

Good luck!

<?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;
}