Syntax error while executing a sql request with parameters in symfony?

Question

I have a just simple problem I guess, I followed Symfony 4.3 documentation, and tried to do do exactly what they have done to execute a SQL request, but I get an error when passing parameters in the execute method, but I get no error while executing the same code but without passing parameters to the request.

I tried this and it worked:

    $conn = $this->getDoctrine()->getManager()->getConnection();

    $sql = '
        SELECT * FROM question_comment WHERE question=2 LIMIT 0, 3';
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    var_dump($stmt->fetchAll());

But this does not work, I get a syntax error:

    $question = $request->query->get('question');
    $row = $request->query->get('row');

    $question = intval($question);
    $beggining = intval($row*3);

    $conn = $this->getDoctrine()->getManager()->getConnection();

    $sql = '
        SELECT * FROM question_comment WHERE question=:question LIMIT :beggining , 3';
    $stmt = $conn->prepare($sql);
    $stmt->execute(['question' => $question, 'beggining' => $beggining]);
    var_dump($stmt->fetchAll());

This is the error:

An exception occurred while executing ' SELECT * FROM question_comment WHERE question= :question LIMIT :beggining , 3' with params [2, 0]:

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 ''0' , 3' at line 1

Here is what I have followed, I did exactly what was there, but its not working.

I tried this solution to bind my parameters but it did not work too :

 $sql = '
            SELECT * FROM question_comment WHERE question=:question LIMIT :beggining , 3';
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':question', $question, \PDO::PARAM_INT); 
        $stmt->bindParam(':beginning', $beginning, \PDO::PARAM_INT); 
        $stmt->execute();

And I got this :

An exception occurred while executing ' SELECT * FROM question_comment WHERE question=:question LIMIT :beggining , 3' with params [2, null]:

SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

try this $stmt->execute([':question' => $question, ':beggining' => $beggining]); — Ed Bangga
– Ed Bangga, Commented Aug 28, 2019 at 1:47
i believe symfony is using PDO, might want to change this to $stmt->bindParam(':question', $question, PDO::PARAM_STR); — Ed Bangga
– Ed Bangga, Commented Aug 28, 2019 at 1:56
seems this is null $row = $request->query->get('row');, what are the columns of this table question_comment — Ed Bangga
– Ed Bangga, Commented Aug 28, 2019 at 2:08

Ed Bangga · Accepted Answer · 2019-08-28 02:23:05Z

3

alternative way to bind parameters is using this.

 $stmt->bindParam(':question', $question, PDO::PARAM_STR); 
 $stmt->bindParam(':beginning', $beginning, PDO::PARAM_INT);

or to make it sure you binded the $beginning parameter, cast it to int.

$stmt->bindValue(':beginning', (int)trim($beginning), PDO::PARAM_INT);

or if you want to stick on your existing code.

$stmt->execute(['question' => $question, 'beggining' => (int)trim($beggining)]);

edited Aug 28, 2019 at 2:23

answered Aug 28, 2019 at 1:57

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ed Bangga Over a year ago

its because your $request->query->get('row') is null

TaouBen Over a year ago

No, surely not that, I commented my code causing the problem and checked the $row variable with var_dump function and I get it right

TaouBen Over a year ago

I did change PARAM_STR to PARAM_INT and same things, it is weird, I am making sure that $question and $begginig are integer with intval() method but same things :(

TaouBen Over a year ago

It worked, I mispelled something above, but your bindParams method was really helpful, thank you.

Collectives™ on Stack Overflow

Syntax error while executing a sql request with parameters in symfony?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related