CRUD Operation by PDO in PHP

0
339

Download Source Code

Create-Read-Update-Delete (CRUD) operation using PHP Data Object (PDO)

Introduction

In this tutorial I want to present Create-Read-Update-Delete (CRUD) operation using PHP Data Object (PDO). Most PHP programmers have discovered how to use MySQLi and MySQL extensions. However, PHP Data Objects(PDO) offer a ways to work with database access layer providing a uniform method of access to multiple databases which make work much easier. It does not support syntaxes specific to databases, but it permits relatively seamless switching between different platforms and databases, which can simply be done by changing the connection string.

Class Declaration

PDO provide a way to design a way to php script by the Object Oriented Programming way where you need to define/create your own classes where the class contains bunch of method, property.

Here I have created a class name MyPDO that is extended from PDO class. Inside the class MyPDO I have initialized the property Hostname, Port number, Database name, User, Database Password and the connection string property name $connection that’s visibility mode is public by which any methods inside the database class can be accessed. Also I have declared some public methods like constructor, destructor, CreateTable, fetchAll, Insert, Delete and Update inside the class MyPDO to perform the CRUD operation.

And at the end of the class MyPDO an object is created to access the method inside the class.

Create Database

Here I have create a database (manually) in the phpmyadmin.

Create Database “test”

Database Connection

All objects can have a special built-in method called a ‘constructor’. Constructors allow you to initialize your object’s properties when you instantiate an object. If you create a __construct(); on the execution PHP will automatically call the __construct() when you create an object from your class.

To connect the MySQLi database server using PDO, I want to call a constructor method. When Constructor method is called that established connection with the Database (that you previously created in the MySQL Database Server). The constructor function has established the connection with the database and it is return the connection is ok or not.

Create Table

Next you need to create table within database to store data. Which table you want to create that’s name should be assign in the variable $tablename. Already I have assigned a constant property PRAM_table where a table name is assigned.

A query is assigned in the variable $TableSql by which a table automatically created in the database.

And then, CreateTable method is declared with the class object.

CreateTable method accept the argument $TableSql and inside the method CreateTable the sql query is executed using the exec() function. After completing the execution of this function automatically table will be created in the database.

Create Table “people” within Database “test”

Insert Operation

Here I want to describe the Insert Operation.

To implement the insert operation, I have declared an associative array which array index is table column and value is data for the table column. Here each index name should be double quoted and value of the associative array should have following rules:-

  • String values must be quoted
  • Numeric values must not be quoted
  • The word NULL must not be quoted

Here I want to insert three value for firstname, lastname and email. That values is declared in the $InsColumnVal associative array.

And then insert method is called with the object which accept the two argument $tablename and $InsColumnVal.

Insert method definition is as following,

Form live validation

After completing the execution of the Insert function, “New record Inserted successfully” message is shown if the data is inserted successfully or else an error is thrown if the data inserted is failed.

Delete Operation

To perform the delete operation, I have declared an associative array that is to be which table row you want to delete.

For example here I’m using an associative array which firstname is ‘Daniyal’ and lastname is “Zahan”; its means delete the data row which firstname is ‘Daniyal’ and lastname is “Zahan”. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

Now you can call the delete method with the object, where delete method accept two arguments one is table name ($tablename) on which this delete operation will be perform and other is delete column value associative array ($DelColumnVal).

Following is the definition of delete method which visibility mode is public. This delete method only performs the delete operation on the table and it can’t return anything.

After execute the delete method, you will show “Data is deleted successfully” in your web browser or fail to thrown an error message.

N.B: In this delete method has restriction is that sql query WHERE clause only contains AND expression.

Update Operation

To update table data row I have declared two associative arrays one is $set that accepted set operation value against columns fields and other is $condition that accepted conditional operation value against the column fields. Here same syntax rule is allowed for the associative array that is described earlier in the Insert Operation section.

Now you can call update method which accepted three arguments. First is table name ($tablename) on this which table you want to perform the operation, Second is $set associative array that you declare previous and third is $condition associative array that also declare previous.

Following is the definition of update method which visibility mode is public. This method only update the record within table not return anything.

If record is exist in the table it will be updated successfully and “Data is updated successfully” or else “Record you want to updated is no longer exists”. If any problem is happen to update record then an error will be thrown.

N.B: In this update method has restriction is that sql query WHERE clause only contains AND expression.

Fetch Operation

To fetch the data from table you can call fetch method which accept a arguments that is table name ($tablename) on which table you want to perform the fetch operation.

Following is the definition of fetch method which fetches all rows and returns the result-set as an associative array.

By this fetch operation all data are fetched and you can use any data as your wish.

Close Connection

Although PHP automatically closes the connection upon script termination, Instead of you want to close the connection just before script is complete, you can do so by just invoking the destructor method. But destructor method automatically invoked when the object is destroyed or its lifetime is bound to scope and the execution leaves the scope.

Within the destructor method you need to just assigned $this->connection = null which closes a previously opened database connection.

Conclusion

Previously, I was published the article “CRUD Operation by MySqli OOP way in PHP” and that’s article response was good. That’s why I get the inspired to write this article “CRUD Operation by PDO in PHP”. As PDO is offer a ways to work with database access layer that make work much easier than the MySqli. You can use this as a library to create any real time database related project. So keep going and Happy Coding…

SHARE
Next articleFive things you may have missed over the weekend
I am a professional software developer from last 4 years. I have experience in analysis, design, development, testing and implementation of desktop (using C, C++, C#) and web (using HTML5, CSS3, JavaScript, PHP, MySql) platform using. I have good exposure to object-oriented design, software architectures, design patterns, test-driven development and Project Management.

LEAVE A REPLY