PHP uses PDO and mysqli extensions to realize interactive operation with database

This article illustrates how PHP uses PDO and mysqli extensions to realize the interaction with database. Share it for your reference, as follows:

Database

When we develop php, someone may have learned the connection interaction of php database, or they may be preparing to learn it. Now, following the trend of php, mysql extensions have been discontinued and may be eliminated in future developments, such as mysql- > query(),mysql- > connect () and so on may not be available in the future. Therefore, we should try to use PDO and mysqli extensions.

PDO

The basic operations are as follows:

<?php
// PDO + MySQL
$servername = "localhost";
$username = "username";
$password = "password";
try{
  $pdo = new PDO('mysql:host=$servername;dbname=myDB', '$username',
   '$password');
  echo ' Connection succeeded ' ; 
}
catch(PDOExcepton $e){
  echo $e->getMessge();
}
$statement = $pdo->query("SELECT some_field FROM some_table");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['some_field']);
// PDO + SQLite
$pdo = new PDO('sqlite:/path/db/foo.sqlite');
$statement = $pdo->query("SELECT some_field FROM some_table");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['some_field']);
// Close the connection 
$pdo=null;

PDO does not translate SQL requests or emulate features that do not exist; It simply uses the same API to connect different kinds of databases.

More importantly, PDO enables you to safely insert external input (e.g. ID) into your SQL request without worrying about SQL injection. This can be achieved by using PDO statements and qualified parameters.

Let's assume that an PHP script receives a number ID as a request parameter. This ID should be used to fetch 1 user record from the database. The following is a wrong practice:

<?php
$pdo = new PDO('sqlite:/path/db/users.db');
$pdo->query("SELECT name FROM users WHERE id = " . $_GET['id']); // <-- NO!

This is a bad piece of code. You are inserting 1 original request parameter into the SQL request. This will make it easy for hackers to attack by [SQL injection]. Think about 1. If a hacker passes a constructed id parameter like http://domain. com/? id=1% 3BDELETE+FROM+users. This will cause the value of the $_ GET ['id'] variable to be set to 1; DELETE FROM users is then executed to delete all user records! Therefore, you should filter ID input using the PDO constraint parameter.

<?php
$pdo = new PDO('sqlite:/path/db/users.db');
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id');
$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT); // <--  Filter your data first   For INSERT , UPDATE Especially important, etc 
$stmt->bindParam(':id', $id, PDO::PARAM_INT); // <--  Pass PDO Automatic alignment SQL Clean up 
$stmt->execute();

This is the correct code. It uses one constraint parameter in one PDO statement. This escapes the external ID input before it is sent to the database to prevent potential SQL injection attacks.

For write operations, such as INSERT or UPDATE, it is particularly important to filter data and clean up other contents (remove HTML tags, Javascript, and so on). PDO will only clean up SQL, and will not do anything for your application.

mysqli Extension

The basic operation of mysqli is as follows:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
//  Create a connection 
$conn = new mysqli($servername, $username, $password);
//  Detect a connection 
if ($conn->connect_error) {
  die(" Connection failed : " . $conn->connect_error);
} 
echo " Connection succeeded ";
?>

Note that in the above object-oriented example $connect_error was added in PHP 5.2. 9 and 5.3. 0. If you need to be compatible with an earlier version, please replace it with the following code:

//  Detect a connection 
if (mysqli_connect_error()) {
  die(" Database connection failed : " . mysqli_connect_error());
}

Database interaction

<ul>
<?php
foreach ($db->query('SELECT * FROM table') as $row) {
  echo "<li>".$row['field1']." - ".$row['field1']."</li>";
}
?>
</ul>

This is wrong in many ways, mainly because it is difficult to read and difficult to test and debug. And if you don't limit it, it will output a lot of fields.

There are many different solutions to do this-depending on whether you prefer object-oriented programming (OOP) or functional programming-but there must be a few separate elements.

Let's take a look at the most basic practice:

<?php
function getAllFoos($db) {
  return $db->query('SELECT * FROM table');
}
foreach (getAllFoos($db) as $row) {
  echo "<li>".$row['field1']." - ".$row['field1']."</li>"; 
}

This is a good start. Put these two elements into two different files, so you get a clean separation.
Create a class to place the above functions, and you get an "Model". Create a simple. php file to hold the presentation logic, and you get an "View". This is very close to the object-oriented architecture commonly used by most frameworks in MVC-1.

//foo.php

<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
//  Make Templates Visible 
include 'models/FooModel.php';
//  Instantiate class 
$fooModel = new FooModel($db);
// Get the list of Foos
$fooList = $fooModel->getAllFoos();
//  Display view 
include 'views/foo-list.php';

//models/FooModel.php

<?php
class FooModel
{
  protected $db;
  public function __construct(PDO $db)
  {
    $this->db = $db;
  }
  public function getAllFoos() {
    return $this->db->query('SELECT * FROM table');
  }
}

//views/foo-list.php

<?php foreach ($fooList as $row): ?>
  <?= $row['field1'] ?> - <?= $row['field1'] ?>
<?php endforeach ?>

Many frameworks provide their own database abstraction layers, one of which is designed on top of PDO. These abstraction layers usually wrap your request in the PHP method, and emulate your database to have some functionality that was not previously supported. This abstraction is a true database abstraction, not just the database connection abstraction provided by PDO. This kind of abstraction does add a certain amount of performance overhead, but if you are designing an application that uses MySQL, PostgreSQL, and SQLite at the same time, a little extra performance overhead is worth it for improving code cleanliness.

For more readers interested in PHP related contents, please check the topics on this site: "Summary of PHP Database Operation Skills Based on pdo", "Summary of php+mysqli Database Programming Skills", "Introduction to php Object-Oriented Programming", "Summary of php String (string) Usage", "Introduction to php+mysql Database Operation Skills" and "Summary of php Common Database Operation Skills"

I hope this paper is helpful to everyone's PHP programming.