How to use PDO and Bootstrap to connect PHP to databases

If you use the MySQLi API to access your MySQL data, your code is closely tied to this API and will no longer work if your MySQL database migrates to Oracle or SQL Server for example. To make your code independent of a DBMS, you can adopt the PDO component. In the following lines, we see how to use PDO by displaying MySQL data. The display interface will use Twitter Bootstrap.

PDO or PHP Data Objects is a set of classes for PHP interaction with databases. These classes can be used to display or update data, to create and use prepared statements or to manage transactions.

In this introduction to PDO, we will :

  • create a HTML5 template with Bootstrap for the display interface,
  • create a table books in a database named bookstore,
  • create a books.php page to display data,
  • use some common PDO methods.

You can go further in the use of PDO by implementing the prepared statements and transaction functionalities, by creating complete CRUD interfaces or by inheriting from the PDO class to add your own behaviors.


Want to go further with PDO?
Attend the PHP virtual classroom.

How to use PDO to connect PHP to databases


Firstly, we will present the most common PDO classes and methods for interacting with data.

1- The classes provided by PDO

The PDO component provides 3 main classes for working with data: the PDOException class, the PDO class and the PDOStatement class :

- PDOException : used to manage exceptions. It inherits from Exception.

- PDO : contains methods to query and update data.

- PDOStatement : contains the fetch methods.

Some PDO methods

PDO classes expose several methods and several attributes. Here are some of these methods, which you will find in more detail in the documentation:

query() : executes a SELECT query, returns a PDOStatement object.

exec() : to execute update requests.

lastInsertId() : gives the ID of an insert SQL request.

prepare() : to prepare a prepared statement.

fetch() : perform the fetch.

setFetchMode() : sets the fetch type.

beginTransaction() : start a transaction.

2- Create a table named books

We will manage books stored in a books table in MySQL database. Say your database is called bookstore.

Below is the books table, with 7 fields or columns.

You can create the table by using the SQL code below. If you use a tool like phpMyAdmin, you can create this table through the interface without typing any SQL code.

    title VARCHAR(50),
    author VARCHAR(50),
    editor VARCHAR(50),
    price DECIMAL(5,2),
    description TEXT,
    photo VARCHAR(30)
) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_general_ci;

After creating your table, insert some books into it by use the insert SQL code below; add as many books as you want.

VALUES(NULL, 'The good wife', 'John DOE', 'CBS', 15, 'Enjoy the life', 'wife.jpg'),
(NULL, 'Tales of mystery and imagination', 'Edgar Allan POE', 'Enigma', 22, 'Tales', 'tales.jpg');

You don't have enough SQL knowledge ?
Attend the SQL virtual classroom.

3- Bootstrap template to display data

To have a responsive display of our books, we will create a simple Bootstrap template with a few bootstrap rows.

This template will be composed of several parts that you just need to include with the PHP include() directive to quickly compose a new page.

+ header.php will contain the navigation.

+ banner.php will allow you to add a slider.

+ footer.php will end each page.

All these files can be placed in a folder named includes, images files in images folder, styles in css folder and javascripts in js folder (see figure).

Custom CSS and javascript files can be downloaded here: css.css and bootstrap.min.js.


It contains the HEAD of each page with references to CSS files like bootstrap.min.css and JavaScript files. It also contains the Bootstrap navigation for each page.

<!doctype html>
		<meta charset="utf-8">
		<title><?php echo $pagetitle;?></title>
		<meta name="viewport" content="width=device-width,initial-scale=1.0"/>
		<meta name="description" content="Bookstore"/>

		<link rel="stylesheet" 
		<link href="css/css.css" rel="stylesheet" media="screen"/>
		<script src=""></script>

    <nav class="navbar navbar-default navbar-fixed-top">
		<div class="container-fluid">
			<div class="navbar-header">
			  <button class="navbar-toggle" data-toggle="collapse" 
					<span class="icon-bar"></span> 
			  <a class="navbar-brand" href="index.php">
				<span class="navbar-logo">
					<img src="images/logo.png" width="50%">

			  <ul class="nav navbar-nav collapse navbar-collapse n
			           avHeaderCollapse navbar-right">
				<li><a class="link" href="index.php">Home</a></li>
				<li><a class="link" href="books.php">Books</a></li>
				<li><a class="link" href="contact.php">Contact</a></li>


The banner is a location on a Bootstrap row used for placing content such as text or a slider for example.

<div class="bandeau">
	<div class="container">   
		<div class="row">
			<div class="col-md-12 col-sm-12">
				<div class="accroche">
					   BEST BOOKS

					<p class="sous-accroche">
						  IT technologies.


The footer ends each page. It contains for example a bottom menu, a copyright or any other information that you want to put in footer. It may also refer to Javascript code: bootstrap.min.js, Facebook, Twitter, Google Analytics or other tracking code.

	    <footer class="footer">
				<div class="copyright">
					Copyright : Bookstore 2022.

		<script src="js/bootstrap.min.js"></script>

4- Display data - the books.php page

It is now possible to create the display page using PDO. Its interface can be improved to visualize the details of a book, the deletion of one or more books, the update of a book and to add a book (CRUD).

The books.php page will have code similar to the following.

	$pagetitle = "Bookstore : list of books";//Page title
<div class="container-fluid marges background" style="margin-top:0px;margin-bottom:0px;">
	<div class="container">    
		<div class="row">
			<div class="col-md-12 title">
				List of books

		<div class="row">
			<div class="col-md-12 argu">


Executing the SQL query with PDO

To execute the query that retrieve books, you must instantiate a PDO object and invoke the query() method on it. To handle error cases, you can use an attribute defining the exception handling mode (line 10) and place all the useful code in a try block. The catch block is used to display a possible error message.

    // books.php
	try {
		$dsn = 'mysql:host=localhost;dbname=bookstore;charset=utf8';
		$user = 'root'; $pass = '';
		$options = array();// Array of options des options
		//Create a PDO object
		$dbh = new PDO($dsn,$user,$pass,$options);
		// La requete SQL
		$query = "SELECT * FROM books";
		// Query : return a PDOStatement object
		$stmt = $dbh->query($query);
		// Define fetch mode
	//Return a PDOException object
	catch (PDOException $e) {
		//Apply getMessage() method
		echo $e->getMessage().   "<br/>";
	//Page title
	$titre = "Bookstore : best books.";

	//Header and  and bootstrap navbar

	// Banner

$dbh = new PDO($dsn, $user, $pass, $options) : creation of a PDO object. $options is an array of options that we want to define when creating the object.

$dbh->setAttribute(PDO::ATTR_ERRMODE, ERRMODE_EXCEPTION) : we indicate that we will handle errors using the exception mechanism with a PDOException object.

$stmt = $dbh->query($query) : we execute the query with the query() method and we obtain a PDOStatement type object.

$stmt->setFetchMode($PDO::FETCH_OBJ) : we define the fetch mode we want to use.

We display data in the BODY of the page. The fetch() method will be used.

Displaying data within the page

To display the correct information, we can test the number of rows returned by the SELECT query. This test is done with the rowCount() method of the PDOStatement object. The fetch uses the fetch() or fetchAll() method.

We place this PHP code in a bootstrap row, in multiple DIVs using the row, container, and container-fluid CSS classes.

<div class="container-fluid marges background" style="margin-top:0px;margin-bottom:0px;">
	<div class="container">    
		<div class="row">
			<div class="col-md-12 title">
				List of books

		<div class="row">
			<div class="col-md-12 argu">
					if($stmt->rowCount() != 0){	
						echo "<table id='tab_books'>
								  <tr style='background-color:yellow;font-weight:bold;'>
						// Fetch
						while($data = $stmt->fetch()){
							echo "<tr> 
									  <td><a href=detail-pdo.php?bookid=" . $data->bookid . ">". $data->title . "</a></td>
									  <td>". $data->author . "</td>
									  <td>". $data->editor . "</td>
						echo "</table>";// Close the table tag
						echo "<br><b>Total : </b>" . $stmt->rowCount() . "<br>";
						echo "No book in the database.<br><br>";

Close the connection

After displaying data, the connection is closed by destroying the PDO object with unset($dbh) or by assigning it the value NULL.

	// Footer links
	// Close the connection 
	unset($dbh); // or $dbh=NULL

Want to go further with PDO?
Attend the PHP virtual classroom.


Founder of ReCONVERT, web and e-commerce project manager, trainer of +2000 trainees face-to-face and online (LIVE and VOD). Currently, I am developing Digital Learning.

Follow me on social media.