How to Change MySQL to MySQLi

How to change mysql to mysqli?

The first thing to do would probably be to replace every mysql_* function call with its equivalent mysqli_*, at least if you are willing to use the procedural API -- which would be the easier way, considering you already have some code based on the MySQL API, which is a procedural one.

To help with that, the MySQLi Extension Function Summary is definitely something that will prove helpful.

For instance:

• mysql_connect will be replaced by mysqli_connect
• mysql_error will be replaced by mysqli_error and/or mysqli_connect_error, depending on the context
• mysql_query will be replaced by mysqli_query
• and so on

Note: For some functions, you may need to check the parameters carefully: Maybe there are some differences here and there -- but not that many, I'd say: both mysql and mysqli are based on the same library (libmysql ; at least for PHP <= 5.2)

For instance:

• with mysql, you have to use the mysql_select_db once connected, to indicate on which database you want to do your queries
• mysqli, on the other side, allows you to specify that database name as the fourth parameter to mysqli_connect.
• Still, there is also a mysqli_select_db function that you can use, if you prefer.

Once you are done with that, try to execute the new version of your script... And check if everything works ; if not... Time for bug hunting ;-)

Convert MySQL to MySQLi in PHP

Use mysqli_query - syntax almost the same, but if your use procedural style, first parameter - database link:

$link = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
$query = mysqli_query($link, "INSERT INTO users VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')");

Or object style:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$query = $mysqli->query("INSERT INTO users VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')");

Also you should not use variables direct in the sql query, use parameters binding:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$stmt = $mysqli->prepare("INSERT INTO users VALUES ('',?,?,?,?,?,?,'0')")

$stmt->bind_param('ssssss', $un, $fn, $ln, $em, $pswd, $d);
$stmt->execute();
$stmt->close();

How do I migrate my site from mysql to mysqli?

I advise you to read through this. A lot of helpful information for what you want to do, plus a tool to help. See: Converting to MySQLi.

It's just the PHP that changes.

Updating from MYSQL to MYSQLI

You can download a converter tool from here:

https://github.com/philip/MySQLConverterTool

The code it generates is pretty gross, mainly because of the way it implements the default database link argument with a $GLOBAL variable. (This also makes it easy to recognize when someone is using code that's gone through the converter.)

There's also a MySQL Shim Library located here:

https://github.com/dshafik/php7-mysql-shim

how to do mysql to mysqli

Use regular expressions. I have not checked that my expression works as advertised, and regular expression syntax varies depending on the tool you use. But in general it should work this way:

• Search for mysql_query\(("[^"]*"), *(\$[a-zA-Z0-9_]+)\)
• Replace with mysqli_query($2, $1)

As an example, in Eclipse IDE, use "Search/File...", tick the checkbox "Regular expression", specify "*.php" as file name pattern and enter this as under "Containing text":

mysql_query\(("[^"]*"), *(\$[a-zA-Z0-9_]+)\)

Then hit the "Replace..." button and enter this under "With":

mysqli_query($1, $2)

Click Preview to check the results. If done correctly, this can update hundreds of files within seconds. Remember to test your code afterwards.

---

Related Topics