Many times, your PHP program needs to use a database to store data for a long time. At this time, you have several different options to connect to and interact with the database. stay Before PHP 5.1.0, we recommended using, for example mysqli，pgsql，mssql And other native drivers.
In use only One In the case of database, native driver is a good way, but if you use MySQL and a little MSSQL at the same time, or you need to use Oracle database, you can't use only one database driver. You need to learn different API s for each database - this is obviously unscientific.
MySQL extension
PHP mysql The extension is very old and has been replaced by two other extensions:
In PHP mysql The extension is no longer under new development, Officially marked obsolete in PHP version 5.5.0 , and in 7.0 officially removed .
Want to identify whether it is used mysql ， You don't need to php.ini Check it out. Just use the editor to open your project and search globally mysql_* ， If there are similar mysql_connect() perhaps mysql_query() If the method appears, it indicates that it is used mysql.
Even if you haven't used PHP 7.x yet, it's best to consider using it mysqli or PDO Replace the use of mysql, so that when you need to upgrade in the future, you won't be in trouble for a while.
If you're from mysql Upgrade to mysqli , try not to use global substitution mysql_* by mysqli_*， This is not only an oversimplification, but also ignores the excellent features provided by mysqli, such as data parameter binding PDO Parameter binding is also provided.
PDO extension
PDO Is a database connection abstract library, which has been built into PHP since version 5.1.0. It provides a common interface to interact with a variety of different databases. For example, you can use basically the same code to connect to MySQL or SQLite:
<?php
// PDO + MySQL
$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');
$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']);PDO does not transform SQL requests or simulate missing features. It simply uses the same API to connect to different types of databases.
More importantly, PDO allows you to safely insert external input (such as ID) into SQL queries without worrying about database SQL injection attacks. This can be achieved by using PDO statements and binding parameters.
Suppose we pass in an id as the query parameter. This id is used to obtain the user record from the data. This use is wrong:
<?php
$pdo = new PDO('sqlite:/path/db/users.db');
$pdo->query("SELECT name FROM users WHERE id = " . $_GET['id']); // < -- this is not allowed!This operation is extremely dangerous! Inserting the original parameters directly into the SQL statement will cause SQL injection Risks. If the hacker calls the URL (e.g domain.com/?id=1%3BDELETE+FROM+user... )Pass in a modified ID parameter. use $_ GET['id'] The obtained parameter is 1;DELETE FROM users All users will be deleted! Instead, you should use PDO binding parameters
<?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 incoming data (please refer to PHP filter), which is particularly important for INSERT, UPDATE and other operations.
$stmt->bindParam(':id', $id, PDO::PARAM_INT); // < -- automatically fi lt er SQL through PDO
$stmt->execute();This is the correct way to operate. It uses binding parameters on PDO statements. This will escape the foreign input id before it is introduced into the database to prevent potential SQL injection attacks.
For write operations, such as INSERT or UPDATE, you still need to use filter Clean it, For other operations (remove Html tags, javascript, etc.). PDO only cleans up SQL, not programs.
You should also know that database connections consume resources and may cause the depletion of available resources without implicitly closing the connection. However, this is usually more common in other languages. With PDO, you can implicitly close these connections by destroy ing objects, that is, setting the value to NULL, ensuring that all remaining connections to reference objects are deleted. If you don't explicitly do this, PHP will automatically close the connection at the end of your script -- unless, of course, you're using a persistent connection.
Database interaction
When developers first come into contact with PHP, they often mix database interaction with presentation logic. The code used may be as follows:
<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 not easy to read and difficult to test and debug. And if you don't limit it, it will output a lot of fields.
Although there are many other solutions that can achieve this - depending on your preference Object oriented programming (OOP) still Functional programming — But there must be some separate elements.
Let's look at the most basic approach:
<?php
function getAllFoos($db) {
return $db->query('SELECT * FROM table');
}
$results = getAllFoos($db);
foreach ($results as $row) {
echo "<li>".$row['field1']." - ".$row['field1']."</li>"; // BAD!!
}This is a good start. By putting these two elements in two different files, you get some clear separation.
Create a class to place the above function, and you get a "Model". Create a simple. php file to store the presentation logic, and you get a "View". This is very close MVC — A majority frame Common object-oriented architecture.
foo.php
<?php
$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8mb4', 'username', 'password');
// Make your model available
include 'models/FooModel.php';
// Create an instance
$fooModel = new FooModel($db);
// Get foo list
$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): ?>
<li><?= $row['field1'] ?> - <?= $row['field1'] ?></li>
<?php endforeach ?>This is essentially the same as what most modern frameworks do, with a little more manual work. You may not need to do this completely every time, but if you want to modify your application unit testing , mixing too much presentation logic with database interaction may bring a series of problems.
Database abstraction layer
Many frameworks provide their own database abstraction layers, some of which are designed in PDO Upper. These abstraction layers usually wrap your request in PHP methods to make your database have some previously unsupported functions through simulation. This abstraction is a real database abstraction, not just the database connection abstraction provided by PDO. This kind of abstraction does increase the performance overhead to a certain extent, but if the application you are designing needs to use MySQL, PostgreSQL and SQLite at the same time, a little extra performance overhead is still worthwhile for improving the code cleanliness.
Some abstraction layers are used PSR-0 or PSR-4 Namespace standard, so it can be installed in any application you need:
This article was first published in LearnKu.com On the website.