SQLSTATE [HY093]: Invalid parameter number problem in insert

Question

SQLSTATE [HY093]: Invalid parameter number problem in insert

Navigation

#1 by (3 votes)
#2 by (2 votes)

2

I need to insert the following data through a form:

TITLE
DESCRIPTION
PRICE

HTML:

    <?php 
session_start();
session_destroy(); 
?>
<html>
<head>
    <meta charset="UTF-8">
    <title></title>
</head>
<body>
<form action="inserir.php" method="post" target="_self">
    <label for="email">Titulo:</label><br>
    <input type="text" id="titulo" name="titulo" value="">
    <br>

    <label for="senha">Descrição:</label><br>
    <input type="text" id="descricao" name="descricao" value="">
    <br>
    <label for="senha">Preço:</label><br>
    <input type="text" id="preco" name="preco" value="">
    <br>
    <button type="submit" id="acao" name="acao" value="cadastrar">Cadastrar</button>
</form>
</body>
</html>

INSERIR.PHP

    <?php 
try { 

$titulo = $_REQUEST['titulo'];
$descricao = $_REQUEST['descricao'];
$preco = $_REQUEST['preco'];

    $pdo = new PDO('mysql:host=localhost;dbname=diner', 'root', ''); 
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
    $stmt = $pdo->prepare('INSERT INTO prato(titulo, descricao, preco) VALUES(:titulo, :descricao, :preco)'); 
    $stmt->execute(array( ':titulo' => '$titulo' )); 
    $stmt->execute(array( ':descricao' => '$descricao' )); 
    $stmt->execute(array( ':preco' => '$preco' )); 
    echo $stmt->rowCount();
     } catch(PDOException $e) { 
        echo 'Error: ' . $e->getMessage();
?>

Since then I'm no longer successful .. the following error appears: Error: SQLSTATE [HY093]: Invalid parameter number: number of bound variables does not match number of tokens

My table is: DISH and has the columns: prato_id = int = primary title = varchar description = varchar price = varchar I know I need to generate an ID number, because it will not be by the user, and I do not know how to do it.

Second, after doing this, how do I make a table list printed in HTML? showing the items registered?

and finally delete some id.

I am 2 days running the internet looking to learn about CRUD and PDO, but I pack it.

php pdo crud

asked by anonymous 17.05.2015 / 10:12

2 answers

2

I've solved it as follows:

In the database, I changed the id_prato column to auto-increment .

The code looks like this:

<?php

try {

$pdo = new PDO('mysql:host=localhost;dbname=diner', 'root', ''); 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$pdo = $pdo->prepare('INSERT INTO prato VALUES(DEFAULT, :titulo, :descricao, :preco)'); //Preparando os dados
$pdo->bindValue( ':titulo' , $_REQUEST['titulo']); // recebendo dados do formulario
$pdo->bindValue( ':descricao' , $_REQUEST['descricao']); 
$pdo->bindValue( ':preco' , $_REQUEST['preco']); 
$pdo->execute(); // salvando no banco
echo $pdo->rowCount(); // retorna quantas linhas foram alteradas.
 } catch(PDOException $e) { 
    echo 'Error: ' . $e->getMessage();}

? >

17.05.2015 / 12:01

Knowing if the page is in the background Error in multiplication and division (java)

score 3 · Accepted Answer

SQLSTATE [HY093]: Invalid parameter number: number of bound variables does not match number of tokens

It means that the number of parameters passed in the query is not equal to the number of columns or vice versa. In your code, there are 3 queries that pass only one parameter.

$stmt->execute(array(':titulo' => '$titulo')); 
$stmt->execute(array(':descricao' => '$descricao')); 
$stmt->execute(array(':preco' => '$preco'));

There are two ways to bind between placeholders and values, the first is to specify each value individually with bindValue() or bindParam() The difference between these two methods is that in the first it is allowed to pass values directly while the second only accepts references (variables) return of function or method generate error: Cannot pass parameter 2 by reference

Form with bindValue / bindParam

$stmt = $pdo->prepare('INSERT INTO prato(titulo, descricao, preco) VALUES(:titulo, :descricao, :preco)'); 
$stmt->bindValue(':titulo', $titulo)); 
$stmt->bindValue(':descricao', $descricao)); 
$stmt->bindValue(':preco', $preco));
$stmt->execute();

Shape with execute

With execute() it is possible to pass all parameters at once through an array, it is very useful in dynamic queries.

$stmt = $pdo->prepare('INSERT INTO prato(titulo, descricao, preco) VALUES(:titulo, :descricao, :preco)');
$stmt->execute(array(':titulo' => $titulo, ':descricao' => $descricao, ':preco' => $preco));