Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]:

Question

Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:\wamp\www\pegasus\classes\generic.class.php on line 68
class Add_consum extends Generic {

private $result;
private $error;
private $impexp;
private $sea_air;
private $customername;
private $customerreference;
private $homeconsumption;
private $invoicevalue;
private $currency;
private $mbl_hbl;
private $vesselname;
private $lcl_fcl;
private $noofcontainers;
private $sft_containers;
private $fcl_containerno;
private $weight;
private $noofpallets;
private $itemdescripion;
private $suppliersdetails;
private $cif_fob;
private $blnumer;
private $bl_date;

function __construct() {

    if(isset($_POST['searchimpexp'])) {
        $this->searchimpexp();
        exit();
    }
// jQuery form validation
    parent::checkExists();

    if(isset($_POST['add_consum'])) {
        $this->impexp = parent::secure($_POST['impexp']);
        $this->sea_air = parent::secure($_POST['sea_air']);
        $this->customername = parent::secure($_POST['customername']);
        $this->customerreference = parent::secure($_POST['customerreference']);
        $this->homeconsumption = parent::secure($_POST['homeconsumption']);
        $this->invoicevalue = parent::secure($_POST['invoicevalue']);
        $this->currency = parent::secure($_POST['currency']);
        $this->mbl_hbl = parent::secure($_POST['mbl_hbl']);
        $this->vesselname = parent::secure($_POST['lcl_fcl']);
        $this->lcl_fcl = parent::secure($_POST['customerreference']);
        $this->noofcontainers = parent::secure($_POST['noofcontainers']);
        $this->sft_containers = parent::secure($_POST['sft_containers']);
        $this->fcl_containerno = parent::secure($_POST['fcl_containerno']);
        $this->weight = parent::secure($_POST['weight']);
        $this->noofpallets = parent::secure($_POST['noofpallets']);
        $this->itemdescripion = parent::secure($_POST['itemdescripion']);
        $this->suppliersdetails = parent::secure($_POST['suppliersdetails']);
        $this->cif_fob = parent::secure($_POST['cif_fob']);
        $this->blnumer = parent::secure($_POST['blnumer']);
        $this->bl_date = parent::secure($_POST['bl_date']);

        // Confirm all details are correct
        $this->verify();

        // Create the user
        $this->addconsum();

        if(!empty($this->error)) parent::displayMessage($this->error);
            else echo $this->result;
        exit();

    }

}

/** @todo: Should be in a different class, not add_consum. */
private function searchimpexp() {

    if(empty($_POST['searchimpexp'])) return false;

    $sql = array( ':searchQ' => $_POST['searchimpexp'] . '%' );
    $sql = "SELECT distinct username as suggest, user_id
            FROM login_users
            WHERE username LIKE :searchQ
            OR name LIKE :searchQ
            OR user_id LIKE :searchQ
            ORDER BY username
            LIMIT 0, 5";

    $stmt = parent::query($sql);

    if ( $stmt->rowCount() < 1 ) {
        echo '<h3>' . _('No suggestions') . '</h3>
              <p class="help-block">' . _('Try searching by username, name, or user id.') . '</p>';
        return false;
    }

    echo '<h2>' . _('Suggestions') . '</h2>';

    while($suggest = $stmt->fetch(PDO::FETCH_ASSOC))
        echo "<p><a href='users.php?uid=" . $suggest['user_id'] . "'>" . $suggest['suggest'] . "</a></p>\n";

}

// Return a value if it exists public function getPost($var) {

    if(!empty($this->$var)) {
        return $this->$var;
    } else return false;

}

private function verify() {

    if(empty($this->impexp)) {
        $this->error = '<div class="alert alert-error">'._('You must enter a impexp.').'</div>';
        return false;
    }

    if(empty($this->sea_air)) {
        $this->error = '<div class="alert alert-error">'._('You must enter a seaair.').'</div>';
        return false;
    }

    if(empty($this->customername)) {
        $this->error = '<div class="alert alert-error">'._('You must enter a customername.').'</div>';
        return false;
    }



}

private function addconsum() {

    if (!empty($this->error)) return false;

    $params = array(
        ':impexp'           => $this->impexp,
        ':sea_air'          => $this->sea_air,
        ':customername'     => $this->customername,
        ':homeconsumption'  => $this->customerreference,
        ':homeconsumption'  => $this->homeconsumption,
        ':invoicevalue'     => $this->invoicevalue,
        ':currency'         => $this->currency,
        ':mbl_hbl'          => $this->mbl_hbl,
        ':vesselname'       => $this->vesselname,
        ':lcl_fcl'          => $this->lcl_fcl,
        ':noofcontainers'   => $this->noofcontainers,
        ':sft_containers'   => $this->sft_containers,
        ':fcl_containerno'  => $this->fcl_containerno,
        ':weight'           => $this->weight,
        ':noofpallets'      => $this->noofpallets,
        ':itemdescripion'   => $this->itemdescripion,
        ':suppliersdetails' => $this->suppliersdetails,
        ':cif_fob'          => $this->cif_fob,
        ':blnumer'          => $this->blnumer,
        ':bl_date'          => $this->bl_date,
    );

parent::query("INSERT INTO add_consignment (impexp, sea_air, customername, customerreference, homeconsumption, invoicevalue, currency, mbl_hbl, vesselname, lcl_fcl, noofcontainers, sft_containers, fcl_containerno, weight, noofpallets, itemdescripion, suppliersdetails, cif_fob, blnumer, bl_date ) VALUES (:impexp, :sea_air, :customername, :customerreference, :homeconsumption, :invoicevalue, :currency, :mbl_hbl, :vesselname, :lcl_fcl, :noofcontainers, :sft_containers, :fcl_containerno, :weight, :noofpallets, :itemdescripion, :suppliersdetails, :cif_fob, :blnumer, :bl_date);", $params); }

} $addconsum = new Add_consum();

Answer 1

The code:

    $sql = array( ':searchQ' => $_POST['searchimpexp'] . '%' );
    $sql = "SELECT distinct username as suggest, user_id
            FROM login_users
            WHERE username LIKE :searchQ
            OR name LIKE :searchQ
            OR user_id LIKE :searchQ
            ORDER BY username
            LIMIT 0, 5";

    $stmt = parent::query($sql);

will only set

$stmt = parent::query("SELECT distinct username as suggest, user_id
                FROM login_users
                WHERE username LIKE :searchQ
                OR name LIKE :searchQ
                OR user_id LIKE :searchQ
                ORDER BY username
                LIMIT 0, 5");

The $sql that sets the array is useless, because it's overwritten when $sql sets the select-statement.

Invalid parameter number: number of bound variables does not match number of tokens means that nr of the variables inside the query isn't the number of values given to the PDO-object. I'm not sure what the function parent::query does but a prepare-statement should be used.

You will have to do something like

<?php

$statement = parent::query-prepare("SELECT distinct username as suggest, user_id
                    FROM login_users
                    WHERE username LIKE :searchQ
                    OR name LIKE :searchQ
                    OR user_id LIKE :searchQ
                    ORDER BY username
                    LIMIT 0, 5");
$statement->execute(array(':searchQ' => $_POST['searchimpexp'] . '%'));     
$row = $statement->fetchAll();

?>

Answer 2

Your problem appears to be that your are not binding the values to your query:

$stmt = parent::query($sql);
$stmt->bindValue( ':searchQ', $_POST['searchimpexp'], PDO::PARAM_STR );