PHP PDO to Connect MySQL Database Tutorial

Furqan Aziz | 28 Oct 2013 | Tutorials

PHP PDO to Connect MySQL Database Tutorial
Social Box
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.
  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. For any query/suggestions please post a comment on Blog.
LATEST TUTORIALS
PHP MySQL Simple Delete using PDO in Bootstrap Tutorial
Convert Simple Text To 3D Text using Adobe Fireworks CS6 Tutorial
PHP Simple Update using PDO in Bootstrap Tutorial
PHP Simple Insert using PDO in Bootstrap Tutorial
PHP MySQL Easy CSV Data Insertion in Bootstrap using PDO Tutorial
PHP Easy Sign-in Using PDO Prepared Statement Tutorial
Easy PHP Email With Attachments using Swift Mailer Tutorial
Easy Pagination with PDO in PHP plus Export to Excel Tutorial
PHP PDO to Connect MySQL Database Tutorial
Best CSS Frameworks Collection For Rapid Web Development
PHP-MySQL Ajax Jquery-UI Autocomplete in Bootstrap Tutorial
PHP Simple Sign-in Tutorials
Jquery Raty Usage via PHP
PHP Dynamic Select options Via Ajax
CSV Importer in MySQL
PHP File Load Via Jquery into DIV
© 2014 CreWow Network - All Rights Reserved.