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:
- 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 :
- 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.
- 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 :
- 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 :
- 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 :
- 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 :
- Screen shot of the code is shown below :
- Now to get the output enter the following url http://localhost/Pdo/ in the browser :
- Thus in this section we have done all the CRUD operations i.e. create, read , update and delete statement with the help of PDO.
Create DATABASE company;
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');
<?php try{ //connect to database $dbh = new PDO('mysql:host=localhost;dbname=company;charset=utf8','root','1234'); } catch (PDOException $e){ echo $e->getMessage(); } ?>
<?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>
<?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();
<?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(); ?>
<?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; ?>