PHP

The Classic mysql Extension

The original notes list the functions of PHP's first MySQL extension, whose names all start with mysql_. For more than a decade almost every PHP tutorial used them. However:

• The mysql extension was deprecated in PHP 5.5 (2013) and removed in PHP 7.0 (2015).
• It did not support prepared statements, so programs were easily open to SQL injection.
• It did not support newer MySQL features (multiple statements, transactions API, SSL options, stored-procedure results).

Its replacements are MySQLi ("MySQL improved", available since PHP 5) – which offers both a procedural and an object-oriented interface – and PDO. This book uses MySQLi in both styles (Chapters 34–36).

<?php
// Classic code from PHP 5 notes – run on PHP 8
$connect = mysql_connect("localhost", "root", "");
echo "Connected!";
?>

Fatal error: Uncaught Error: Call to undefined function mysql_connect() in /opt/lampp/htdocs/phpbook/ch27/oldcode.php:3 Stack trace: #0 {main} thrown in /opt/lampp/htdocs/phpbook/ch27/oldcode.php on line 3

The program stops with a fatal error because the function no longer exists. Checking a few names confirms it:

<?php
foreach (["mysql_connect", "mysql_query", "mysqli_connect", "mysqli_query"] as $f) {
    echo str_pad($f, 18) . " : " . (function_exists($f) ? "available" : "NOT available") . "<br>";
}
echo "mysqli extension loaded: " . (extension_loaded("mysqli") ? "yes" : "no") . "<br>";
echo "mysqli client library: " . mysqli_get_client_info();
?>

mysql_connect : NOT available
mysql_query : NOT available
mysqli_connect : available
mysqli_query : available
mysqli extension loaded: yes
mysqli client library: mysqlnd 8.3.6

Classic Functions and their Replacements

In the table, $link is the connection and $result a query result. In the procedural style the connection is passed as the first argument; in the OOP style the functions are methods of the mysqli and mysqli_result objects.

Converting Old Code – a First Look

The same small task in the classic style and in the two MySQLi styles (the database and table are created in Chapter 32):

$link = mysql_connect("localhost", "root", "");
mysql_select_db("phpbook", $link);
$result = mysql_query("SELECT name FROM students");
while ($row = mysql_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
mysql_close($link);

$link = mysqli_connect("localhost", "root", "", "phpbook");
$result = mysqli_query($link, "SELECT name FROM students");
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
mysqli_close($link);

$link = new mysqli("localhost", "root", "", "phpbook");
$result = $link->query("SELECT name FROM students");
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . "<br>";
}
$link->close();

The steps are identical – connect, query, fetch in a loop, close – only the function names and the position of the connection change. Part IV runs all of these programs against a real database and shows their output.