-3
$orderby = 'title';
$order = 'ASC';

$stmt = $conn->prepare('SELECT f.id, title, description, release_year, first_name, last_name FROM film AS f, film_actor AS fa JOIN actor AS a WHERE fa.actor_id = a.actor_id AND fa.film_id = f.film_id ORDER BY :orderby :order LIMIT :limit');
$stmt->execute(
    array(  'order'=>$order, 
            'orderby'=>$orderby, 
            'limit'=>$limit
        )
    );

I'm getting the error described in the title when trying to run this query. I've tried to find a way to debug the output but failed. I'll appreciate the help. PS: I'm learning SQL using PDO in PHP.

Thanks in advance.

bruhbruh
  • 313
  • 1
  • 14

2 Answers2

0

You forgot : here

array(  'order'=>$order, 
        'orderby'=>$orderby, 
        'limit'=>$limit
    )

should be

array(  ':order'=>$order, 
        ':orderby'=>$orderby, 
        ':limit'=>$limit
    )
Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100
  • Thanks, i've made that change but I still get the same error. Here's the corrected code: `$stmt = $conn->prepare('SELECT f.id, title, description, release_year, first_name, last_name FROM film AS f, film_actor AS fa JOIN actor AS a WHERE fa.actor_id = a.actor_id AND fa.film_id = f.film_id ORDER BY :orderby :order LIMIT :limit'); $stmt->execute( array( ':order'=>$order, ':orderby'=>$orderby, ':limit'=>$limit) );` – bruhbruh May 13 '13 at 06:21
  • @talentedunicorn show the full error. – Yogesh Suthar May 13 '13 at 06:22
  • Here's the full error: `DB Error: 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 ''200'' at line 1` – bruhbruh May 13 '13 at 06:26
  • @talentedunicorn where you have assigned `200` in your query? I can't see it. – Yogesh Suthar May 13 '13 at 06:29
  • @YogeshSuthar, i think 200 is `limit` because he forgot to put `,` between two `order by` columns – Amir May 13 '13 at 06:31
  • Oh yeah sorry guys there was a variable `$limit = 200;` in my code. – bruhbruh May 13 '13 at 06:34
0

It seems that $stmt->execute() will add quotes around the $limit value hence 200 will be '200' resulting to an error.

A suggested solution is to use bindParam() Look here for more info http://php.net/manual/en/pdostatement.execute.php

bruhbruh
  • 313
  • 1
  • 14