How to Migrate My Site from MySQL to MySQLi

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.

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();

Switch large website from MySQL to MySQLi

There is no easy answer to your question as practically every simple way to do this involved doing things differently when the application was written.

If you have direct calls to mysql_* functions throughout your code and no database abstraction layer where you do your queries through a helper class or function then you will need to edit every command.

You cannot just get away with adding an i to commands like mysql_query as procedurally mysqli_query() requires the first parameter to be the link to the db where with mysql_query() if a connection was given at all, it was a second parameter.

Instead of just changing mysql_query(...) to mysqli_query($link,.....) I would recommend that there is no better time to put a db abstraction layer in place. So use functions eg sql_query() that actually process your queries so in future if you need to change DB again you can just update the db specific commands in one abstraction file. That way if you write a function that wraps mysqli_query then you could be able to simply rename your mysql_query() to your helper function and let the helper function worry about putting the link in there.

Whilst that is the simplest way, it will not bind parameters or prepare statements which is a major factor in preventing sql injection vulnerabilities

Once you have changed all these commands you need to test.

If you have no automated tests written then this is probably a good time to start writing them. Even though you will need to check that every change has worked, if you do it by automated test then you can avoid that pain in future.

Simply converting from mysql to mysqli

Try this, I used it as below. Here change db_name = your original database ame

db.php (create a separate connection file)

$db_hostname = "localhost";  //usually "localhost be default"
$db_username = "root";  //your user name
$db_pass = "root";  //the password for your user
$db_name = "yourdatabasename";  //the name of the database

// connect to database
$db = new mysqli ($db_hostname, $db_username, $db_pass, $db_name);
if ($db->connect_error) {
trigger_error('Database connection failed: '  . $db->connect_error,   E_USER_ERROR);
}

Now this is your file

<?php
include('db.php');
//This is the directory where images will be saved
$target = "images/";
$target = $target . basename( $_FILES['photo']['name']);

//This gets all the other information from the form
$name= (isset($_POST['image_author']));
$description= ($_POST['image_description']);
$pic=($_FILES['photo']['name']);


// Connects to your Database
mysqli_select_db($db,"image_gallery") or die(mysqli_error($db)); 

//Writes the information to the database
$result = mysqli_query($db,"INSERT INTO images (image_author, image_description, image_pathname) VALUES ('$name', '$description', '$pic')");

//Writes the photo to the server
if(move_uploaded_file($_FILES['photo']['tmp_name'], $target))
{

//Tells you if its all ok
echo "The file has been uploaded, and your information has been added to the directory <p> <a href='upload.php'> Go back</a>";
}
else {

//Gives and error if its not
echo "Sorry, there was a problem uploading your file.";
}
?>

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

PHP Migrating from mysql_* to mysqli_

Ok, so in the first function you are trying to replace

return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');

Let's first make clear what this does:

specify query
fetch the result
get 0. row ("1st" in English)
get column user_id

Now do this step-by-step with mysqli_:

//specify query
$result = mysqli_query(connect(),"SELECT `user_id` FROM `users` WHERE `username` = '$username'");
//fetch result
$row = mysqli_fetch_assoc($result);
//get column
return $row['user_id'];

You don't need to specify the row as fetch_assoc returns only one.

Now for the second function

return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) ==1) ? $user_id : FALSE;

specify query
fetch result
get 0. row
if this equals 1: return user_id, otherwise FALSE

Now with mysqli_:

//specify query
$result = mysqli_query(connect(),"SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'");
//fetch result
$row = mysqli_fetch_row($result);
//if first returned column is equal to 1 return $user_id
//otherwise FALSE
return ($row[0]==1) ? $user_id : FALSE;

But wait - why did I use mysqli_fetch_row here whereas mysqli_fetch_assoc was used above? RTM ;)

What have we learned today? Only because you can write your code as short as possible doesn't mean you should. If the original code had been split up a bit more, the transition to MySQLi should have been quite easy, as you could have easily debugged smaller parts instead of a complex expression.

Need help converting MySQL to MySQLI in Wordpress

WordPress uses its own Database abstraction class, called WPDB, for use in WordPress and WordPress plugins. You can also find out more about the WordPress Database API. Further I took a look at the src/wp-includes/wp-db.php file which indicates WordPress will fallback on MYSQLi if needed.

Migrating to mysqli

you should use prepared statement, using that you can avoid sql-injection hack

$stmt = $mysqli->prepare("SELECT * FROM usuarios WHERE user=:user");
$stmt->bindParam(':user', $usuario_tienda);
$result = $stmt->execute(); 
$resultado_user = $result->fetch_assoc();
echo $resultado_user['tienda'];