How to Transition from Deprecated MySQL Functions to PDO in PHP?-PHP Tutorial-php.cn

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

Mary-Kate Olsen

Release： 2024-11-10 22:01:03

Original

994 people have browsed it

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);

Copy after login

MSSQL Connection:

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

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

Copy after login

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);

Copy after login

Indexed Placeholders:

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

Copy after login

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

Copy after login

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));

Copy after login

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!