PDO error with variable in LIMIT

Asked

Viewed 171 times

1

In PDO today was the first time I had to put a variable in LIMIT, and I’m getting this error:

Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[HY093]: Invalid Parameter number: number of bound variables does not match number of tokens' in filename.php:85 Stack trace: #0 name-doarquivo.php(85): Pdostatement->execute(Array) #1 {main} thrown in name-doarquivo.php on line 85

The line 85 is this:

$sql2->execute(array(":idQuiz" => $idQuiz));

My code:

$perguntaQuiz = 2;
$sql2 = $pdo->prepare('SELECT * FROM quiz_pergunta WHERE idquiz = :idQuiz ORDER BY id ASC LIMIT 0, :perguntaQuiz');
$sql2->bindParam(':perguntaQuiz', $perguntaQuiz, PDO::PARAM_INT); 
$sql2->execute(array(":idQuiz" => $idQuiz));

What am I missing?

mysql pdo
  • 1

    The (int) is not necessary.

  • Now gave another error... vo edit

  • 1

    Just like @zekk said, remove the cast. If you really need it, which in case doesn’t seem necessary, do it previously outside the bindParam.

  • I don’t get it @Marcelodeandrade

  • In your code exemplified in the question, there is no need for cast (int) since php will already interpret the value as an integer when you did $perguntaQuiz = 2;

  • I got it, I changed it, but there was another mistake.... I edit the question!

  • 1

    Duplicate of Number of "bound variables" does not match the number of "tokens" ?

  • you left the same code as the answers?

Show 3 more comments

2 answers

4


The error informs that one of the reported parameters has no value to be replaced. Let’s see:

$perguntaQuiz = 2;
$sql2 = $pdo->prepare('SELECT * FROM quiz_pergunta WHERE idquiz = :idQuiz ORDER BY id ASC LIMIT 0, :perguntaQuiz');
$sql2->bindParam(':perguntaQuiz', $perguntaQuiz, PDO::PARAM_INT); 
$sql2->execute(array(":idQuiz" => $idQuiz));

In your select you inform the Binds :idQuiz and :perguntaQuiz but there is only one method bindParam declared, you inserted the second, within the method execute.

Change to: 

$perguntaQuiz = (int)2;
$idQuiz = (int)2;

$sql2 = $pdo->prepare('SELECT * FROM quiz_pergunta WHERE idquiz = :idQuiz ORDER BY id ASC LIMIT 0, :perguntaQuiz');
$sql2->bindParam(':perguntaQuiz', $perguntaQuiz, PDO::PARAM_INT); 
$sql2->bindParam(':idQuiz', $idQuiz, PDO::PARAM_INT); 
$sql2->execute();

  • Thank you, now for the other error: Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ''1'' at line 1' in.php:87 Stack trace: #0 file.php(87): Pdostatement->execute() #1 {main} in.php file on line 87 Line 87: $sql2->execute();

  • You set the value for the variable $idQuiz?

  • Yes, if I take the limit, or put a fixed value in the limit, the error does not occur. That is, the idquiz is coming right

  • Where is the value of $idQuiz? Is static?

  • tries to cast an int in the bindParam of :perguntaQuiz. $sql2->bindParam(':perguntaQuiz', (int)$perguntaQuiz, PDO::PARAM_INT); , and checks whether $idQuiz and mainly $perguntaQuiz are coming as int, if it comes as string can from this error.

  • So with @Neuberoliveira said, force the cast with (int) and see if the problem continues.

  • If you set the int, it switches to this error: Fatal error: Cannot pass Parameter 2 by Reference in file.php on line 85

  • I edited that they are, do the cast before as I had previously commented.

  • @Marcelodeandrade now forcing before was that was! Funny that are values that comes from the database, from an INT field....

Show 4 more comments

3

This error means that Voce did not pass the values to all markers.

$sql2 = $pdo->prepare('SELECT * FROM quiz_pergunta WHERE idquiz = :idQuiz ORDER BY id ASC LIMIT 0, :perguntaQuiz');

The markers are these parameters that start with :, in case you have 2 of them :idQuiz and :perguntaQuiz the preference PDO to the parameters you pass in the execute(), as in the execute() has only 1 :idQuizhe gives that mistake.

You have to pass the valors on one or the other.

In bindParam

$sql2->bindParam(':perguntaQuiz', $perguntaQuiz, PDO::PARAM_INT); 
$sql2->bindParam(':idQuiz', $idQuiz, PDO::PARAM_INT);

Or in the execute()

$sql2->execute(array(":perguntaQuiz"=>$perguntaQuiz, ":idQuiz" => $idQuiz));

  • Also of the error.... Thank you, now for the other error: Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ''1'' at line 1' in.php file:87 Stack trace: #0 file.php(87): Pdostatement->execute() #1 {main} in file.php on line 87 Line 87: $sql2->execute();

Browser other questions tagged mysql pdo

You are not signed in. Login or sign up in order to post.