etutorialspoint
  • Home
  • PHP
  • MySQL
  • MongoDB
  • HTML
  • Javascript
  • Node.js
  • Express.js
  • Python
  • Jquery
  • R
  • Kotlin
  • DS
  • Blogs
  • Theory of Computation

PHP MySQL PDO Database Connection and CRUD Operations

In this article, you will learn how to connect to the MySQL database using PHP Data Object and perform CRUD operations. So, let's first know what is PDO?

Database is the base of every web application, consisting of the database management system that manages the data users create, delete, modify, and query. There are several vendors available to connect PHP with popular DBMS. To standardize and streamline development practices, PHP introduced PHP Data Objects (PDO) in PHP 5.1.

PDO extends for PHP Data Object. It is lightweight, more portable interface for accessing the database in PHP. It is a database access layer which makes the developer to write portable code much easier.

To perform database operation using PDO, we need a database PDO Driver. Without PDO database driver, we cannot get benefit of pdo database extensions.

PDO working is object-based, which is more productive. As MySQL does not support prepared statement, stored procedures. So with the help of PDO, we can use prepared statement features in MySQL. With the help of PDO, we can make MySQL more secure, and and we can also access multiple databases. PDO Driver provides an abstraction layer between the database and PHP.



About Prepared Statement

In a prepared statement, we can use the same query multiple times. First, we need to analyze and optimize the query and later we can use it in many places. It reduces development time and executes faster. PDO provides various ways to work with objects and retrieves prepared statements that make work much easier.


PDO MySQL Connection in PHP

PDO Drivers

To use PDO in PHP, php_pdo_mysql should be enabled in php.ini in PHP extension. This is by default enabled in PHP version 5.1.0 and later versions.

The PDO can work with 12 different database systems. If you have to switch your project database to another database, PDO makes the process easy. There are different PDO drivers available for different databases.

Databases Drivers
MySQL 3.x & later PDO_MYSQL
SQLite 3 and SQLite 2 PDO_SQLITE
PostgreSQL PDO_PGSQL
ODBC v3 PDO_ODBC
IBM Informix PDO_INFORMIX
Cubrid PDO_CUBRID
Firebird PDO_FIREBIRD
Microsoft SQL Server PDO_SQLSRV


To check Available PDO Drivers in PHP

The following PHP command returns the available PDO Drivers in PHP. It returns all enabled PDO Drivers in Array.

<?php
 $database = new PDO("mysql:host=$hostname;dbname=$databasename", 
 $username, $password);
?>




PDO MySQL Database Connection

The PDO represents a connection between PHP and the database. Here is the syntax for PDO MySQL database connection. It creates a new PDO object with the data source name, user, and password. The PDO object is an instance of the PDO class.

<?php
$database = new PDO("mysql:host=$hostname;dbname=$databasename", 
$username, $password);
?>

  • $hostname - Server Name
  • $databasename - Database Name
  • $username - Database User Name
  • $password - Database Password

To handle PDO connection error

PDO has an exception class to handle any problems that may occur in our database queries. To handle the exception, it would be better to write the connection code in a try catch block. If an exception is thrown in the try block, the script stops execution and control goes directly to the catch block.

<?php
try{ 
$database = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password);
}
catch(PDOExtension $e){
echo $e->getMessage();
}
?>




Close the PDO MySQL database connection.

To close the MySQL PDO connection, just set null to the database connection variable.

$database = null;




MySQL CRUD operations using PDO


PHP MySQL Create Table

In this given code, we have created a table name 'employees'. It contains four fields(emp_id, first_name, last_name emp_code). The '$database' is the database connection variable that we have created above.

Here exec() is used to execute the PDO and return the number of affected rows.

<?php
$create_tbl = "CREATE TABLE employees emp_id int(11) NOT   NULL AUTO_INCREMENT, first_name varchar(25) NOT NULL,
 last_name varchar(25) NOT NULL, emp_code varchar(25) NOT NULL"; 
$create_emp = $database->exec($create_tbl);
if($create_emp != 'false'){
 echo 'Employee table created successfully.'; 
}
else{ 
 echo 'Error in Create Statement.';
} 
?>




PHP MySQL INSERT Table

Here, prepare() is used to prepare the insert statement for execution by using PDO execute() method.

<?php
$emp_insert = $database->prepare("INSERT into employees   ('emp_id','first_name','last_name','emp_code') VALUES ('', 'John', 'Bown', '121')");
$emp_insert->execute();
?>
To protect from SQL Injection, we should use placeholder instead of values. Like, we can write the above code as -
<?php
$emp_data = array('id'=>'', 'fname'=>'John', 'lname'=>'Bown', 'empcode'=>'121');
$emp_insert = $database->prepare("INSERT into employees('emp_id','first_name','last_name','emp_code') 
VALUES ('', ':fname', ':lname', ':empcode')");
$emp_insert->execute($emp_data);
?>


PHP MySQL UPDATE Table

Here, the prepare() is used to prepare the update statement for execution by using PDO execute() method. The bindparam() binds a parameter to the specified variable name.

<?php
$emp_update = $database->prepare("UPDATE employees SET first_name= :fname, last_name = :lname WHERE id = :id ");
$emp_update->bindparam(':fname', 'Doran');
$emp_update->bindparam(':lname', 'Devis');
$emp_update->execute();
?>


PHP MySQL SELECT from Table

Here, the prepare() is used to prepare the select statement for execution by using PDO execute() method. The bindparam() binds a parameter to the specified variable name to fetch the desired value, i.e., it sets the where clause condition and the fetch() method fetches a row from the result set.

Here, PDO::FETCH_ASSOC returns an array indexed by column name.

<?php
$emp_select = $database->prepare("SELECT FROM employees WHERE id = :id ");
$emp_select->bindparam(':id', '1');
$emp_select->execute();
$emprows = $emp_select->fetch(PDO::FETCH_ASSOC);
return $emprows;
?>




Related Articles

How to create search filter in PHP
PHP array length
Import Excel File into MySQL Database using PHP
PHP String Contains
PHP remove last character from string
PHP random quote generator
PHP calculate percentage of total
PHP sanitize input
Upload multiple files and store in MySQL database using PHP
multiple choice quiz in PHP and MySQL
Import Data Into MySQL From Excel File
Preventing Cross Site Request Forgeries(CSRF) in PHP
PHP code to send email using SMTP
Simple pagination in PHP
Simple PHP File Cache
PHP upload multiple files
PHP Connection and File Handling on FTP Server
Sending form data to an email using PHP
Recover forgot password using PHP and MySQL
How to display PDF file in PHP from database




Most Popular Development Resources
Retrieve Data From Database Without Page refresh Using AJAX, PHP and Javascript
-----------------
PHP Create Word Document from HTML
-----------------
How to get data from XML file in PHP
-----------------
Hypertext Transfer Protocol Overview
-----------------
PHP code to send email using SMTP
-----------------
Characteristics of a Good Computer Program
-----------------
How to encrypt password in PHP
-----------------
Create Dynamic Pie Chart using Google API, PHP and MySQL
-----------------
PHP MySQL PDO Database Connection and CRUD Operations
-----------------
Splitting MySQL Results Into Two Columns Using PHP
-----------------
Dynamically Add/Delete HTML Table Rows Using Javascript
-----------------
How to add multiple custom markers on google map
-----------------
How to get current directory, filename and code line number in PHP
-----------------
Fibonacci Series Program in PHP
-----------------
Get current visitor\'s location using HTML5 Geolocation API and PHP
-----------------
How to Sort Table Data in PHP and MySQL
-----------------
Simple star rating system using PHP, jQuery and Ajax
-----------------
Submit a form data using PHP, AJAX and Javascript
-----------------
jQuery loop over JSON result after AJAX Success
-----------------
How to generate QR Code in PHP
-----------------
Simple pagination in PHP
-----------------
Recover forgot password using PHP7 and MySQLi
-----------------
PHP MYSQL Advanced Search Feature
-----------------
PHP Server Side Form Validation
-----------------
PHP user registration and login/ logout with secure password encryption
-----------------
jQuery File upload progress bar with file size validation
-----------------
Simple PHP File Cache
-----------------
Simple File Upload Script in PHP
-----------------
Php file based authentication
-----------------
To check whether a year is a leap year or not in php
-----------------
Calculate distance between two locations using PHP
-----------------
PHP User Authentication by IP Address
-----------------
PHP Secure User Registration with Login/logout
-----------------
Simple way to send SMTP mail using Node.js
-----------------
How to print specific part of a web page in javascript
-----------------
Simple Show Hide Menu Navigation
-----------------
Detect Mobile Devices in PHP
-----------------
Polling system using PHP, Ajax and MySql
-----------------
PHP Sending HTML form data to an Email
-----------------
Google Street View API Example
-----------------
Get Visitor\'s location and TimeZone
-----------------
SQL Injection Prevention Techniques
-----------------
Preventing Cross Site Request Forgeries(CSRF) in PHP
-----------------
Driving route directions from source to destination using HTML5 and Javascript
-----------------
Convert MySQL to JSON using PHP
-----------------
Set and Get Cookies in PHP
-----------------
CSS Simple Menu Navigation Bar
-----------------
PHP Programming Error Types
-----------------
Date Timestamp Formats in PHP
-----------------
How to select/deselect all checkboxes using Javascript
-----------------
How to add google map on your website and display address on click marker
-----------------
Write a python program to print all even numbers between 1 to 100
-----------------
How to display PDF file in web page from Database in PHP
-----------------
PHP Getting Document of Remote Address
-----------------
File Upload Validation in PHP
-----------------


Most Popular Blogs
Most in demand programming languages
Best mvc PHP frameworks in 2019
MariaDB vs MySQL
Most in demand NoSQL databases for 2019
Best AI Startups In India
Kotlin : Android App Development Choice
Kotlin vs Java which one is better
Top Android App Development Languages in 2019
Web Robots
Data Science Recruitment of Freshers - 2019


Interview Questions Answers
Basic PHP Interview
Advanced PHP Interview
MySQL Interview
Javascript Interview
HTML Interview
CSS Interview
Programming C Interview
Programming C++ Interview
Java Interview
Computer Networking Interview
NodeJS Interview
ExpressJS Interview
R Interview


Popular Tutorials
PHP Tutorial (Basic & Advance)
MySQL Tutorial & Exercise
MongoDB Tutorial
Python Tutorial & Exercise
Kotlin Tutorial & Exercise
R Programming Tutorial
HTML Tutorial
jQuery Tutorial
NodeJS Tutorial
ExpressJS Tutorial
Theory of Computation Tutorial
Data Structure Tutorial
Javascript Tutorial






Learn Popular Language

listen
listen
listen
listen
listen

Blogs

  • Jan 3

    Stateful vs Stateless

    A Stateful application recalls explicit subtleties of a client like profile, inclinations, and client activities...

  • Dec 29

    Best programming language to learn in 2021

    In this article, we have mentioned the analyzed results of the best programming language for 2021...

  • Dec 20

    How is Python best for mobile app development?

    Python has a set of useful Libraries and Packages that minimize the use of code...

  • July 18

    Learn all about Emoji

    In this article, we have mentioned all about emojis. It's invention, world emoji day, emojicode programming language and much more...

  • Jan 10

    Data Science Recruitment of Freshers

    In this article, we have mentioned about the recruitment of data science. Data Science is a buzz for every technician...

Follow us

  • etutorialspoint facebook
  • etutorialspoint twitter
  • etutorialspoint linkedin
etutorialspoint youtube
About Us      Contact Us


  • eTutorialsPoint©Copyright 2016-2023. All Rights Reserved.