MySQL Functions to PDO: A Secure and Modern Approach?

Convert MySQL functions to PDO

Why switch?

MySQL functions have been deprecated due to their outdated architecture, lack of security features, and global state dependencies. PDO (PHP Data Objects) provides a modern, secure, object-oriented alternative for database operations.

Connect to database

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>

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>

Execute query

PDO uses prepared statements to prevent SQL injection.

Use named variables:

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

Use index variables:

<code class="language-php">$SQL = 'SELECT ID, EMAIL FROM users WHERE user=?';
$stmt = $dbh->prepare($SQL);
$stmt->execute([$username]);</code>

Get results

Use fetchAll:

<code class="language-php">$rows = $stmt->fetchAll();</code>

Use fetch:

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

The above is the detailed content of MySQL Functions to PDO: A Secure and Modern Approach?. For more information, please follow other related articles on the PHP Chinese website!