How to Migrate from Deprecated MySQL Functions to PDO for MySQL and MSSQL?

Convert MySQL functions to PDO for MySQL and MSSQL

MySQL function deprecation and alternatives

MySQL function extensions are deprecated and will be removed in a future release. It is recommended to use MySQLi or the PDO_MySQL extension as an alternative. MySQLi and PDO provide improved security and functionality compared to MySQL functions.

Implement PDO

PDO (PHP Data Objects) is an object-oriented API for accessing databases. To connect to a database using PDO, create a new PDO instance using the PDO constructor.

Connect to MySQL

<code class="language-php">$dsn = 'mysql:dbname=databasename;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);</code>

Connect to MSSQL

<code class="language-php">$dsn = 'sqlsrv:Server=127.0.0.1;Database=databasename';
$user = 'dbuser';
$password = 'dbpass';

$dbh = new PDO($dsn, $user, $password);</code>

Use PDO to execute queries

PDO uses prepared statements to prevent SQL injection vulnerabilities. A prepared statement defines a SQL query with named or indexed placeholders to represent variables.

<code class="language-php">$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
$preparedStatement = $dbh->prepare($SQL);</code>

Bind variables

Use bindParam or bindValue to bind values ​​to prepared statements.

<code class="language-php">$preparedStatement->bindParam(':username', $username);</code>

Execute query

Execute prepared statements to get results.

<code class="language-php">$preparedStatement->execute();</code>

Get results

PDO provides methods such as fetch and fetchAll to retrieve results as an array.

<code class="language-php">while ($row = $preparedStatement->fetch()) {
    echo $row['ID'];
    echo $row['EMAIL'];
}</code>

Use PDO connection class

You can create a PDO connection class to simplify database operations.

<code class="language-php">class Database {

    protected $connection;

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

    public function query($SQL) {
        return $this->connection->query($SQL);
    }

    public function prepare($SQL, $params = []) {
        $preparedStatement = $this->connection->prepare($SQL);
        $preparedStatement->execute($params);
        return $preparedStatement;
    }

}

// 使用方法：
$db = new Database($dsn, $user, $password);

$SQL = 'SELECT ID, EMAIL FROM users WHERE user = :username';
$result = $db->prepare($SQL, ['username' => $username]);

while ($row = $result->fetch()) {
    echo $row['ID'];
    echo $row['EMAIL'];
}</code>

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