Announcement

Collapse
No announcement yet.

Faster, safer queries using PDO

Collapse
This is a sticky topic.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Faster, safer queries using PDO

    What’s PDO & why should you use it?

    PDO stands for ‘Prepared data objects’ and uses object-oriented features available from PHP 5 and onwards to run database queries.
    It’s compatible with many databases including MSQL, DB2, MySQL, Oracle, ODBC, PostgreSQL & SQLite meaning you won’t have to write new queries should you ever change database.
    Preparing data in this way is more secure, faster & easier to write. No more worrying about escaping strings & SQL injection attacks!
    To check if you can use PDO, create a new PHP page with the following line:
    PHP Code:
    <?php
         print_r
    (PDO::getAvailableDrivers());
    ?>
    This will tell you what (if any) PDO drivers are available on your system.

    Getting started.

    First, we need to make a connection (I’m using mysql drivers for this example).
    PHP Code:
    <?php 
        $host 
    "Your host name, usually localhost";
        
    $dbname "Your database name";
        
    $username "Your database username";
        
    $password "Your database password";
        
    $dbh = new PDO("mysql:host=$host;dbname=$dbname"$username$password);
    ?>
    Or you can put your connection variables in directly -
    PHP Code:
    <?php
        $dbh 
    = new PDO("mysql:host=Your host name;dbname=Your database name""Your database username""Your database password");
    ?>

    I like to put mine in a function and also ‘catch’ any connection errors and write the details to a text file:
    PHP Code:
    <?php
    function db_conn()
    {
         try{
              
    //make a connection
              
    $dbh = new PDO("mysql:host=localhost;dbname=dbname""username""password");
              
    //set the error mode – see http://php.net/manual/en/pdo.error-handling.php
              
    $dbh->setAttribute (PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
         }
         catch (
    PDOException $e)
         {
              echo 
    "Error connecting to database."
              
    //write the error to a log file
              
    file_put_contents('PDOErrors.txt'$e->getMessage(), FILE_APPEND);  
         }
    return 
    $dbh;
    }
    ?>
    Now, when we want to assign our database handler, we can just use:
    PHP Code:
    <?php
        $dbh 
    db_conn();
    ?>
    Selecting, Inserting & Updating records (Prepare – Bind Values – Execute):

    There are several methods of binding values ready for inserting the data, see -

    http://php.net/manual/en/pdostatement.bindvalue.php
    http://www.php.net/manual/en/pdostatement.bindparam.php
    http://www.php.net/manual/en/pdostat...bindcolumn.php

    Using named placeholders -
    PHP Code:
    <?php
    $query  
    $dbh->prepare("SELECT id, username, email FROM users WHERE email = :email AND password = :pw");
    $query->bindValue(":email"trim($_POST['email']) );
    $query->bindValue(":pw"sha1(trim($_POST['password'])) );
    $query->execute();
    $row $query->fetch(PDO::FETCH_ASSOC);

    //OR execute using an array

    $query  $dbh->prepare("SELECT id, username, email FROM users WHERE email = :email AND password = :pw");
    $query->execute(array(":email" => trim($_POST['email']), ":pw" => sha1(trim($_POST['password']))  ));
    $row $query->fetch(PDO::FETCH_ASSOC);
    ?>
    Using question mark placeholders -
    PHP Code:
    <?php
    $query  
    $dbh->prepare("SELECT id, username, email FROM users WHERE email = ? AND password = ?");
    $query->bindValue(1trim($_POST['email']) );
    $query->bindValue(2sha1(trim($_POST['password'])) );
    $query->execute();
    $row $query->fetch(PDO::FETCH_ASSOC);
    ?>
    You can also specify the type & length of value to bind, e.g. –
    PHP Code:
    <?php
        $query
    ->bindValue(1trim($_POST['email']),  PDO::PARAM_STR50  );
    ?>
    Looping through results
    PHP Code:
    <?php
        $query 
    $dbh->prepare("SELECT CONCAT_WS(' ', title, firstname, lastname) AS name FROM users WHERE usertype = :utype");
        
    $query->execute(array(":utype" => (int)$_POST['usertype'] ));
        While(
    $row $query->fetch(PDO::FETCH_ASSOC))
        {
            echo 
    $row['name'].'<br />';
        }
    ?>
    Inserting data is just as simple –
    PHP Code:
    <?php 
        $query 
    $dbh->prepare("INSERT INTO users (username, email) VALUES (:uname, :email)");
        
    $query->execute(array(":uname" => trim($_POST['username']), ":email" => trim($_POST['email']) ));
    ?>
    Getting the last inserted ID and looping through posted variables.

    Let’s say we have a form with an email field and an array of colours -
    HTML Code:
    <input type = "email" id="user_email" name="user_email" />
    <input type = "checkbox" id="red" name="colours[]" value="red" />
    <input type = "checkbox" id="blue" name="colours[]" value="blue" />
    <input type = "checkbox" id="green" name="colours[]" value="green" />
    <input type = "checkbox" id="yellow" name="colours[]" value="yellow" />
    And we want to insert the email into a database table called ‘emails’ and then insert the colours into a database table called ‘colours’, adding the last inserted ID from the emails table to each of the colours –
    PHP Code:
    <?php 
        
    If($_POST)
        {
              
    //insert the email address
              
    $email_query $dbh->prepare("INSERT INTO emails (email) VALUES (:email) ");
              
    $email_query->execute(array(":email" => trim($_POST['email']) ));

              
    //get the last inserted ID
              
    $last_id $dbh->lastInsertId();

              
    //prepare the colour query
              
    $insert_colours $dbh->prepare("INSERT INTO colours (email_id, colour) VALUES (:eid, :colour)");

              
    //loop through the posted colours and execute the prepared colours query
              
    foreach($_POST['colours'] as $value)
              {
                  
    $insert_colours-> execute(array(":eid" => $last_id":colour" => $value));
              }
    }
    ?>
    Using LIKE %%

    Using LIKE in a PDO query is a little different than what we're used to in that we cannot just do this -
    PHP Code:
    "SELECT email FROM users WHERE email LIKE %:email%" 
    There are a couple of ways of achieving this, my personal favourite is using CONCAT -
    PHP Code:
    <?php
    $query 
    $dbh->prepare("SELECT email FROM users WHERE email LIKE CONCAT ('%', :email, '%')");
    $query->bindValue(":email"trim($_POST['email']);
    ?>
    or
    PHP Code:
    <?php
    $email 
    "%".trim($_POST['email'])."%";
    $query $dbh->prepare("SELECT email FROM users WHERE email LIKE :email");
    $query->bindValue(":email"$email);
    ?>
    How many rows were affected?

    If you want to get the number of rows affected by a prepared statement, use –
    PHP Code:
    <?php
        $count 
    $query->rowCount();
    ?>
    Now you've got no excuse for writing unsafe queries!

    Here are a few articles on PDO to wet your appetite even further –

    http://net.tutsplus.com/tutorials/ph...tabase-access/
    http://mattbango.com/notebook/web-de...hp-and-mysqli/
    http://www.kitebird.com/articles/php-pdo.html

    *Note - the above code examples are untested, if you spot any typo's, please let me know.
    Last edited by Reiss; 04-13-2012, 04:48 AM.

  • #2
    Hey Reiss,

    I was just wondering about closing the connection afterwards. I have checked on php.net and I cant find anything regarding how to connections are closed. I don't want to just assume that the PDO class has a destructor that closes the connection automatically, just in case it doesn't?

    Thanks Marcus.

    If your thread has been solved by a member of HTMLForums or if you managed to solve the problem yourself, could you please mark the thread as Resolved.

    Comment


    • #3
      If you've assigned your connection like so -
      PHP Code:
      $dbh = new PDO("mysql:host=$host;dbname=$dbname"$username$password); 
      to close it, you would set $dbh to NULL
      PHP Code:
      $dbh NULL  //close connection 

      Comment


      • #4
        Originally posted by chuckymong View Post
        I was just wondering about closing the connection afterwards. I have checked on php.net and I cant find anything regarding how to connections are closed.
        It's on the "Connections and Connection management" page, Example #3 Closing a connection

        I don't want to just assume that the PDO class has a destructor that closes the connection automatically, just in case it doesn't?
        Also on this page

        The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

        JFYI
        Domain Registrar Domaintechnik® | Hosting | Domains | SSL certificate | Webspace Austria

        Comment

        Working...
        X