PHP MySQL query with 「PDO」

Don’t continue to use original MySQL functions in PHP, let’s try PDO !

Why choose PDO ?
#

First, MySql functions (such mysqli_connect()、mysql_connect()) as its name looks，only for MySQL.
But PDO is more than that !
PDO supported 12 different drivers, which includes MySQL、Oracle、SQLite、MS SQL、IBM…and more. (Read documentatin from official for more)

And more importantly, it’s able to effectively prevents SQL Injection.

How to connect ?
#

It’s needed be the instance.

 1define('DB',[
 2  'host' => 'localhost', # the host of database server
 3  'user' => 'root', # the user of database
 4  'pass' => 'password', # the password of user
 5  'name' => 'database', # the name of database
 6]);
 7
 8$db_connect = new PDO("mysql:host=".DB['host'].";dbname=".DB['name'].";charset=utf8mb4", DB['user'], DB['pass']);
 9
10if(!$db_connect){ die('Database: failed connection !'); } # check if connection is failed

And also recommend that use the follwing settings (just put it below the code above):

1# Allow to use the "Prepared Statement".
2# It prevents the SQL Injection from happening. (false)
3$db_connect->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
4
5# Whether to prevent convert numeric values to strings when fetching. (false)
6$db_connect->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

For more detail, see the PHP official website https://www.php.net/manual/en/pdo.setattribute.php

Pre-Query & Execute
#

Pre-Query
#

1$sql = "SELECT * FROM `table` WHERE `id`=:id OR `name`=:name;";
2$query = $db_connect->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

Just put the sentence of SQL query to the prepare() function, and the values of sentence are able to use the format :value to represent the value field. (the value in :value can be change the any word you want, except the few sensitive words. And it’s NOT allow to use the duplicate name.)

(It hasn’t not yet be executed from now.)

Execute
#

1$values = [
2  ':id' => 123,
3  ':name' => 'admin',
4];
5
6$result = $query->execute($values);
7
8if($result === false){ die('Query Error !'); };

Put the values to the execute() function (using array format), and it will return the result, also possible to return false when the query got the some errors.