why sql give me error "Invalid parameter number: parameter was not defined"

Question

Today I got error Invalid parameter number: parameter was not defined in my yii app while updating data . then i come to know that my sql database table column contains name with "-" symbol i.e "table-post" etc .

Then i changed "-" with "_" and everything is working .

This is the query snippet (I replaced the "-" with "_")

/* percentage  losses  senser*/
    $attributes['totlcommloss_sensor']  = $_POST['totlcommloss_sensor'];
    $attributes['asp_hour_sensor']= $_POST['asp-hour_sensor'];
    $attributes['asp_daily_sensor'] = $_POST['asp-daily_sensor'];
    $attributes['asp_weekly_sensor']= $_POST['asp-weekly_sensor'];
    $attributes['asp_monthly_sensor'] = $_POST['asp-monthly_sensor'];
    $attributes['asp_5_day_senser']= $_POST['asp_5_day_senser'];

    /* cost losses */
    //$attributes['costlosshourly'] = $_POST['acs-hourly'];

    if (0 != intval($user['id'])) {         
        $command->update('alarm_settings', $attributes, 'id=:id', array(':id' => intval($user['id'])));
            } 
    else {
        $NumberOfRowsEffected = $command->insert('alarm_settings', $attributes);
         }

Can some one explain why this example shows error? Many thanks in advance.

Answer 1

In your case, the '-' in your table name will be seen as a calculation and not as part of the actual table name. The reason behind this is that MySQL can only give one function to a character/word, and does not know which one you actually want to use at that particular spot in your query.

In MySQL a lot of these special characters or reserved words exist. Every time you use one of those, backticks have to be used.

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

You can use characters beyond that set as table or column identifiers, including whitespace for example, but then you must quote (backtick) them.

In your case using `table-post` (backticks!) instead of table-post should work

Answer 2

Based on mysql documentation on Schema Object Names Only following characters are permitted as non quoted identifiers.

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

Extended: U+0080 .. U+FFFF

- ANSI(45) isn't allowed as a valid identifier unless it is quoted with backticks (``) or double quotes("") which are ANSI SQL compatible with ANSI_QUOTES

Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:

ASCII: U+0001 .. U+007F