prepared parameterized query with PDO

by

To create the connection

try {
    $db = new PDO("mysql:dbname=".DB_NAME.";host=".DB_HOST,DB_USER,DB_PWD);
} catch (PDOException $e) {
    die("Database Connection Failed: " . $e->getMessage());
}

Then to prepare a statement

$prep = $db->prepare("SELECT * FROM `users` WHERE userid = ':id'");

As you can see, you label each parameter you’d like by prefixing any string with ‘:’. Then all you do is pass an array mapping the parameter (:id) to the value when you execute.

if (!$prep->execute(array(":id" => $userinput))) {
   $error = $prep->errorInfo();
   echo "Error: {$error[2]}"; // element 2 has the string text of the error
} else {
   while ($row = $prep->fetch(PDO::FETCH_ASSOC)) { // check the documentation for the other options here
        // do stuff, $row is an associative array, the keys are the field names
   }
}

Instead of PDO::FETCH_ASSOC with the “fetch” function, there are various other ways to get your data. You can use fetchAll to get an array of ALL the results at once instead of just going row by row. Or you can get the array of information as a 0-indexed array, or you can even fetch the results directly into a class instance (if the field names line up with the properties of the class.)

All the documentation of PDO can be found here: PHP.net PDO Manual

More Related Contents:

  1. Can I mix MySQL APIs in PHP?
  2. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  3. mysqli or PDO – what are the pros and cons? [closed]
  4. How to prevent duplicate usernames when people register?
  5. What is the difference between MySQL, MySQLi and PDO? [closed]
  6. PHP PDO and MySQLi [duplicate]
  7. Unknown database error in PHP but it exists in PHPMyAdmin
  8. PHP: mysql v mysqli v pdo [closed]
  9. What is the difference between bindParam and bindValue?
  10. How do I create a PDO parameterized query with a LIKE statement?
  11. PDOException SQLSTATE[HY000] [2002] No such file or directory
  12. How to get numeric types from MySQL using PDO?
  13. PDO prepared statement – what are colons in parameter names used for?
  14. php pdo: get the columns name of a table
  15. PDO bindParam vs. execute
  16. parameters in MySQLi
  17. PDO error message? [duplicate]
  18. Mysql No connection could be made because the target machine actively refused it
  19. Replacing mysql_* functions with PDO and prepared statements
  20. How do I sanitize input with PDO?
  21. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  22. Bind Param with array of parameters
  23. Setting a connect timeout with PDO
  24. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  25. PDO MySQL: Insert multiple rows in one query
  26. PDO LIMIT and OFFSET [duplicate]
  27. mysqli_connect(): (HY000/2002): No connection could be made because the target machine actively refused it
  28. I’m getting this error code: Invalid argument supplied for foreach() [duplicate]
  29. How do I ensure I caught all errors from MySQLi::multi_query?
  30. Difference between PDO->query() and PDO->exec()

Leave a Comment