Home >Backend Development >PHP Tutorial >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!