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

Written by - 0 comments

Published on - 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

In mysqli_close the database connection link must be given:

// mysqli - NEW

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.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder