带消息'SQLSTATE [HY093]的PDOException'

Question

Error:

PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

<?php

require '../database.php';

$id = null;
if ( !empty($_GET['id'])) {
    $id = $_REQUEST['id'];
}

if ( null==$id ) {
    header("Location: index.php");
}



//
//echo "<pre>";
//echo print_r($_POST);
//echo "</pre>";
//
//die();


if ( !empty($_POST)) {
    // keep track validation errors
//    $pr_id_vinError = null;
    $pr_nomError = null;
    $pr_soustitreError = null;
    $pr_enligneError = null;
    $pr_millesimeError = null;
    $pr_descriptionError = null;
    $pr_typeError = null;
    $pr_prixError = null;
    $pr_ordreError = null;


    // keep track post values
    $pr_nom = $_POST['pr_nom'];
    $pr_id_vin = $_POST['pr_id_vin'];
    $pr_soustitre = $_POST['pr_soustitre'];
    $pr_enligne = $_POST['pr_enligne'];
    $pr_millesime = $_POST['pr_millesime'];
    $pr_description = $_POST['pr_description'];
    $pr_type = $_POST['pr_type'];
    $pr_prix = $_POST['pr_prix'];
    $pr_ordre = $_POST['pr_ordre'];
    $pr_couleur = $_POST['pr_couleur'];


    // validate input
    $valid = true;

//    if(empty($pr_id_vin)) {
//        $pr_id_vinError =  'Vieille selectione le nom du vin';
//        $valid = false;
//    }

    if (empty($pr_nom)) {
        $pa_nomError = 'Vieille entre le nom du produit';
        $valid = false;
    }

    if (empty($pr_soustitre)) {
        $pr_soustitreError = 'Vieille ente sous titre du produit';
        $valid = false;
    }

    if (empty($pr_enligne)) {
        $pr_enligneError = 'Vieille selectione  si il est en ligne ou non';
        $valid = false;
    }

    if (empty($pr_millesime)) {
        $pr_millesimeError = 'Vieille entre millesieme en chifre';
        $valid = false;
    }

    if (empty($pr_description)) {
        $pr_descriptionError = 'Vieille entre la description du vin';
        $valid = false;
    }

    if (empty($pr_type)) {
        $pr_typeError = 'Vieille entre le typ du produit';
        $valid = false;
    }

    if (empty($pr_prix)) {
        $pr_prixError = 'Vieille entre le du produit';
        $valid = false;
    }

    if (empty($pr_ordre)) {
        $pr_ordreError = 'Vieille entre ordre d\'affichage';
        $valid = false;
    }

//    var_dump($valid);die();

    // insert data
    if ($valid) {
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->exec("set names utf8");


//        echo "<pre>";
//        echo "<br>";
//        echo print_r($_POST);
//        echo "</pre>";
//
//        die();

        $sql = "UPDATE produit  set pr_id_vin, pr_nom = ?, pr_soustitre = ?, pr_enligne =?, pr_millesime =?, pr_description =?, pr_type =?, pr_prix =?, pr_ordre =?, pr_couleur =? WHERE pr_id = ?";
        $q = $pdo->prepare($sql);
        $q->bindParam(':pr_id_vin', $pr_id_vin, PDO::PARAM_INT);
        $q->bindParam(':pr_nom', $pr_nom, PDO::PARAM_STR, 255);
        $q->bindParam(':pr_soustitre', $pr_soustitre, PDO::PARAM_STR, 255);
        $q->bindParam(':pr_enligne', $pr_enligne, PDO::PARAM_STR, 1);
        $q->bindParam(':pr_millesime', $pr_millesime, PDO::PARAM_STR, 4);
        $q->bindParam(':pr_description', $pr_description, PDO::PARAM_STR);
        $q->bindParam(':pr_type', $pr_type, PDO::PARAM_STR);
        $q->bindParam(':pr_prix', $pr_prix, PDO::PARAM_STR);
        $q->bindParam(':pr_ordre', $pr_ordre, PDO::PARAM_INT);
        $q->bindParam(':pr_couleur', $pr_couleur, PDO::PARAM_STR);
        $q->bindParam(':pr_id', $id, PDO::PARAM_INT);
        $q->execute();


//        $q->execute(array($pr_id_vin, $pr_nom,$pr_soustitre,$pr_enligne,$pr_millesime,$pr_description,$pr_type,$pr_prix,$pr_ordre,$pr_couleur, $id));
        Database::disconnect();
        header("Location: index.php");
    }
}else {
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("set names utf8");
    $sql = "SELECT * FROM produit where pr_id = ?";
    $q = $pdo->prepare($sql);
    $q->execute(array($id));
    $data = $q->fetch(PDO::FETCH_ASSOC);
    $pr_id = $data['pr_id'];
    $pr_id_vin = isset( $data['pr_id_vin']) ? $data['pr_id_vin'] : '';   //A VOIRE DATA[]
    $pr_nom = $data['pr_nom'];
    $pr_soustitre = $data['pr_soustitre'];
    $pr_enligne = $data['pr_enligne'];
    $pr_millesime = $data['pr_millesime'];
    $pr_description = $data['pr_description'];
    $pr_type = $data['pr_type'];
    $pr_prix = $data['pr_prix'];
    $pr_ordre = $data['pr_ordre'];
    $pr_couleur = $data['pr_couleur'];
    Database::disconnect();
}
?>


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <link   href="../assets/css/bootstrap.min.css" rel="stylesheet">
    <script src="../assets/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="../tinymce/js/tinymce/tinymce.min.js"></script>
    <script type="text/javascript" src="../tinymce/js/tinymce/wysywig-myConfiguration.js"></script>
</head>

<body>
<div class="container">

    <div class="span10 offset1">
        <div class="row">
            <h3>Mis a jour produit</h3>
        </div>

        <form class="form-horizontal" action="update.php?id=<?php echo $pr_id?>" method="post">
            <div class="control-group <?php echo !empty($pr_id_vinError)?'error':'';?>">
                <label class="control-label">Choisissez nom du vin </label>
                <div class="controls">
                    <select class="form-control" name="pr_id_vin">
                        <?php
                        try
                        {
                            $pdo = Database::connect();
                            $pdo->exec("set names utf8");
                            $result = $pdo->query("SELECT pk_producto, nombre FROM ta_productos");
                            foreach($result as $row)
                            {
                                echo '<option value="'.$row['pk_producto'].'"';
                                if($row['pk_producto']== isset($pr_id_vin))
                                {
                                    echo ' selected';
                                }
                                echo '>'. $row['nombre'] . '</option>'."
";
                            }
                        }
                        catch(PDOException $e)
                        {
                            echo 'Pas de resultat';
                        }
                        ?>
                    </select>
                    <?php if (!empty($pr_nomError)): ?>
                        <span class="help-inline"><?php echo $pr_id_vinError;?></span>
                    <?php endif; ?>
                </div>
            </div>
            <div class="control-group <?php echo !empty($pr_nomError)?'error':'';?>">
                <label class="control-label">Nom du produit</label>
                <div class="controls">
                    <input name="pr_nom" type="text"  placeholder="Nom du prduit" value="<?php echo !empty($pr_nom)?$pr_nom:'';?>">
                    <?php if (!empty($pr_nomError)): ?>
                        <span class="help-inline"><?php echo $pr_nomError;?></span>
                    <?php endif; ?>
                </div>
            </div>
            <div class="control-group <?php echo !empty($pr_soustitreError)?'error':'';?>">
                <label class="control-label">Sous tite du produit</label>
                <div class="controls">
                    <textarea  name="pr_soustitre"  value="<?php echo !empty($pr_soustitre)?$pr_soustitre:'';?>"><?php echo !empty($pr_soustitre)?$pr_soustitre:'';?></textarea>
                    <?php if (!empty($pr_soustitreError)): ?>
                        <span class="help-inline"><?php echo $pr_soustitreError;?></span>
                    <?php endif;?>
                </div>
            </div>
            <div class="control-group">
                <label class="control-label">Choisir si en ligne ou non</label>
                <div class="controls">
                    <label class="radio-inline"><input type="radio" id="enLigne" name="pr_enligne" value="Y"> En ligne</label>
                    <label class="radio-inline"><input type="radio" id="horsLigne" name="pr_enligne" value="N" checked="checked"> Hors ligne</label>
                    <?php
                    if (strpos($pr_enligne,'Y') !== false) {
                        echo "<script>";
                        echo "document.getElementById('enLigne').checked = true;";
                        echo "</script>";
                    }else {
                        echo "<script>";
                        echo "document.getElementById('horsLigne').checked = true;";
                        echo "</script>";
                    }
                    ?>
                </div>
            </div>
            <div class="control-group <?php echo !empty($pr_millesimeError)?'error':'';?>">
                <label class="control-label">Millesime</label>
                <div class="controls">
                    <input name="pr_millesime" type="number"  placeholder="Millesime du produit" value="<?php echo !empty($pr_millesime)?$pr_millesime:'';?>">
                    <?php if (!empty($pr_millesimeError)): ?>
                        <span class="help-inline"><?php echo $pr_millesimeError;?></span>
                    <?php endif;?>
                </div>
            </div>
            <div class="control-group <?php echo !empty($pr_descriptionError)?'error':'';?>">
                <label class="control-label">Description du produit</label>
                <div class="controls">
                    <textarea  name="pr_description"  value="<?php echo !empty($pr_description)?$pr_description:'';?>"><?php echo !empty($pr_description)?$pr_description:'';?></textarea>
                    <?php if (!empty($pr_descriptionError)): ?>
                        <span class="help-inline"><?php echo $pr_descriptionError;?></span>
                    <?php endif;?>
                </div>
            </div>
            <div class="control-group <?php echo !empty($pr_typeError)?'error':'';?>">
                <label class="control-label">Type du produit</label>
                <div class="controls">
                    <input name="pr_type" type="text"  placeholder="Typ du produit" value="<?php echo !empty($pr_type)?$pr_type:'';?>">
                    <?php if (!empty($pr_typeError)): ?>
                        <span class="help-inline"><?php echo $pr_typeError;?></span>
                    <?php endif;?>
                </div>
            </div>
            <div class="control-group <?php echo !empty($pr_prixError)?'error':'';?>">
                <label class="control-label">Prix</label>
                <div class="controls">
                    <input name="pr_prix" type="number" step=0.01 placeholder="Insere le prix" value="<?php echo !empty($pr_prix)?$pr_prix:'';?>">
                    <?php if (!empty($pr_prixError)): ?>
                        <span class="help-inline"><?php echo $pr_prixError;?></span>
                    <?php endif;?>
                </div>
            </div>
            <div class="control-group <?php echo !empty($pr_ordreError)?'error':'';?>">
                <label class="control-label">Ordre d'affichage</label>
                <div class="controls">

                    <?php
                    $selected = '';
                    echo '<select name="pr_ordre">',"
";
                    for($i=0; $i<=150; $i++)
                    {
                        if($i === 1)
                        {
                            $selected = ' selected="selected"';
                        }
                        echo "\t",'<option value="', $i ,'"', $selected ,'>', $i ,'</option>',"
";
                        $selected='';
                    }
                    echo '</select>',"
";
                    ?>

                </div>
            </div>
            <div class="control-group">
                <label class="control-label">Couleur</label>
                <div class="controls">
                    <?php
                    $arrayCouleurs = array(

                        '#ff0000' => 'rouge',
                        '#ffffff' => 'blanc',
                        '#FD6C9E' => 'Rose'
                    );
                    $selected = '';
                    echo '<select name="pr_couleur">',"n";
                    foreach($arrayCouleurs as $valeurHexadecimale => $nomCouleur)
                    {
                        if($nomCouleur === 'rouge')
                        {
                            $selected = ' selected="selected"';
                        }
                        echo "\t",'<option value="', $nomCouleur ,'"', $selected ,'>', $nomCouleur ,'</option>',"
";
                        $selected='';
                    }
                    echo '</select>',"
";
                    ?>
                </div>
            </div>
            <input type="hidden" name="id" value="<?php echo isset($id) && !empty($id) ? $id : 0 ?>">
            <div class="form-actions">
                <button type="submit" class="btn btn-success">Mis a jour</button>
                <a class="btn" href="index.php">Return</a>
            </div>
        </form>
    </div>

</div> <!-- /container -->
</body>
</html>

I'm using almost the same code for product creation and it is working fine.
I was looking for some other topics on StackOverflow but with no positive result.
I'm not sure what is going on.
I think that I have to bind every param.
Can someone tell my what I have been missing, please?

Answer 1

There are a few things wrong here.

Firstly, you missed the parameter for the SET for UPDATE produit set pr_id_vin,

Then you're mixing ? and : placeholder types.

Since I cannot be sure of what your column names are, I will let you fill those in by following this example:

$query = "UPDATE table SET col_1 = :col_1, col_2 = :col_2 WHERE col_x = :col_x";

Reference:

• http://php.net/pdo.prepared-statements

Sidenote: As Fluffeh might have thought and commented on, this is not an undocumented feature.

Upon testing this myself, was also faced with the same error while adding the missing ?.

• Using :var must be used.

---

Edit: My "thought" did not work upon further testing.

A thought:

However and what you could do is use and basing yourself on Example #2 of the manual:

$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

and changing it to:

$q->bindParam('1', $pr_id_vin, PDO::PARAM_INT);
$q->bindParam('2', $pr_nom, PDO::PARAM_STR, 255);
...

etc., but I couldn't be 100% sure about, but you can try it.