PHP - MySQL error : Invalid parameter number: parameter was not defined

Question

I am trying to use a simple MySQL insert query with the parameters in array form. It keeps telling me the number of parameters are wrong :

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Here is my code

try
{
$bdd = new PDO('mysql:host=localhost;dbname=looktallshoes;charset=utf8', 
'root', '');
}
catch(Exception $e)
{
    die('Erreur : '.$e->getMessage());
}

$req = $bdd -> prepare("INSERT INTO products (product-title, product-
category, product-source, source-link, product-price, price-before-discount, 
product-source-price, height-increase, admin-product-short-description, 
admin-product-long-description, large-main-name, square-main-name, other-
photo-1-name, other-photo-2-name, other-photo-3-name, other-photo-4-name) 

VALUES(:product-title, :product-category, :product-source, :source-link, 
:product-price, :price-before-discount, :product-source-price, :height-
increase, :admin-product-short-description, :admin-product-long-description, 
:large-main-name, :square-main-name, :other-photo-1-name, :other-photo-2-
name, :other-photo-3-name, :other-photo-4-name)");

    $req->execute(array(
    'product-title'=>$_POST['product-title'],
    'product-category'=>$_POST['product-category'],
    'product-source'=>$_POST['product-source'],
    'source-link'=>$_POST['source-link'],
    'product-price'=>$_POST['product-price'],
    'price-before-discount'=>$_POST['price-before-discount'],
    'product-source-price'=>$_POST['product-source-price'],
    'height-increase'=>$_POST['height-increase'],
    'admin-product-short-description'=>$_POST['admin-product-short-
    description'],
    'admin-product-long-description'=>$_POST['admin-product-long-
     description'],
    'large-main-name'=>$_POST['large-main-name'],
    'square-main-name'=>$_POST['square-main-name'],
    'other-photo-1-name'=>$_POST['other-photo-1-name'],
    'other-photo-2-name'=>$_POST['other-photo-2-name'],
    'other-photo-3-name'=>$_POST['other-photo-3-name'],
    'other-photo-4-name'=>$_POST['other-photo-4-name'],
    ));

You shouldn't use dashes for anything other than subtracting (doing math) in MySQL. — Qirel, Sep 10 '17 at 14:20
For the column names that have dashes, quote each one using backticks. — Sloan Thrasher, Sep 10 '17 at 14:29

score 1 · Accepted Answer · answered Sep 10 '17 at 14:34

When you use a hyphen (-) in MySQL, it thinks you are doing mathematics - subtracting one thing from another. So using column-names with hyphens is a bad idea in itself, but it's possible to work around. However, your placeholders needs to be changed. A valid pattern is [:][a-zA-Z0-9_]+, which means you can use alphanumeric values and underscore. For columns, they need to be wrapped in backticks. It'd look something like this

$req = $bdd->prepare("INSERT INTO products (`product-title`, `product-
category`, `product-source`, `source-link`, `product-price`, `price-before-discount`, 
`product-source-price`, `height-increase`, `admin-product-short-description`, 
`admin-product-long-description`, `large-main-name`, `square-main-name`, `other-
photo-1-name`, `other-photo-2-name`, `other-photo-3-name`, `other-photo-4-name`) 

VALUES(:product_title, :product_category, :product_source, :source_link, 
:product_price, :price_before_discount, :product_source_price, :height_increase, 
:admin_product_short_description, :admin_product_long_description, 
:large_main_name, :square_main_name, :other_photo_1_name, :other_photo_2_name, 
:other_photo_3_name, :other_photo_4_name)");

 $req->execute(array(
    'product_title' => $_POST['product-title'],
    'product_category' => $_POST['product-category'],
    'product_source' => $_POST['product-source'],
    'source_link' => $_POST['source-link'],
    'product_price' => $_POST['product-price'],
    'price_before_discount' => $_POST['price-before-discount'],
    'product_source_price' => $_POST['product-source-price'],
    'height_increase' => $_POST['height-increase'],
    'admin_product_short_description' => $_POST['admin-product-short-description'],
    'admin_product_long_description' => $_POST['admin-product-long-description'],
    'large_main_name' => $_POST['large-main-name'],
    'square_main_name' => $_POST['square-main-name'],
    'other_photo_1_name' => $_POST['other-photo-1-name'],
    'other_photo_2_name' => $_POST['other-photo-2-name'],
    'other_photo_3_name' => $_POST['other-photo-3-name'],
    'other_photo_4_name' => $_POST['other-photo-4-name'],
));

PHP - MySQL error : Invalid parameter number: parameter was not defined

1 Answers1