PHP PDO to Connect MySQL Database Tutorial

This tutorial will show you how to connect to MySQL Database using PDO (PHP Data Objects) which defines a lightweight, consistent interface for accessing databases in PHP.

Website : Crewow Website

1. First we will create sample table. Below is a code for Sample MySQL table named category, you can copy it and paste it in your query window inside database say shopdb and run the query to create table.

• --
  -- Table structure for table `category`
  --
  CREATE TABLE IF NOT EXISTS `category` (
    `SrNo` int(11) NOT NULL AUTO_INCREMENT,
    `Category` varchar(50) NOT NULL,
    `SubCategory` varchar(100) NOT NULL,
    PRIMARY KEY (`SrNo`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
  --
  -- Dumping data for table `category`
  --
  INSERT INTO `category` (`SrNo`, `Category`, `SubCategory`) VALUES
  (1, 'Electronics', 'Mobile'),
  (2, 'Electronics', 'Tablet'),
  (3, 'Electronics', 'IPad'),
  (4, 'Electronics', 'ITab'),
  (5, 'Electronics', 'Television'),
  (6, 'Electronics', 'Camera'),
  (7, 'Electronics', 'Laptop'),
  (8, 'Electronics', 'LCD'),
  (9, 'Electronics', 'Computer'),
  (10, 'Books', 'Magazines'),
  (11, 'Books', 'Journals'),
  (12, 'Books', 'Course Books'),
  (13, 'Garments', 'Kids Clothes');

2. Now its time to connect to MySQL Database using PHP Data Objects. Create new PHP File and write following lines for connection to MySQL Database.

• <?php
  // mysql hostname
  $hostname = 'localhost';
  // mysql username
  $username = 'username';
  // mysql password
  $password = 'password';
  // Database Connection using PDO
  $dbh = new PDO("mysql:host=$hostname;dbname=shopdb", $username, $password);
  // $dbh is a Database Handle
  // new is to create PDO Object
  // mysql is a Database type
  // host=$hostname;dbname=shopdb",$username,$password is a connection string
  // Closing MySQL database connection
      $dbh = null;
  ?>

3. If there are any connection errors, a PDOException object will be thrown. You can catch the exception if you want to handle the error condition. we will use try/catch statement.

• <?php
  // mysql hostname
  $hostname = 'localhost';
  // mysql username
  $username = 'username';
  // mysql password
  $password = 'password';
  // Database Connection using PDO
  try {
  $dbh = new PDO("mysql:host=$hostname;dbname=shopdb", $username, $password);
  // Closing MySQL database connection
      $dbh = null;
      }
  catch(PDOException $e)
      {
      echo $e->getMessage();
      }
  //If we will not use catch statement, then in case of error zend engine terminate the script and display a back trace. This back trace will likely reveal the full database connection details, including the username and password.  
  ?>

4. The connection remains active for the lifetime of that PDO object. To close the connection, you will assign NULL to the variable($dbh) that holds the object.

• <?php
  // Closing MySQL database connection
      $dbh = null;  
  ?>

5. Now we will Select data from MYSQL Database using prepared statement. These statements helps to protect from SQL Injection. There are two methods to use prepared statements. one is named and second in unnamed. we will use one of them here.

• <?php
  // mysql hostname
  $hostname = 'localhost';
  // mysql username
  $username = 'username';
  // mysql password
  $password = 'password';
  // Database Connection using PDO
  try {
  $dbh = new PDO("mysql:host=$hostname;dbname=shopdb", $username, $password);
  // Define Variables
      $SrNo = 6;
      $Category = 'Electronics';
  // We Will prepare SQL Query
      $STM = $dbh->prepare("SELECT * FROM category WHERE SrNo = :SrNo AND Category = :Category");
  // bind paramenters, Named paramenters alaways start with colon(:)
      $STM->bindParam(':SrNo', $SrNo);
      $STM->bindParam(':Category', $Category);
  // For Executing prepared statement we will use below function
      $STM->execute();
  // we will fetch records like this and use foreach loop to show multiple Results
      $STMrecords = $STM->fetchAll();
      foreach($STMrecords as $row)
          {
          echo $row['SrNo'].'-'.$row['Category'].'-'.$row['SubCategory'];
          }
  // Closing MySQL database connection   
      $dbh = null;
      }
  catch(PDOException $e)
      {
      echo $e->getMessage();
      }  
  ?>

You can also check Connections and Connection management for more details about PDO Connection.