PHP CRUD Operations Using PDO Prepared Statements

PHP CRUD Operations Using PDO Prepared Statements

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.

CRUD Index Page

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.

insert query output

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

SQL inserted data

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.

select all query 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.

MySQL Select Query

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.

update query output

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

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.

delete query output

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

delete 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

Article By
Javed Ur Rehman is a passionate blogger and web developer, he loves to share web development tutorials and blogging tips. He usually writes about HTML, CSS, JavaScript, Jquery, Ajax, PHP and MySQL.
  1. Sorry for my commentary Javed. you could have put the code step by step for beginning programmers. the tutorial got advanced. I didn’t mean to be offensive, sorry. Thanks.

    1. I try my best to explain things as simple as possible. However, to understand this you must have some basic SQL and PHP understanding, I think you can get basic PHP and MySQL knowledge from w3schools.

  2. Thanks for the script, it’s very helpful for my college project.

    It is possible for you to show how to:-

    1. Save an image

    2. Retrieve the image from the database.

    3. Compress the image before storing

    4. Add a watermark to the image.

    I will need these functionalities in my school project and if you guide me, i will be more than grateful.

    Good Luck and Best Wishes.

    1. I basically created a class that helps us to write less code to perform PDO methods. And I shared it with others because I believe that this can help other developers as well. If you prefer to use the default PDO methods. You can use it the way you like.

  3. Found Bug when testing Select All Records – I don’t know how to fix – please can you help?

    Connection works for all other links and a seperate test_connection.php I tried.

    This is the error:
    SQLSTATE[HY000] [1045] Access denied for user ‘ur22wd9w945kr’@’localhost’ (using password: YES)
    Fatal error: Uncaught Error: Call to a member function prepare() on null in /home/customer/www/spongtech.com/public_html/CRUD/dbclass.php:38 Stack trace: #0 /home/customer/www/spongtech.com/public_html/CRUD/selectall.php(10): DB->query(‘SELECT * FROM `…’) #1 {main} thrown in /home/customer/www/spongtech.com/public_html/CRUD/dbclass.php on line 38

    1. Hi Brooke,
      It seems like you are trying to call the member function without initialize the $db = new DB; Make sure that you have created an instance of DB class before calling member function.
      And Access denied for user error is caused by the incorrect database credentials which are username, password, dbname and host.

      1. Hi Javed
        Thanks for your quick reply.
        I am just using your code from your download. The only thing that has changed are the database credentials. I have tested these and they are fine. The only link that creates that error is the Select All Records.
        Cheers Brooke

        1. Alright, If you think that there is no database connection error, then I would suggest you to fetch single record on the Select All page and check are you able to get single record on the same page? You will need to debug the code to find out why does this not working on your side.
          If you can fetch single record this mean, there is something need to be fix in select all query. If it fails it means there is error in db connection on that specific page.

      2. Hi Javed
        Sorry my error, cache was preventing updates to show.

        I was using a server that has extreme amounts of cacheing and once I put in a .htaccess to stop this all went well.

        Sorry for the hassle. I wanted to use your work because of the way you communicated so well. Thanks again.

        Cheers Brook

Leave a Reply

Your email address will not be published. Required fields are marked *