PDOException: Invalid parameter number with message 'SQLSTATE[HY093]: Columns/Parameters are 1-based'

Question

This project is really tough. I'm trying to access a database with various fields, each has a unique variable; but I'm still getting "Columns are 1-based'

Stack Trace

Notice: exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based' in C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkDatabaseWrapper.php:80
Stack trace:
#0 C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkDatabaseWrapper.php(80): PDOStatement->bindParam(0, '447950491677', 2)
#1 C:\wamp\www\CTEC3110\includes\classes\cw_download_message_data\class.CourseWorkDownloadMessageDataModel.php(357): CourseWorkDatabaseWrapper->safe_query('SELECT id FROM ...', Array)
#2 C:\wamp\www\CTEC3110\includes\classes\cw_download_message_data\class.CourseWorkDownloadMessageDataModel.php(181): CourseWorkDownloadMessageDataModel->do_check_if_data_exists()
#3 C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkContainer.php(142): CourseWorkDownloadMessageDataModel->do_store_downloaded_message_data()
#4 C:\wamp\www\CTEC3110\includes\classes\cw_download_message_data\class.CourseWorkDownloadMessageDataController.php(15): CourseWorkContaine in C:\wamp\www\CTEC3110\includes\classes\cw_framework\class.CourseWorkDatabaseWrapper.php on line 102


$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_keys)
    {
        var_dump($m_param_keys);

        foreach ($m_param_keys as $m_param_key => $m_param_value)
        {
            $this->c_obj_stmt->bindParam($m_param_key, $m_param_value, 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;

where "$m_query_string" is:

SELECT id
FROM cw_messages
WHERE source = :source_terminal
    AND destination = :destination_terminal
    AND date = :message_date
    AND time = :message_time
    AND type = :message_type
    AND reference = :message_reference
    AND data = :message_data
LIMIT 1

Here are "$m_arr_query_parameters":

Array ( [:source_terminal] => Array ( [0] => 447950491677 [1] => 447950491677 ) [:destination_terminal] => Array ( [0] => 447817814149 [1] => 447817814149 ) [:message_date] => Array ( [0] => 14/08/2014 [1] => 14/08/2014 [2] => 2014-08-14 [3] => 2014-08-14 ) [:message_time] => Array ( [0] => 18:57:31 [1] => 19:48:28 ) [:message_type] => Array ( [0] => SMS [1] => SMS ) [:message_reference] => Array ( [0] => 0 [1] => 0 ) [:message_data] => Array ( [0] => Test 3 [1] => Test at 20:48 ) ) 

Here's the var dump of $m_param_keys:

array (size=2) 0 => string '447950491677' (length=12) 1 => string '447950491677' (length=12)

Answer 1

Marc B's answer was close, but in the end, the fix was actually more simple than I'd thought.

I was passing the $m_param_key through one too many foreach loops and needed to keep it on the outside loop.

Here's the resolved code:

// bind the parameters
if (sizeof($m_arr_query_parameters) > 0)
{   
  foreach ($m_arr_query_parameters as $m_param_key => $m_param_values)
    {
      foreach($m_param_values as $m_param_value)
        {
          $this->c_obj_stmt->bindParam($m_param_key, $m_param_value);
        }
    }
}

// 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;

Answer 2

Looking at your $m_param_keys dump, you are binding 2 parameters while i guess you try to bind 7 of them according to the prameter array you posted.

Also looking at the error : Notice: exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number

I can pretty much tell that if you bind all the parameters you are supposed to (all the ones in your array) you won't have an error anymore.

Edit, adding the code to it :

$params = array(  ':source_terminal' => $m_message_source,
            ':destination_terminal' => $m_message_destination,
            ':message_date' => $m_message_date,
            ':message_time' => $m_message_time,
            ':message_type' => $m_message_type,
            ':message_reference' => $m_message_reference,
            ':message_data' => $m_message_data);

foreach( $params as $key => $val )
    $this->c_obj_stmt->bindParam($key, $val, PDO::PARAM_STR);

Answer 3

Your dump shows $m_param_keys to be a numerically keyed array. You're essentially trying to do

->bindParam(0, 447950491677)

PDO parameters are numbered from 1, not from 0. And you're also using NAMED parameters, so binding by numeric offset is also not permitted. The bind call should be

->bindParam(':source_terminal', $m_message_source)