Database connection using PDO

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 :

Install Xampp server and if it is already installed then go to the xampp control pannel and start the Apache and MySQL modules .

First we have to create our database through which we can access the data.

Now open up your web browser and enter the given url http://localhost/phpmyadmin/

The following window will be displayed :

Now fill the appropriate details inside it and click on go button.

After clicking the Go button following window will get displayed :

Now to create the database go to the following link as shown in the picture below:

After clicking on the SQL tab following window will appear.

Now create database with following command:

Create DATABASE company;

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

Now write the following command to create the database and insert values into the table :

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');

Thus you will have the following view of your window :

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

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

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

Now open the index.php file in any text editor and write the following code for the connection with the database.

<?php
try{
    //connect to database
    $dbh = new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','1234');
} catch (PDOException $e){
    echo $e->getMessage();
}
?>

Here is the screenshot of the editor :

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 :

<?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>

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

Now enter the following url to get the output http://localhost/Pdo/

So now lets insert some data into it , so replace the code in index.php file with following one :

<?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();

Here is the screen shot of the text editor :

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 :

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

So replace the code of index.php file with the following one :

<?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();
?>

You will have the following view of your text editor window :

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

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

Now replace the code of index.php with the following one :

<?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;  

?>