How do I use pdo’s prepared statement for order by and limit clauses?

by

After using :

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I got the message :

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 ”0’, ’10” at line 1

So, when you use an array for execute, it consider your inputs as string which is not a good idea for LIMIT 

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT * FROM table ORDER BY :sort :dir LIMIT :start, :results";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':start', $_GET['start'], PDO::PARAM_INT);
$stmt->bindParam(':results', $_GET['results'], PDO::PARAM_INT);
$stmt->bindParam(':sort', $_GET['sort']);
$stmt->bindParam(':dir', $_GET['dir']);
$stmt->execute();

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($data);

More Related Contents:

  1. Can I bind an array to an IN() condition in a PDO query?
  2. PHP – Using PDO with IN clause array
  3. PDO Prepared Inserts multiple rows in single query
  4. PDO prepared statement – what are colons in parameter names used for?
  5. pdo prepared statements with wildcards
  6. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  7. Get last insert id after a prepared insert with PDO
  8. Insert multiple rows with PDO prepared statements
  9. Replacing mysql_* functions with PDO and prepared statements
  10. PHP PDO::bindParam() data types.. how does it work?
  11. Binding params for PDO statement inside a loop
  12. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  13. Calling stored procedure with Out parameter using PDO
  14. When *not* to use prepared statements?
  15. Which tokens can be parameterized in PDO prepared statements?
  16. Retrieve (or simulate) full query from PDO prepared statement
  17. PDO error: SQLSTATE[HY000]: General error: 2031
  18. Use of PDO in classes [duplicate]
  19. Error while using PDO prepared statements and LIMIT in query [duplicate]
  20. How do I use password hashing with PDO to make my code more secure? [closed]
  21. MySQLi prepared statements error reporting [duplicate]
  22. PDO’s query vs execute
  23. Unknown database error in PHP but it exists in PHPMyAdmin
  24. Trying to access array offset on value of type bool
  25. LIKE query using multiple keywords from search field using PDO prepared statement
  26. How to get a query error from prepare() in PDO PHP?
  27. PHP PDO how to run a multiple query request?
  28. PHP Laravel: No connection could be made because the target machine actively refused it
  29. Check to see if an email is already in the database using prepared statements
  30. PDO Exception Questions – How to Catch Them

Leave a Comment