Changing from PHP mysql to mysqli - what to look out for

Written by Claudio Kuenzler - 0 comments

Published on September 14th 2013 - last updated on April 28th 2021 - Listed in Internet PHP MySQL

---

For more than ten years I've been using PHP's mysql functions to create dynamic web pages. Today I migrated a web page created in 2009 to a new web server running with PHP 5.4 and got some warnings in the output. 

After a bit of research it looks like the mysql functions will disappear in PHP 5.5:

mysql_query — Send a MySQL query
This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.

Awww! :-(

Well, time to learn the newer mysqli functions. What came a little strange to me were different methods how to use the mysqli functions:

• object oriented style: $result = $mysqli->query($query);
• procedural style: $result = mysqli_query($link, $query);

Because the procedural style is more similar to the original mysql_query, I decided to go with the procedural style.

By changing the source code from mysql to mysqli, I came across the following changes.

The connection goes directly to the database

In previous mysql_connect, only the host, user and password could be given. The database then had to be selected by mysql_select_db():

// mysql - OLD
$dbh=mysql_connect("$mysql_host", "$mysql_user", "$mysql_password") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$mysql_db");

Now the database is already given in the connect function:

// mysqli - NEW
$mysqli = mysqli_connect("$mysql_host", "$mysql_user", "$mysql_password", "$mysql_db");

Each SQL query needs to be "linked" to the database connection

Previously it was possible to just create the connection, select the database and then start doing SQL queries:

// mysql - OLD
$anfrage = mysql_query("SELECT * FROM categories ORDER BY sortno");

In mysqli, each query needs to be linked against the database connection, previously defined (see $mysqli above):

// mysqli - NEW
$anfrage = mysqli_query($mysqli, "SELECT * FROM categories ORDER BY sortno");

mysql_close becomes mysqli_close

Well that was pretty obvious. But with mysql_close() it was optional to mention the connection/link identifier:

// mysql - OLD
mysql_close();

In mysqli_close the database connection link must be given:

// mysqli - NEW
mysqli_close($mysqli);

After some manual work in the source code, the website now runs as it should again. With the new mysqli functions.

 

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.