Download

In this tutorial, I will explain how to perform PHP CRUD operations using PDO Prepared Statements. CRUD stands for Create, Read, Update and Delete, these four functions are essential part of any application.

PDO stands for PHP Data Objects, it is an extension that provides the interface for accessing databases in PHP. PDO supports multiple databases, which means you can work on MySQL, SQLite, Oracle, PostgreSQL, MS SQL Server and other databases.

There are many benefits of using PDO, some of them are listed below:

• Security: PDO uses a prepared statement which protects against SQL injection.
• Reusability: Supports multiple databases with unified API, which is a key benefit of PDO.
• Speed: It enhance the speed by using prepare function, which means you do not need to write query for each execution, just change the data and execute.

Prepared Statements

Prepared Statements protects from SQL injections because it divide query and data into two parts.

In procedural query, we write query like this:

SELECT * FROM Cars WHERE id=1;

In Prepared Statements, we can write query in any two methods given below:

SELECT * FROM Cars WHERE id=?;

or

SELECT * FROM Cars WHERE id=:id;

As you can see above that prepared statements uses two ways, first is the positional (?) placeholder and second is the named (:id) placeholder to pass the data into query.

Without further delay, lets start work on PHP CRUD using PDO Prepared Statements.

Steps to Create PHP CRUD Operations Using PDO Prepared Statements

To make it easy to understand, I am performing only PHP CRUD without any UI framework such as Bootstrap, because my focus is only on PHP CRUD Operations with PDO Prepared Statements.

I will follow the below simple steps to create a PHP CRUD.

1. Create a Database and Table
2. Create a Database Class File
3. Create an Index File
4. Create an Insert Query File
5. Create a Select All Query File
6. Create a Select Query File
7. Create an Update Query File
8. Create a Delete Query File

1. Create a Database and Table

To create database run the following query in MySQL.

CREATE DATABASE allphptricks;

To create a table run the following query.

CREATE TABLE `cars` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `founded` int(10) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Create a Database Class File

Create a dbclass.php file and copy paste the following code in it. This is very essential code, it contains a database class which holds the database credentials and methods which reduces the amount of writing code while performing CRUD.

Make sure to update your database credentials such as username, password.

// Start Database Class
class DB
{
	private $db_host = '127.0.0.1';	
	private	$db_name = 'allphptricks';
	private $db_user = 'root';
	private	$db_pass = '';

	private $dbh;
	private $error;
	private $stmt;
	
	public function __construct()
	{
		//Set DSN (Data Source Name)
		$dsn = 'mysql:host='.$this->db_host.';dbname='.$this->db_name;
		$db_options = array(
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
		);
		try
		{
			$this->dbh = new PDO($dsn, $this->db_user, $this->db_pass, $db_options);
		}
		catch(PDOException $e)
		{
			echo $this->error = $e->getMessage();
		}
	}

	public function query($query)
	{
		$this->stmt = $this->dbh->prepare($query);
	}	
	
	public function bind($param, $value, $type = null)
	{
		if(is_null($type))
		{
			switch(true)
			{
				case is_int($value);
					$type = PDO::PARAM_INT;
					break;
				case is_bool($value);
					$type = PDO::PARAM_BOOL;
					break;
				case is_null($value);
					$type = PDO::PARAM_NULL;
					break;
				default;
					$type = PDO::PARAM_STR;
				
			}
		}
		$this->stmt->bindValue($param, $value, $type); 	
	}

	public function execute($array = null)
	{
		return $this->stmt->execute($array);
	}

	public function lastInsertId()
	{
		return $this->dbh->lastInsertId();
	}

	public function rowCount()
	{
		return $this->stmt->rowCount();
	}	

	public function result($array = null)
	{
		$this->execute($array);
		return $this->stmt->fetch();
	}

	public function resultSet($array = null)
	{
		$this->execute($array);
		return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
	}

	public function close()
	{
		return $this->dbh = null;
	}	

}
// End Database Class

3. Create an Index File

Create a index.php file and copy paste the following code in it. Although, this file is not important but I have created this so that you can just click on any link to perform the CRUD without writing the URL in the browser.

<p>Click on the below links to perform the CRUD
 operations using PDO Prepared Statements.</p>
<ol>
    <li>
        <a href="insert.php">Insert Records</a>
    </li>
    <li>
        <a href="selectall.php">Select All Records</a>
    </li>
    <li>
        <a href="select.php">Select One Record</a>
    </li>
    <li>
        <a href="update.php">Update Record</a>
    </li>
    <li>
        <a href="delete.php">Delete Record</a>
    </li>
</ol>

The above code will generate the following output.

4. Create an Insert Query File

Create an insert.php file and copy paste the following code in it. This will insert records into Cars table.

PDO offers two methods to pass data into query, positional (?) and named (:id) placeholders. Following code contain both methods to insert data into table.

require_once('dbclass.php');
$db = new DB;

// Insert Query (Method 1 - named placeholders)
$db->query("INSERT INTO `cars` (`name`, `founded`) VALUES (:name, :founded)");
$db->bind(":name", 'Audi');
$db->bind(":founded", '1909');
$db->execute(); // 1 Record Added Successfully
$insert1 = $db->lastInsertId();

$db->bind(":name", 'BMW');
$db->bind(":founded", '1916');
$db->execute(); // 1 More Record Added Successfully
$insert2 = $db->lastInsertId();

// Insert Query (Method 2 - positional placeholders)
$db->query("INSERT INTO `cars` (`name`, `founded`) VALUES (?, ?)");
$db->execute(array('Mercedes', '1926')); // 1 Record Added Successfully
$insert3 = $db->lastInsertId();
$db->execute(array('Tesla', '2003')); // 1 More Record Added Successfully
$insert4 = $db->lastInsertId();

$db->close(); // Database connection is closed
echo "Method 1 - named placeholder<br />";
echo $insert1 . "<br />";
echo $insert2 . "<br />";
echo "Method 2 - positional placeholder<br />";
echo $insert3 . "<br />";
echo $insert4 . "<br />";

The above code will generate the following output on the screen. 1, 2, 3, and 4 indicates the id assigned to rows.

If we go to check the database table cars, we will find the following data in it.

5. Create a Select All Query File

Create a selectall.php file and copy paste the following code in it. This will fetch all records from Cars table.

require_once('dbclass.php');
$db = new DB;

// Select Query of All Records 
$db->query("SELECT * FROM `cars`");
$cars = $db->resultSet();

$db->close(); // Database connection is closed

if( !empty($cars) )
{
    foreach($cars as $car)
    {
        echo $car['name'] ." ". $car['founded'];
        echo "<br />";
    }
}

The above code will generate the following output.

6. Create a Select Query File

Create a select.php file and copy paste the following code in it. This will fetch single record from Cars table. We have fetch single record using both positional (?) and named (:id) placeholders, therefore we will have two records in output.

require_once('dbclass.php');
$db = new DB;

// Select Query of One Record (Method 1 - named placeholder)
$db->query("SELECT * FROM `cars` WHERE id=:id");
$db->bind(':id', 1);
$car = $db->result();

// Select Query of One Record (Method 2 - positional placeholder)
$db->query("SELECT * FROM `cars` WHERE id=?");
$car2 = $db->result(array(2));

$db->close(); // Database connection is closed

if( !empty($car) )
{
	echo "Method 1 - named placeholder<br />";
	echo $car['name'] ." ". $car['founded'];
	echo "<br />";
}
if( !empty($car2) )
{
	echo "Method 2 - positional placeholder<br />";
	echo $car2['name'] ." ". $car2['founded'];
}

The above code will generate the following output.

7. Create an Update Query File

Create an update.php file and copy paste the following code in it. This will update records in Cars table.

require_once('dbclass.php');
$db = new DB;

// Update Query (Method 1 - named placeholder)
$db->query("UPDATE `cars` SET `name`=:name WHERE id=:id");
$db->bind(':name', 'Audi 2');
$db->bind(':id', 1);
$db->execute();
$update1 = $db->rowCount();

// Update Query (Method 2 - positional placeholder)
$db->query("UPDATE `cars` SET `name`=? WHERE id=?");
$db->execute(array('BMW 2', 2));
$update2 = $db->rowCount();

$db->close(); // Database connection is closed
echo "Method 1 - named placeholder<br />";
echo $update1;
echo "<br />";
echo "Method 2 - positional placeholder<br />";
echo $update2;

The above code will generate the following output on screen. 1 indicates that one record is updated.

If you again check all records, you will get the following updated output.

8. Create a Delete Query File

Create a delete.php file and copy paste the following code in it. This will delete records in Cars table.

require_once('dbclass.php');
$db = new DB;

// Delete Query (Method 1 - named placeholder)
$db->query("DELETE FROM `cars` WHERE id=:id");
$db->bind(':id', 1);
$db->execute();
$delete1 = $db->rowCount();

// Delete Query (Method 2 - positional placeholder)
$db->query("DELETE FROM `cars` WHERE id=?");
$db->execute(array(2));
$delete2 = $db->rowCount();

$db->close(); // Database connection is closed
echo "Method 1 - named placeholder<br />";
echo $delete1;
echo "<br />";
echo "Method 2 - positional placeholder<br />";
echo $delete2;

The above code will generate the following output, after deleting the records from Cars table. 1 indicates that 1 record has been deleted.

If you again select all records, you will get the following output.

Conclusion

PHP CRUD operations can be performed very easily with the prepared statements, ensures the security of data and improved the speed of execution of code.

I try my best to explain the code as simple as possible, therefore I didn’t use any CSS in the entire tutorials. I hope this will help all of those developers who are interested to learn about PHP CRUD using PDO.

If you still have any query you can leave it in comment section below, I will try to respond as soon as possible.

Download

If you found this tutorial helpful, share it with your friends and developers group.

I spent several hours to create this tutorial, if you want to say thanks so like my page on Facebook, Twitter and share it.

Facebook Official Page: All PHP Tricks

Twitter Official Page: All PHP Tricks