How to change mysql to mysqli?
PhpMysqlMysqliPhp Problem Overview
Based on this code below I use for regular mysql, how could I convert it to use mysqli?
Is it as simple as changing mysql_query($sql);
to mysqli_query($sql);
?
<?PHP
//in my header file that is included on every page I have this
$DB["dbName"] = "emails";
$DB["host"] = "localhost";
$DB["user"] = "root";
$DB["pass"] = "";
$link = mysql_connect($DB['host'], $DB['user'], $DB['pass']) or die("<center>An Internal Error has Occured. Please report following error to the webmaster.<br><br>".mysql_error()."'</center>");
mysql_select_db($DB['dbName']);
// end header connection part
// function from a functions file that I run a mysql query through in any page.
function executeQuery($sql) {
$result = mysql_query($sql);
if (mysql_error()) {
$error = '<BR><center><font size="+1" face="arial" color="red">An Internal Error has Occured.<BR> The error has been recorded for review</font></center><br>';
if ($_SESSION['auto_id'] == 1) {
$sql_formatted = highlight_string(stripslashes($sql), true);
$error .= '<b>The MySQL Syntax Used</b><br>' . $sql_formatted . '<br><br><b>The MySQL Error Returned</b><br>' . mysql_error();
}
die($error);
}
return $result;
}
// example query ran on anypage of the site using executeQuery function
$sql='SELECT auto_id FROM friend_reg_user WHERE auto_id=' .$info['auto_id'];
$result_member=executequery($sql);
if($line_member=mysql_fetch_array($result_member)){
extract($line_member);
} else {
header("location: index.php");
exit;
}
?>
Php Solutions
Solution 1 - Php
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 bymysqli_connect
mysql_error
will be replaced bymysqli_error
and/ormysqli_connect_error
, depending on the contextmysql_query
will be replaced bymysqli_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 ;-)
Solution 2 - Php
(I realise this is old, but it still comes up...)
If you do replace mysql_*
with mysqli_*
then bear in mind that a whole load of mysqli_*
functions need the database link to be passed.
E.g.:
mysql_query($query)
becomes
mysqli_query($link, $query)
I.e., lots of checking required.
Solution 3 - Php
mysql_*
functions to MySQLi API
The ultimate guide to upgrading The reason for the new mysqli extension was to take advantage of new features found in MySQL systems versions 4.1.3 and newer. When changing your existing code from mysql_*
to mysqli API you should avail of these improvements, otherwise your upgrade efforts could go in vain.
The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:
- Object-oriented interface
- Support for Prepared Statements
- Enhanced debugging capabilities
When upgrading from mysql_*
functions to MySQLi, it is important to take these features into consideration, as well as some changes in the way this API should be used.
1. Object-oriented interface versus procedural functions.
The new mysqli object-oriented interface is a big improvement over the older functions and it can make your code cleaner and less susceptible to typographical errors. There is also the procedural version of this API, but its use is discouraged as it leads to less readable code, which is more prone to errors.
To open new connection to the database with MySQLi you need to create new instance of MySQLi class.
$mysqli = new \mysqli($host, $user, $password, $dbName);
$mysqli->set_charset('utf8mb4');
Using procedural style it would look like this:
$mysqli = mysqli_connect($host, $user, $password, $dbName);
mysqli_set_charset($mysqli, 'utf8mb4');
Keep in mind that only the first 3 parameters are the same as in mysql_connect
. The same code in the old API would be:
$link = mysql_connect($host, $user, $password);
mysql_select_db($dbName, $link);
mysql_query('SET NAMES utf8');
If your PHP code relied on implicit connection with default parameters defined in php.ini, you now have to open the MySQLi connection passing the parameters in your code, and then provide the connection link to all procedural functions or use the OOP style.
For more information see the article: How to connect properly using mysqli
2. Support for Prepared Statements
This is a big one. MySQL has added support for native prepared statements in MySQL 4.1 (2004). Prepared statements are the best way to prevent SQL injection. It was only logical that support for native prepared statements was added to PHP. Prepared statements should be used whenever data needs to be passed along with the SQL statement (i.e. WHERE
, INSERT
or UPDATE
are the usual use cases).
The old MySQL API had a function to escape the strings used in SQL called mysql_real_escape_string
, but it was never intended for protection against SQL injections and naturally shouldn't be used for the purpose.
The new MySQLi API offers a substitute function mysqli_real_escape_string
for backwards compatibility, which suffers from the same problems as the old one and therefore should not be used unless prepared statements are not available.
The old mysql_* way:
$login = mysql_real_escape_string($_POST['login']);
$result = mysql_query("SELECT * FROM users WHERE user='$login'");
The prepared statement way:
$stmt = $mysqli->prepare('SELECT * FROM users WHERE user=?');
$stmt->bind_param('s', $_POST['login']);
$stmt->execute();
$result = $stmt->get_result();
Prepared statements in MySQLi can look a little off-putting to beginners. If you are starting a new project then deciding to use the more powerful and simpler PDO API might be a good idea.
3. Enhanced debugging capabilities
Some old-school PHP developers are used to checking for SQL errors manually and displaying them directly in the browser as means of debugging. However, such practice turned out to be not only cumbersome, but also a security risk. Thankfully MySQLi has improved error reporting capabilities.
MySQLi is able to report any errors it encounters as PHP exceptions. PHP exceptions will bubble up in the script and if unhandled will terminate it instantly, which means that no statement after the erroneous one will ever be executed. The exception will trigger PHP Fatal error and will behave as any error triggered from PHP core obeying the display_errors
and log_errors
settings. To enable MySQLi exceptions use the line mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
and insert it right before you open the DB connection.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli($host, $user, $password, $dbName);
$mysqli->set_charset('utf8mb4');
If you were used to writing code such as:
$result = mysql_query('SELECT * WHERE 1=1');
if (!$result) {
die('Invalid query: ' . mysql_error());
}
or
$result = mysql_query('SELECT * WHERE 1=1') or die(mysql_error());
you no longer need to die()
in your code.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli($host, $user, $password, $dbName);
$mysqli->set_charset('utf8mb4');
$result = $mysqli->query('SELECT * FROM non_existent_table');
// The following line will never be executed due to the mysqli_sql_exception being thrown above
foreach ($result as $row) {
// ...
}
If for some reason you can't use exceptions, MySQLi has equivalent functions for error retrieval. You can use mysqli_connect_error()
to check for connection errors and mysqli_error($mysqli)
for any other errors. Pay attention to the mandatory argument in mysqli_error($mysqli)
or alternatively stick to OOP style and use $mysqli->error
.
$result = $mysqli->query('SELECT * FROM non_existent_table') or trigger_error($mysqli->error, E_USER_ERROR);
See these posts for more explanation:
mysqli or die, does it have to die?
How to get MySQLi error information in different environments?
4. Other changes
Unfortunately not every function from mysql_*
has its counterpart in MySQLi only with an "i" added in the name and connection link as first parameter. Here is a list of some of them:
mysql_client_encoding()
has been replaced bymysqli_character_set_name($mysqli)
mysql_create_db
has no counterpart. Use prepared statements ormysqli_query
insteadmysql_drop_db
has no counterpart. Use prepared statements ormysqli_query
insteadmysql_db_name
&mysql_list_dbs
support has been dropped in favour of SQL'sSHOW DATABASES
mysql_list_tables
support has been dropped in favour of SQL'sSHOW TABLES FROM dbname
mysql_list_fields
support has been dropped in favour of SQL'sSHOW COLUMNS FROM sometable
mysql_db_query
-> usemysqli_select_db()
then the query or specify the DB name in the querymysql_fetch_field($result, 5)
-> the second parameter (offset) is not present inmysqli_fetch_field
. You can usemysqli_fetch_field_direct
keeping in mind the different results returnedmysql_field_flags
,mysql_field_len
,mysql_field_name
,mysql_field_table
&mysql_field_type
-> has been replaced withmysqli_fetch_field_direct
mysql_list_processes
has been removed. If you need thread ID usemysqli_thread_id
mysql_pconnect
has been replaced withmysqli_connect()
withp:
host prefixmysql_result
-> usemysqli_data_seek()
in conjunction withmysqli_field_seek()
andmysqli_fetch_field()
mysql_tablename
support has been dropped in favour of SQL'sSHOW TABLES
mysql_unbuffered_query
has been removed. See this article for more information Buffered and Unbuffered queries
Solution 4 - Php
The easiest way i always handle this Where
$con = mysqli_connect($serverName,$dbusername,$dbpassword);
3 steps replacement in the following order
- All "mysql_select_db(" with "mysqli_select_db($con,"
- All "mysql_query(" with "mysqli_query($con," and
- All "mysql_" with "mysqli_".
This works for me everytime
Solution 5 - Php
2020+ Answer
I've created a tool called Rector, that handles instant upgrades. There is also mysql → mysqli set.
It handles:
-
function renaming
-
constant renaming
-
switched arguments
-
non-1:1 function calls changes, e.g.
$data = mysql_db_name($result, $row);
↓
mysqli_data_seek($result, $row); $fetch = mysql_fetch_row($result); $data = $fetch[0];
How to use Rector?
1. Install it via Composer
composer require rector/rector --dev
// or in case of composer conflicts
composer require rector/rector-prefixed --dev
rector.php
in project root directory with the Mysql to Mysqli set
2. Create <?php
use Rector\Core\Configuration\Option;
use Rector\Set\ValueObject\SetList;
use Symfony\Component\DependencyInjection\Loader\Configurator\ContainerConfigurator;
return static function (ContainerConfigurator $containerConfigurator): void {
$parameters->set(Option::SETS, [
SetList::MYSQL_TO_MYSQLI,
]);
};
3. Let Rector run on e.g. /src directory to only show the diffs
vendor/bin/rector process src --dry-run
4. Let Rector change the code
vendor/bin/rector process src
I've already run it on 2 big PHP projects and it works perfectly.
Solution 6 - Php
In case of big projects, many files to change and also if the previous project version of PHP was 5.6 and the new one is 7.1, you can create a new file sql.php and include it in the header or somewhere you use it all the time and needs sql connection. For example:
//local
$sql_host = "localhost";
$sql_username = "root";
$sql_password = "";
$sql_database = "db";
$mysqli = new mysqli($sql_host , $sql_username , $sql_password , $sql_database );
/* check connection */
if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}
// /* change character set to utf8 */
if (!$mysqli->set_charset("utf8")) {
printf("Error loading character set utf8: %s\n", $mysqli->error);
exit();
} else {
// printf("Current character set: %s\n", $mysqli->character_set_name());
}
if (!function_exists('mysql_real_escape_string')) {
function mysql_real_escape_string($string){
global $mysqli;
if($string){
// $mysqli = new mysqli($sql_host , $sql_username , $sql_password , $sql_database );
$newString = $mysqli->real_escape_string($string);
return $newString;
}
}
}
// $mysqli->close();
$conn = null;
if (!function_exists('mysql_query')) {
function mysql_query($query) {
global $mysqli;
// echo "DAAAAA";
if($query) {
$result = $mysqli->query($query);
return $result;
}
}
}
else {
$conn=mysql_connect($sql_host,$sql_username, $sql_password);
mysql_set_charset("utf8", $conn);
mysql_select_db($sql_database);
}
if (!function_exists('mysql_fetch_array')) {
function mysql_fetch_array($result){
if($result){
$row = $result->fetch_assoc();
return $row;
}
}
}
if (!function_exists('mysql_num_rows')) {
function mysql_num_rows($result){
if($result){
$row_cnt = $result->num_rows;;
return $row_cnt;
}
}
}
if (!function_exists('mysql_free_result')) {
function mysql_free_result($result){
if($result){
global $mysqli;
$result->free();
}
}
}
if (!function_exists('mysql_data_seek')) {
function mysql_data_seek($result, $offset){
if($result){
global $mysqli;
return $result->data_seek($offset);
}
}
}
if (!function_exists('mysql_close')) {
function mysql_close(){
global $mysqli;
return $mysqli->close();
}
}
if (!function_exists('mysql_insert_id')) {
function mysql_insert_id(){
global $mysqli;
$lastInsertId = $mysqli->insert_id;
return $lastInsertId;
}
}
if (!function_exists('mysql_error')) {
function mysql_error(){
global $mysqli;
$error = $mysqli->error;
return $error;
}
}
Solution 7 - Php
I would tentatively recommend using PDO for your SQL access.
Then it is only a case of changing the driver and ensuring the SQL works on the new backend. In theory. Data migration is a different issue.
Abstract database access is great.
Solution 8 - Php
Here is a complete tutorial how to make it quickly if you need to make worgking again a website after PHP upgrade. I used it after upgrading hosting for my customers from 5.4 (OMG!!!) to 7.x PHP version.
> This is a workaround and it is better to rewrite all code using > PDO or mysqli Class.
- Connection definition
First of all, you need to put the connection to a new variable $link
or $con
, or whatever you want.
Example
Change the connection from :
@mysql_connect($host, $username, $password) or die("Error message...");
@mysql_select_db($db);
or
@mysql_connect($host, $username, $password, $db) or die("Error message...");
to:
$con = mysqli_connect($host, $username, $password, $db) or die("Error message...");
2. mysql_* modification
With Notepad++ I use "Find in files" (Ctrl + Shift + f) :
in the following order I choose "Replace in Files" :
-
mysql_query( -> mysqli_query($con,
-
mysql_error() -> mysqli_error($con)
-
mysql_close() -> mysqli_close($con)
-
mysql_insert_id() -> mysqli_insert_id($con)
-
mysql_real_escape_string( -> mysqli_real_escape_string($con,
-
mysql_ -> mysqli_
-
adjustments
if you get errors it is maybe because your $con is not accessible from your functions.
You need to add a global $con;
in all your functions, for example :
function my_function(...) {
global $con;
...
}
In SQL class, you will put connection to $this->con
instead of $con
. and replace it in each functions call (for example : mysqli_query($con, $query);
)
Solution 9 - Php
I have just created the function with the same names to convert and overwrite to the new one php7:
$host = "your host";
$un = "username";
$pw = "password";
$db = "database";
$MYSQLI_CONNECT = mysqli_connect($host, $un, $pw, $db);
function mysql_query($q) {
global $MYSQLI_CONNECT;
return mysqli_query($MYSQLI_CONNECT,$q);
}
function mysql_fetch_assoc($q) {
return mysqli_fetch_assoc($q);
}
function mysql_fetch_array($q){
return mysqli_fetch_array($q , MYSQLI_BOTH);
}
function mysql_num_rows($q){
return mysqli_num_rows($q);
}
function mysql_insert_id() {
global $MYSQLI_CONNECT;
return mysqli_insert_id($MYSQLI_CONNECT);
}
function mysql_real_escape_string($q) {
global $MYSQLI_CONNECT;
return mysqli_real_escape_string($MYSQLI_CONNECT,$q);
}
It works for me , I hope it will work for you all , if I mistaken , correct me.
Solution 10 - Php
If you have a lot files to change in your projects you can create functions with the same names like mysql functions, and in the functions make the convert like this code:
$sql_host = "your host";
$sql_username = "username";
$sql_password = "password";
$sql_database = "database";
$mysqli = new mysqli($sql_host , $sql_username , $sql_password , $sql_database );
/* check connection */
if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}
function mysql_query($query){
$result = $mysqli->query($query);
return $result;
}
function mysql_fetch_array($result){
if($result){
$row = $result->fetch_assoc();
return $row;
}
}
function mysql_num_rows($result){
if($result){
$row_cnt = $result->num_rows;;
return $row_cnt;
}
}
Solution 11 - Php
Although this topic is a decade old, I still often require to 'backpatch' existing applications which relied upon the mysql
extension — the original programmers were too lazy to refactor all their code, and just tell customers to make sure that they run the latest PHP 5.6 version available.
PHP 5.6 is now officially deprecated; in other words, developers had a decade to get rid of their dependencies upon mysql
and move to PDO
(or, well, mysqli
...). But... changing so much legacy code is expensive, and not every manager is willing to pay for the uncountable hours to 'fix' projects with dozens of thousands of lines.
I've searched for many solutions, and, in my case, I often used the solution presented by @esty-shlomovitz — but in the meantime, I've found something even better:
https://www.phpclasses.org/package/9199-PHP-Replace-mysql-functions-using-the-mysqli-extension.html
(you need to register to download it, but that just takes a minute)
These are just two files which act as drop-in replacements for the whole mysql
extension and very cleverly emulate pretty much everything (using mysqli
) without the need to worry much about it. Of course, it's not a perfect solution, but very likely it will work in 99% of all cases out there.
Also, a good tutorial for dealing with the chores of migration (listing many of the common pitfalls when migrating) can also be found here: https://www.phpclasses.org/blog/package/9199/post/3-Smoothly-Migrate-your-PHP-Code-using-the-Old-MySQL-extension-to-MySQLi.html
(if you're reading this in 2030 and the PHPclasses website is down, well, you can always try archive.org :-)
Update: @crashwap noted on the comments below that you can also get the same code directly from GitHub. Thanks for the tip, @crashwap :-)
Solution 12 - Php
similar to dhw's answer but you don't have to worry about setting the link as global in all the function because that is kind of difficult:
just use this code in your config file:
$sv_connection = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
$db_connection = mysqli_select_db ($sv_connection, $dbname);
mysqli_set_charset($sv_connection, 'utf8'); //optional
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
function mysqljx_query($q){
global $sv_connection;
return mysqli_query($sv_connection, $q);
}
function mysqljx_fetch_array($r){
return mysqli_fetch_array($r);
}
function mysqljx_fetch_assoc($r){
return mysqli_fetch_assoc($r);
}
function mysqljx_num_rows($r){
return mysqli_num_rows($r);
}
function mysqljx_insert_id(){
global $sv_connection;
return mysqli_insert_id($sv_connection);
}
function mysqljx_real_escape_string($string){
global $sv_connection;
return mysqli_real_escape_string($sv_connection, $string);
}
-now do a search for php files that contain "mysql_" (i used total commander for that - Alt+F7, search for "*.php", find text "mysql_", Start search, Feed to listbox)
-drag&drop them all in Notepad++, there u press CTRL+H, Find what: "mysql_", Replace with "mysqljx_", "Replace All in All Opened Documents"
if you are worried that you have other functions than the ones listed above just replace one by one ("mysql_query" with "mysqljx_query", then mysql_fetch_array with "mysqljx_fetch_array" etc..) and then search again for "mysql_" and if its still there its a uncovered function and you can just add it same as the rest..
that is it