When to use MySQL, MySQLi, or PDO in PHP

Many people have doubt and questioned as to why I use a "weird extension" when I'm writing a new code. Well for those of you that do not know PHP (like the people that quizzed me, you know who you are!), then come to the world of PHP5. This article would be an explanation of what the different methods for accessing a MySQL database are, and when we should use them.

First of all, many people will say to use a single method no matter what, the one they'd say is normally PDO. If you are one of those people that say to use PDO regardless of the situation, you are wrong, read on for more info.Although, if you are  using PDO in the first place, you could probably work out the obviousness behind this article yourself in no time!

PHP's MySQL Extension

PHP's MySQL extension provides simple methods that are synonymous to the C API that is available for MySQL. All in all, it is not an advanced system  and most importantly, it is no longer maintained.

MySQLi Extension

PHP's MySQLi Extension stands for " MySQL Improved ". It's been around for a long time and was mainly developed to be able to use the new features within MySQL4.1 and above, including MySQL5, which we are all (hopefully) running by now.

Specifically, the MySQLi extension provides the following extremely useful benefits over the old MySQL extension..

• OOP Interface (in addition to procedural)
• Prepared Statement Support
• Transaction + Stored Procedure Support
• Nicer Syntax
• Speed Improvements
• Enhanced Debugging

The most important nicety about MySQLi, in my opinion, would either be the OOP interface, or the Prepared Statement Support.

If you're currently using MySQL Extension (ie. mysql_*) functions, and reading this article thinking something along the lines of "Noooo, I don't want to learn new stuff!", then don't worry, MySQLi also provides a procedural interface which is almost the same as mysql_ functions, however they are prefixed with mysqli_.

PDO Extension

PHP Data Objects extension is a Database Abstraction Layer. Specifically, this is not a MySQL interface,as it provides drivers for many Database Engines (of course including MYSQL).

PDO aims to provide a consistent API that means when a database engine has changed, the code changes to reflect this should be minimal to minimal. When using PDO, your code will normally "just work" across many database engines, simply by changing the driver which you're using.

In addition to being cross-database compatible, PDO also supports prepared statements, stored procedures and more, whilst using the MySQL Driver.

New Projects - What to use?

There are really only 2 options. PHP's MySQL extension definitely should not be used any more for new projects, due to it's lack of support / maintenance.

After reading the above descriptions, you may be thinking that it's always best to use PDO as it appears to provide everything that MySQLi does. Simply put - Don't think this at all. Despite PDO having some advantages such as the portability, it does not allow you to use new features available in latest versions of MySQL servers, like a MySQL specific extension does.

If you're coming from MySQL Extension to something modern, although you really should be using OOP already, you don't have to, and shouldn't have to just to use your database in a better way. MySQLi's procedural interface will be a breeze to convert to as 90% of the time, all you'll have to do is replace instances of mysql_ with mysqli_*.

If you are particularly versed with OOP already, and want to use the best thing for your project, then read on.

Is your project going to ever be used on a database engine other than MySQL? It's not a hard question to answer even if it may seem like it now. You can generally answer it by answering the following sub-questions: Is your application redistributable? Is your application advertised/forced to run on a LAMP/WAMP/MAMP stack?

If the answer is NO to that, then why would you use PDO? There is no point at all using a DAL for a single database, some people will say that it's future proofing your application, but if you're having to learn PDO or MySQLi from scratch, you'd spend more time learning PDO for no reason**.

If the answer is YES, then of course, PDO is definitely your best option and you should invest in making your application compatible with as many things as possible. Just remember, by simply using PDO in your application, it's not guaranteed to work on every other databae known to man, or that PDO supports.

Should not all projects be able to be used across multiple databases?

It depends. If your project is client redistributable (ie. a product), then yes, it would possibly be a good selling point to have it work across many database engines to increase your customer base.

But when it comes to companies, or looking for a job, MySQL is what the people want. In all of my years as  commercial developer within an agency or working on behalf of other companies chances are that they have an existing infrastructure and need you to write an application that suits them.The  one of most common hosting platform right now (unfortunately) is a Linux/Apache/MySQL /PHP setup, so everything's right there for them, and you, by default.

Only around three times have I ever been asked to write something that is portable, and even then, I used sqlite and it's sqlite3 extension within PHP.

Notes

1. I wouldn't suggest that in practice you simply change your calls for example,from mysql_query to mysqli_query. Instead  you should learn about the benefits of prepared statements, and how much nicer it is able to bind your parameters with straight up values, no more escaping or anything (there's obviously more to it than that - read!)!

2. Learning PDO certainly is not a bad thing. All I'm saying is that if you have  write an application in MySQL, spend time learning the nitty gritty details about MySQLi and not PDO. PDO come later, and everything that you have learned from MySQLi is still applicable there.

Difference Between Mysqli and Pdo in php

We have two choices for accessing the database in Php:

MySQLi and PDO.

we will discuss difference between PDO and MySQLi on various feature like database support, stability and performance to find which is the better API.

Connection Way
1.PDO 

$pdo = new PDO("mysql:host=localhost.com;dbname=data", 'username', 'password');
2.mysqli, procedural style

$mysqli = mysqli_connect('localhost.com','username','password','data');

3. mysqli,object oriented style

$mysqli = new mysqli('localhost.in','username','password','data');

1.API Support
Both PDO and MySQLi provides an object-oriented approach, but MySQLi provides a procedural way also like old mysql extension. New users may prefer MySQLi because of procedural interface which is similar to old mysql extension, So the migrating from the old mysql extension is easier. But after mastering in PDO, you can use it with any database you like.

2.Security
Both PDO and MySQLi provide SQL injection security, as long as developer follow them with features like escaping and parameter binding with prepared statements.
For example a hacker is trying to inject some malicious SQL through the ‘name’ HTTP query parameter (POST):

 $_POST['name'] = "'; DELETE FROM users; /*" 

If we fail to escape, it will be added in query “as it is” , it will delete all rows from users table as PDO and MySQLi support multiple queries.
// PDO, “manual” escaping

$name = PDO::quote($_POST['name']); 

$pdo->query("SELECT * FROM users WHERE name = $name");

// mysqli, “manual” escaping

$name = mysqli_real_escape_string($_POST['name']); 

$mysqli->query("SELECT * FROM users WHERE name = '$name'");

PDO::quote() not only escapes the string, but it also quotes it. But mysqli_real_escape_string() will only escape the string, you will need to apply the quotes manually.
// PDO, prepared statement

$pdo->prepare('SELECT * FROM users WHERE name = :name');

$pdo->execute(array(':name' => $_POST['name']));

// mysqli, prepared statements

$query = $mysqli->prepare('SELECT * FROM users WHERE name = ?');

$query->bind_param('s', $_POST['name']);

$query->execute();

I always recommend you to use prepared statements with bound queries instead of PDO::quote() and mysqli_real_escape_string().
3.Database Support
The main advantage of PDO over MySQLi is its database driver support. PDO supports 12 different drivers and MySQLi supports MySQL only.
To get the list of all drivers that PDO supports, use following code:

var_dump(PDO::getAvailableDrivers()); 

When you require to switch your project to use another database, in PDO you’ll have to change the connection string and a few queries – if they use methods which are not supported by new database. But in MySQLi, you will have to rewrite every part of code that included queries.

Conclusion:

How to insert data using pdo in three steps in php

Follow the Steps

Step1:Make a connection.
Step2: Pass the query into prepare() fucntion.
Step3: Execute the prepare statement.
Note: we already created Database as registration  and table as user
 Index.php
 <?php
$hostname='localhost';
$username='root';
$password='';
try {
$dbh = new PDO("mysql:host=$hostname;dbname=registration",$username,$password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo 'Connected to Database<br/>';
$name="enjoymyphp";
$password="12352";
$email="enjoymyphp@gmail.com";
$sql =$dbh->prepare("insert into user SET name='".$name."',password='".$password."',email='".$email."'");
$sql->execute();
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

How to fetch the data from database using pdo in Four steps in php

Follow the Steps
Step1:Make a connection.
Step2: Pass the query into prepare() fucntion.
Step3: Execute the prepare statement.

Step4: Create for loop and use the fetch() to retrieve the data.
Note: we already created Database as registration  and table as user

<body>

<center>

<table border="1" cellspacing="1" cellpading>

<tr>

<td>Id</td>

<td>Name</td>

<td>Password</td>

<td>Email</td>

</tr>

 <?php

$hostname='localhost';

$username='root';

$password='';

try {

$dbh = new PDO("mysql:host=$hostname;dbname=registration",$username,$password);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 echo 'Connected to Database<br/>';

$sql= $dbh->prepare("SELECT * FROM user");

$sql->execute();

for($i=0; $row=$sql->fetch();$i++){

echo "<tr><td>".$row["id"]."</td> ";

echo "<td>". $row["name"] ."</td> "; 
echo "<td>".$row["password"]."</td> ";

echo "<td>".$row["email"]."</td></tr>";

}

$dbh = null;

}

catch(PDOException $e)

{

echo $e->getMessage();

}

?>

</table>

</center>

</body>

How to delete any record from database in five step in php

Follow the Step:

Step 1: Select the host
Step2: Connect to the database
Step3: Fetch the Data using  mysqli_fetch_array() function .
Step4:Add <a href ='index.php?id=$id'> it.

Step5:Check id and execute delete query

My Database Name is registration and table name is user. 

Index.php 
 <body>

<?php

$username = "root";

$password = "";

$hostname = "localhost";

$database_name='registration';

$dbhandle = mysqli_connect($hostname ,$username ,$password ,$database_name)

or die("Unable to connect to MySQL");

$query="select *from user";

$abc=  mysqli_query($dbhandle,$query);

?> 

<center>

<table border="1" cellspacing="1" cellpading>  
<tr>
<td>Id</td>

<td>Name</td>
<td>Password</td> 
 <td>Email</td>

<td>delete</td>

</tr>
<?php

while ($row = mysqli_fetch_array($abc))

 {

$id=  $row['id'];

echo "<tr align='center'>";

echo"<td><font color='black'>" .$row['id']."</font></td>";

echo"<td><font color='black'>" .$row['name']."</font></td>";

echo"<td><font color='black'>". $row['password']. "</font></td>";

echo"<td><font color='black'>". $row['email']. "</font></td>";

echo"<td> <a href ='index.php?id=$id'><center>Delete</center></a>";

echo "</tr>";         

}

if(isset($_GET['id']))

{

$delete_id=$_GET['id'];

 $query="DELETE FROM user WHERE id='".$delete_id."';

$message=mysqli_query($dbhandle,$query);

if($message)

{

echo"Successfully Deleted";

} else

{

echo"Something Going Wrong";

} 

}

?>

</table>

</center>

</body>

Output will be

 

After Delete id=4 Output will be . Refresh the page

 

How to fetch data from database in four simple step in php

Follow the Step:

Step 1: Select the host
Step2: Connect to the database
Step3:Create the fetch query  to reterive all data
Step4. Use mysqli_fetch_array() function .

My Table look like 

Index.php

<body>

<?php

$username = "root";

$password = "";

$hostname = "localhost";

$database_name='registration';

$dbhandle = mysqli_connect($hostname,$username,$password,$database_name)

or die("Unable to connect to MySQL");

$query="select *from user";

$abc=  mysqli_query($dbhandle,$query);

?>

 <center>

 <table border="1" cellspacing="1" cellpading>

<tr>

 <td>Id</td>

<td>Name</td>

<td>Password</td>

<td>Email</td>

</tr>

<?php

while ($row = mysqli_fetch_array($abc))

{

 echo "<tr align='center'>";

echo"<td><font color='black'>" .$row['id']."</font></td>";

echo"<td><font color='black'>" .$row['name']."</font></td>";

echo"<td><font color='black'>". $row['password']. "</font></td>";

echo"<td><font color='black'>". $row['email']. "</font></td>";

echo "</tr>";

}

?>

</table>

</center>

    </body>

Output

How to connect with database in three step in php

Follow the step

Step 1:  Create Database and table.

 

Step2: Enter hostname, Database name.

Step3:Insert Data and Execute the Query.

This is my database and my database name is registration and table name is user.

We are inserting  a data statically(already data is selected).

        
<?php

$username = "root";

$password = "";

$hostname = "localhost";

$database_name='registration';

$password=” ”;

$dbhandle = mysqli_connect('localhost',$username ,$password,$database_name)

or die("Unable to connect to MySQL");

 $name ="Gaurav";

$password1="1234";

 $email="gauravrautela123@gmail.com";

 $query="insert into user set name='".$name."',password='".$password1."', email='".$email."'"; 
$execute =  mysqli_query($dbhandle, $query);

 If($execute)

     {

                 echo "Congrats Data inserted";

 }else{

                echo  "Something Going Wrong";

}

?>

After Insertion :

Terminology:

mysqli_connect(): It is php connection method,we can pass  four argument hostname,username,password,Database name.

Die();It stop the rest of exection if die methiod will exectute and show the

messages.