Getting "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined"

Question

I have searched thread after thread with this error and everyone's error conceptually boils down to a parameter missing or misspelled. I have probably spent collectively around 4-5 hours trying to find the solution. I specifically made an account for this problem.

This is the error:

ERROR: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Whatever is wrong, my professors are stumped as well. Anyway here are some snippets of my code:

NOTE: I set "location" equal to "todo-description"'s content because I have not formatted my HTML page with a location box yet.

SOLUTION: the hyphen in "due-date" is an illegal character.

    $data = array(
        'todo-name' => array('name' => 'Todo Item Name','value' => '', 'errors' =>array()),
        'todo-list' => array('name' => 'Todo List', 'value' => '', 'errors' => array()),
        'todo-due-date' => array('name' => 'Due Date', 'value' => '','errors' => array()),
        'todo-location' => array('name' => 'Location', 'value' => '','errors' => array()),
        'todo-priority' => array('name' => 'Priority', 'value' => '','errors' => array()),
        'todo-description' => array('name' => 'Description', 'value' => '','errors' => array()) 
        );

    if($total_errors == 0)
{
    $date = DATETIME::createFromFormat('m/d/Y', $data['todo-due-date']['value']);
    $date = $date->format('Y-m-d');
    $data['todo-due-date']['value'] = $date;

    $query = "insert into todo_item(name, due_date, list, priority, location, description)
        values(:name, :due-date, :list, :priority, :location, :description);";

    $statement = $db->prepare($query);
    try
    {
        $statement->execute(array(
                'name' => $data['todo-name']['value'],
                'due-date' => $data['todo-due-date']['value'],
                'list' => $data['todo-list']['value'],
                'priority' => $data['todo-priority']['value'],
                'location' => $data['todo-description']['value'],
                'description' => $data['todo-description']['value']
                ));

        if($statement)                  
        {
            $position = $db->lastInsertId();
            header("Location: item.php?id=$position");
        }
    }
    catch(Exception $ex)
    {
        die("Could not execute query: {$ex->getMessage()}");
    }
}
else
{
    die('error');
}

I guess it must still work, but I'd advise sticking to the correct case for class names - it's part of the attention to detail that helps weed out issues in general. Thus, I'd change `DATETIME` to `DateTime`. — halfer, Apr 13 '14 at 22:35
Is it possible that a hyphen is not allowed in a parameter name? — wvdz, Apr 13 '14 at 22:41
Aha, good spot @popovits, it is almost certainly the `due-date`. That should be renamed with an underscore. — halfer, Apr 13 '14 at 22:42
OP, also your `header()` needs to be immediately followed by an `exit`, otherwise you may get PHP executed before the client has a chance to act on the redirect request. — halfer, Apr 13 '14 at 22:47
Possible duplicate [PDO valid characters for placeholders](http://stackoverflow.com/questions/5809951/pdo-valid-characters-for-placeholders) — Sean, Apr 13 '14 at 22:49

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

According to the answer to PDO valid characters for placeholders (https://stackoverflow.com/a/5810058/689579)
and
http://lxr.php.net/xref/PHP_5_3/ext/pdo/pdo_sql_parser.re#49

Valid parameter chars

BINDCHR = [:][a-zA-Z0-9_]+;

So your hypenated parameter 'due-date' is invalid

edited May 23 '17 at 11:44

Community

1
1

answered Apr 13 '14 at 22:43

Sean

12,443
3
29
47

I knew another pair of eyes could solve the problem. Thank you. – Richard Apr 15 '14 at 12:10

score -2 · Answer 2 · answered Apr 13 '14 at 22:31

-2

You 've forgotten something. Just have a look:

$statement->execute(array(
    ':name' => $data['todo-name']['value'],
    ':due-date' => $data['todo-due-date']['value'],
    ':list' => $data['todo-list']['value'],
    ':priority' => $data['todo-priority']['value'],
    ':location' => $data['todo-description']['value'],
    ':description' => $data['todo-description']['value']
));

Got it? ;)

answered Apr 13 '14 at 22:31

Marcel

4,854
1
14
24

1

I don't know if this is the problem - apparently [the colon prefix is not necessary](http://stackoverflow.com/a/19774177/472495) in the execute statement (I'm not the downvoter, though). – halfer Apr 13 '14 at 22:39
1

@Marcel Dont be a SA, just tell him/her what is wrong. – RiggsFolly Apr 13 '14 at 23:18

Getting "SQLSTATE[HY093]: Invalid parameter number: parameter was not defined"

2 Answers2