PHP - MYSQL - SQLSTATE [HY093]：参数号无效[关闭]

Question

i have little problem with inserting data to my database. I get this error but i have no idea way. I have almost identical query on other script and it works fine but this one does not. I check the POST data on chrome inspector it shows fine.

if you have idea whats wrong with it, let me know. Thanks in Advance.

$fact_total = (float)$_POST['precio'];
 $fact_btax = (float)$_POST['precio_sin'];
 $fact_tax = (float)$_POST['impuestos'];
 $fact_name = e($_POST['fact_name']);
 $fact_tipo = e($_POST['fact_serv']);

  $fact_tax_rate = 21;

    try{
      $handler = $db->prepare('INSERT INTO fact_info
        (id_client, cl_name, cl_last_name, cl_last_name_2, cl_email, cl_tel, cl_doc_type, cl_doc, cl_via, cl_street, cl_number, cl_level, cl_stairs, cl_door, cl_provincia, cl_city, cl_cod_postal, fact_urgencia, fact_name, fact_tipo, fact_total, fact_btax, fact_tax, fact_tax_rate, created ) VALUES (:id_client, :cl_name, :cl_last_name, :cl_last_name_2, :cl_email, :cl_tel, :cl_doc_type, :cl_doc, :cl_via, :cl_street, :cl_number, :cl_level, :cl_stairs, :cl_door, :cl_provincia, :cl_city, :cl_cod_postal, :fact_urgencia, :fact_name, :fact_tipo, :fact_total, :fact_btax, :fact_tax, :pres_tax_rate, NOW())');
      $handler->execute(array(
        ':id_client' => $client_ids,
        ':cl_name' => e($_POST['fact_cl_name']),
        ':cl_last_name' => e($_POST['fact_lastname']),
        ':cl_last_name_2' => e($_POST['fact_lastname_2']),
        ':cl_email' => e($_POST['fact_email']),
        ':cl_tel' => e($_POST['fact_tel']),
        ':cl_doc_type' => e($_POST['fact_document_type']),
        ':cl_doc' => e($_POST['fact_document_number']),
        ':cl_via' => e($_POST['fact_dir_via']),
        ':cl_street' => e($_POST['fact_dir_calle']),
        ':cl_number' => (int)$_POST['fact_dir_number'],
        ':cl_level' => e($_POST['fact_dir_level']),
        ':cl_stairs' => e($_POST['fact_dir_stairs']),
        ':cl_door' => e($_POST['fact_dir_door']),
        ':cl_provincia' => e($_POST['fact_dir_provincia']),
        ':cl_city' => e($_POST['fact_dir_localidad']),
        ':cl_cod_postal' => (int)$_POST['fact_dir_cod_postal'],
        ':fact_urgencia' => '1',
        ':fact_name' => $fact_name,
        ':fact_tipo' => $fact_tipo,
        ':fact_total' => $fact_total,
        ':fact_btax' => $fact_btax,
        ':fact_tax' => $fact_tax,
        ':fact_tax_rate' => $fact_tax_rate
      ));

    $fact_id = $db->lastInsertId();

    foreach ($_POST['inv_desc'] as $key => $value) {
        $handler4 = $db->prepare('INSERT INTO fact_content (id_fact, fact_desc, fact_qty, fact_price, fact_subtotal) VALUES (:id_fact, :fact_desc, :fact_qty, :fact_price, :fact_subtotal)');
        $handler4->execute(array(
          ':id_fact' => $fact_id,
          ':fact_desc' => e($_POST['inv_desc'][$key]),
          ':fact_qty' => (float)$_POST['inv_qty'][$key],
          ':fact_price' => (float)$_POST['inv_precio'][$key],
          ':fact_subtotal' => (float)$_POST['inv_subtotal'][$key]
          ));
    }

      header('Location: fact_confirm.php?fact_id='.$fact_id.'');
      exit();

Answer 1

I'll explain you why this error occurs.

SQLSTATE[HY093]: Invalid parameter number

This error basically occurs because of the following reasons.

Reason1

As the error name suggests, there is some difference between the number of parameters in your prepared query. What I mean is, take a look at the following example:

$s = $conn->prepare("INSERT INTO table(column1,column2) values(:column1)
$s->bindParam(':column1', $column1Value);
$s->bindParam(':column2', $column2Value);

Now, this will generate the error you mentioned because you're trying to insert into 2 columns, but are only providing value for 1.

Reason2

$s = $conn->prepare("INSERT INTO table(column1) values(:column1,:column2)
$s->bindParam(':column1', $column1Value);
$s->bindParam(':column2', $column2Value);

Now this would generate an error because you're trying to insert value into one column, but providing 2 values inside the VALUE section of the query.

Reason3

$s = $conn->prepare("INSERT INTO table(column1,column2) values(:column1,:column2)
$s->bindParam(':column1', $column1Value);

In this case, you've written the query part correct. However, you missed binding the parameters for the second value, the :column2.

These are the reasons why this error bumps up. Check your code and you'd find out what error out of these 3 you've made.