How to Transition from Deprecated MySQL Functions to PDO in PHP?

How to Transition from MySQL Functions to PDO

Deprecated MySQL Functions

Modern PHP versions deprecate the MySQL functions due to their limitations and security concerns. Instead, developers should use the PDO or MySQLi extensions for improved database connectivity and security.

Introduction to PDO

PDO stands for PHP Data Objects and provides a consistent API for connecting to different database systems, including MySQL and MSSQL. Unlike MySQL functions, PDO uses a unified interface and offers increased security through prepared statements.

Connecting to Databases

MySQL Connection:

$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);

MSSQL Connection:

$dsn = 'sqlsrv:Server=127.0.0.1;Database=databasename';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);

Executing Queries

PDO uses prepared statements instead of plain SQL strings to prevent SQL injection vulnerabilities. Prepared statements are constructed with placeholders that are later bound to variables.

Named Placeholders:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
$queryArguments = array(':username' => $username);
$result = $dbh->prepare($SQL);
$result->execute($queryArguments);

Indexed Placeholders:

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = ?';
$bindParamResults = array($username);
$result = $dbh->prepare($SQL);
$result->bindParam(1, $bindParamResults[0]);
$result->execute();

Fetching Results

Results can be fetched using various methods, such as fetch() and fetchAll().

$row = $result->fetch(PDO::FETCH_ASSOC);  // Returns an associative array
$allRows = $result->fetchAll(PDO::FETCH_ASSOC);  // Returns an array of associative arrays

Sample PDO Class

class PDOC {

    public function __construct($dsn, $user, $password) {
        $this->dbh = new PDO($dsn, $user, $password);
    }

    public function query($sql, $params = array()) {
        $stmt = $this->dbh->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
}
$pdod = new PDOC('mysql:dbname=db;host=localhost', 'root', '');

$query = $pdod->query('SELECT * From table WHERE id = ?', array(2));

The above is the detailed content of How to Transition from Deprecated MySQL Functions to PDO in PHP?. For more information, please follow other related articles on the PHP Chinese website!