Database connection using PDO

0
2048

In this section we will be using Database connection using PDO (PHP Data Objects).

  • To Connect the database using PDO follow the steps given below :

  1. Install Xampp server and if it is already installed then go to the xampp control pannel and start the Apache and MySQL modules .
  2. First we have to create our database through which we can access the data.
  3. Now open up your web browser and enter the given url http://localhost/phpmyadmin/
  4. The following window will be displayed :
  5. 0.1

  6. Now fill the appropriate details inside it and click on go button.
  7. After clicking the Go button following window will get displayed :
  8. 0.2

  9. Now to create the database go to the following link as shown in the picture below:
  10. 0.3

  11. After clicking on the SQL tab following window will appear.
  12. 0.4

  13. Now create database with following command:
  14. Create DATABASE company;

    0.5

  15. So now after clicking on the GO button your database will be created at the left hand side as shown in the image :
  16. 0.6

  17. Now write the following command to create the database and insert values into the table :
  18. CREATE TABLE IF NOT EXISTS `employees` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `first_name` varchar(255) NOT NULL,
      `last_name` varchar(255) NOT NULL,
      `department` varchar(255) NOT NULL,
      `email` varchar(255) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
    
    INSERT INTO `employees` (`id`, `first_name`, `last_name`, `department`, `email`) VALUES
    (1, 'Kevin', 'Jones', 'Design', '[email protected]'),
    (2, 'Tim', 'Laker', 'Programming', '[email protected]'),
    (3, 'Jason', 'Thomas', 'Design', '[email protected]'),
    (4, 'Mark', 'Spencer', 'Marketing', '[email protected]'),
    (5, 'Lisa', 'Roddick', 'Programming', '[email protected]'),
    (6, 'James', 'Smith', 'Marketing', '[email protected]'),
    (9, 'Jack', 'Daniel', 'Design', '[email protected]'),
    (11, 'David', 'Johnson', 'Design', 'djohnson');

  19. Thus you will have the following view of your window :
  20. 0.7

  21. So now click on the Go button and you will have your database created with the inserted data :
  22. 2222222

  23. Now go to Xampp -> htdocs folder and create a new folder say Pdo .
  24. 1

  25. Now in this Pdo folder create a new file say index.php
  26. 1212121

  27. Now open the index.php file in any text editor and write the following code for the connection with the database.
  28. <?php
    try{
        //connect to database
        $dbh = new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','1234');
    } catch (PDOException $e){
        echo $e->getMessage();
    }
    ?>

  29. Here is the screenshot of the editor :
  30. 2

  31. Now we have to write an SQL query and fetch it and display the output in the table format , so replace the index.php file with the following code :
  32. <?php
    try{
        //connect to database
        $dbh = new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','1234');
    } catch (PDOException $e){
        echo $e->getMessage();
    }
    ?>
    
     <?php
    
     /* SELECT DATA */
    
    //Create Query
    $sth = $dbh->query("SELECT * FROM employees");  
    
    //Set Fetch Mode - Array/Object/Num
    $sth->setFetchMode(PDO::FETCH_OBJ);  
    
     ?>
    
    <table width="500" cellpadding=5 cellspacing=5 border=1>
    	<tr>
    		<th>ID#</th>
    		<th>First Name</th>
    		<th>Last Name</th>
    		<th>Dept</th>
    		<th>Email</th>
    	</tr>
    	<?php while($row = $sth->fetch()) : ?>
    		<tr>
    			<td><?php echo $row->id; ?></td>
    			<td><?php echo $row->first_name; ?></td>
    			<td><?php echo $row->last_name; ?></td>
    			<td><?php echo $row->department; ?></td>
    			<td><?php echo $row->email; ?></td>
    		</tr>
    	<?php endwhile; ?>
    </table>

  33. After editing the index.php file you will have the following view of your text editor window :
  34. 4

  35. Now enter the following url to get the output http://localhost/Pdo/
  36. 5.0

  37. So now lets insert some data into it , so replace the code in index.php file with following one :
  38. <?php
    try{
        //connect to database
        $dbh = new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','1234');
    } catch (PDOException $e){
        echo $e->getMessage();
    }
    ?>
    
    <?php
    
    //INSERT DATA 
    
    //Create Dummy Variables
    
    $first_name = "Maverick";
    $last_name = "Jones";
    $department = "Programming";
    $email = "[email protected]";
    
    //Create Statement
    $sth = $dbh->prepare("INSERT INTO employees (first_name,last_name,department,email) VALUES (:first_name,:last_name,:department,:email)");
    
    //Bind Values
    $sth->bindParam(':first_name', $first_name);  
    $sth->bindParam(':last_name', $last_name);  
    $sth->bindParam(':department', $department);  
    $sth->bindParam(':email', $email);  
    
    //Execute
    $sth->execute();

  39. Here is the screen shot of the text editor :
  40. 5.1

  41. So now run the index.php file by entering the url as http://localhost/Pdo .It will automatically insert data into the database and you will have the following output :
  42. 6

  43. Now suppose you want to perform some update operations on your database for example : change the department and email of the first_name Lisa .
  44. 7

  45. So replace the code of index.php file with the following one :
  46. <?php
    try{
        //connect to database
        $dbh = new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','1234');
    } catch (PDOException $e){
        echo $e->getMessage();
    }
    ?>
    
    <?php
    
    // UPDATE DATA 
    
    //Create Dummy Variables
    $id = 12;
    $dept = "Programming";
    $email = "[email protected]";
    
    //Create Statement
    $sth = $dbh->prepare("UPDATE employees SET department = :department,email = :email WHERE id = :id");
    
    //Bind Values
    $sth->bindParam(':id', $id);   
    $sth->bindParam(':department', $dept);  
    $sth->bindParam(':email', $email);  
    
    //Execute
    $sth->execute();
    ?>

  47. You will have the following view of your text editor window :
  48. 8

  49. Now to run it just enter the url as http://localhost/Pdo , you will have the following output :
  50. 9

  51. Now let’s delete some row from the database for example : Suppose if we want to delete the id = 12.
  52. 10

  53. Now replace the code of index.php with the following one :
  54. <?php
    try{
        //connect to database
        $dbh = new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','1234');
    } catch (PDOException $e){
        echo $e->getMessage();
    }
    ?>
    
    /* DELETE DATA */
    //Create Dummy Variables
    $id = 12;
    
    //Create Statement
    $sth = $dbh->prepare("DELETE FROM employees1 WHERE  id = :id");
    
    //Bind Values
    $sth->bindParam(':id', $id);    
    
    //Execute
    $sth->execute();
    
    //Close Connection 
    $dbh = null;  
    
    ?>

  55. Screen shot of the code is shown below :
  56. 11

  57. Now to get the output enter the following url http://localhost/Pdo/ in the browser :
  58. 12

  59. Thus in this section we have done all the CRUD operations i.e. create, read , update and delete statement with the help of PDO.

LEAVE A REPLY

Please enter your comment!
Please enter your name here