'PDOException': Invalid parameter number with message 'SQLSTATE[HY093]: number of bound variables does not match number of tokens'

Question

My issue is I'm trying to connect to my database (it has 4 elements to it, ID, Date, Time and Last Value(last_value)) but as I'm intending to put my data into the database the "message_id" element is null. Can anyone help me out?

Stack Trace

'PDOException': Invalid parameter number with message 'SQLSTATE[HY093]: number of bound variables does not match number of tokens' in C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkDatabaseWrapper.php:78 Stack trace: #0 C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkDatabaseWrapper.php(78): PDOStatement->execute() #1 C:\wamp\www\CTEC3110\includes\classes\cw_download_message_data\class.CourseWorkDownloadMessageDataModel.php(216): CourseWorkDatabaseWrapper->safe_query('SELECT message_...', Array) #2 C:\wamp\www\CTEC3110\includes\classes\cw_download_message_data\class.CourseWorkDownloadMessageDataModel.php(169): CourseWorkDownloadMessageDataModel->do_check_if_data_exists() #3 C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkContainer.php(126): CourseWorkDownloadMessageDataModel->do_store_downloaded_message_data() #4 C:\wamp\www\CTEC3110\includes\classes\cw_download_message_data\class.CourseWorkDownloadMessageDataController.php(6): CourseWorkCont in C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkDatabaseWrapper.php on line 91

Check if Data Exists code

$m_message_id = NULL;
$m_message_date = $this->c_arr_download_message_data['message-date'];
$m_message_time = $this->c_arr_download_message_data['message-time'];
$m_message_last_value = $this->c_arr_download_message_data['message-data'];

$m_sql_query_string = CourseWorkSqlQuery::does_message_exist();

$m_arr_sql_query_parameters =
array(  ':message_id' => $m_message_id,
        ':message_date' => $m_message_date,
        ':message_time' => $m_message_time,
        ':message_last_value' => $m_message_last_value);

$this->c_obj_database_handle->safe_query($m_sql_query_string, $m_arr_sql_query_parameters);

SQL Query

public static function does_message_exist()
{
    $m_sql_query_string  = "SELECT message_id, message_time, message_date, message_last_value ";
    $m_sql_query_string .= "FROM cw_messages ";
    $m_sql_query_string .= "WHERE message_id = :message_id ";
    $m_sql_query_string .= "AND message_time = :message_time ";
    $m_sql_query_string .= "AND message_date = :message_date ";
    $m_sql_query_string .= "AND message_last_value = :message_last_value ";
    $m_sql_query_string .= "LIMIT 1";
    return $m_sql_query_string;
}

Query Checking Code (Under a try/catch)

$m_temp = array();
$this->c_obj_database_handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$this->c_obj_stmt = $this->c_obj_database_handle->prepare($m_query_string);

// bind the parameters
if (sizeof($m_arr_query_parameters) > 0)
{
    foreach ($m_arr_query_parameters as $m_param_key => $m_param_value)
    {
        $m_temp[$m_param_key] = $m_param_value;
        $this->c_obj_stmt->bindParam($m_param_key+1, $m_temp[$m_param_key], PDO::PARAM_STR);
    }
}

// execute the query
$m_execute_result = $this->c_obj_stmt->execute();
$this->c_arr_database_connection_messages['execute-OK'] = $m_execute_result;
$m_database_query_execute_error = false;

possible duplicate of How do I insert NULL values using PDO? — hjpotter92
@hjpotter92 Not relevant, sorry. It's not that I'm trying to insert the null value using PDO; it's that I'm trying to test the data I've got against pre-existing values already in my database. — Raisus
@meda He suggested that this was a possible duplicate of another question, but I'll try it anyway and get back to this. EDIT: No luck. Even using your recommendation still returns the same error and stack trace. $this->c_obj_stmt->bindValue($m_param_key+1, $m_param_value, PDO::PARAM_STR); — Raisus

meda meda · Accepted Answer · 2014-08-08T16:45:16

Try this, since you are using named parameter, it does not makes sense to increment the key by 1:

foreach ($m_arr_query_parameters as $m_param_key => $m_param_value)
{
    $this->c_obj_stmt->bindValue($m_param_key, $m_param_value);
}

'PDOException': Invalid parameter number with message 'SQLSTATE[HY093]: number of bound variables does not match number of tokens'

1 Answers