From MySQL to MySQLi

Any tutorials (or useful resources) should go in here.
Post Reply
User avatar
FrederickGeek8
Posts: 148
Joined: Wed Nov 30, 2011 10:31 pm

From MySQL to MySQLi

Post by FrederickGeek8 »

I wanted to make a video tutorial on this to try and make it easier, but I hate the sound of my own voice in recordings so it looks like that isn't going to happen...

Jacek's tutorials are really, really great but something that I have learned recently is that the method that he uses for accessing the database (mysql functions) are outdated and will soon be removed from PHP completely. So, I took it upon myself to try and help people make the jump into MySQLi.

The (Hard but) Professional Way
This method converts procedural statements (mysql_result, mysql_real_escape_string) into object-oriented statements!
Most functions are primarily the same but the way that you insert them into your code is different.

In your init.inc.php file you probably have two lines of code that looks like this
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
In MySQLi, this gets converted to a class declaration that combines the two functions.
This
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
Becomes this!
$mysqli = new mysqli('127.0.0.1', 'tutorial', 'password', 'example_blog');
This declaration MUST be assigned to a variable (in this case mysqli).

In the init.inc.php, we include other file that have functions inside of them. For each function that uses this database conection, at the top of the function we have to add
global $mysqli;
$mysqli can be replaced by whatever variable you assigned to the connection at init.inc.php. Making the variable usable inside the function allows us to run queries, sanitize inputs, and anything else we would normally do with MySQL functions

You can pretend that all the MySQL function get converted by adding an arrow sign and a dollar sign. That is really all you need to do to get each "new" function.
mysql_real_escape_string()
becomes
$mysqli->real_escape_string()
the only difference presents itself when you are using things that parse a database query. Something like mysql_results needs to be run off of the query so
$query = mysql_query("SELECT * FROM `users` WHERE `user_id` = '{$id}'");
return mysql_fetch_assoc($query);
becomes
$query = $mysqli->query("SELECT * FROM `users` WHERE `user_id` = '{$id}'");
return $query->fetch_assoc();
If you follow these steps, everything should come pretty easily with MySQLi.

The (Easy but) Lazy Way
If you have no need to go "pro" and you really don't want to use any object-oriented programming, MySQLi also had procedural version. This requires no further installion.
The syntax of MySQLi procedural is almost exactly the same so all you need to do is add an "i" to the function so:
mysql_query()
becomes
mysqli_query()

I am relatively new to MySQLi. If you have any questions, I'll be happy to try to help.
wrichards8
Posts: 66
Joined: Thu Jan 12, 2012 3:54 pm
Contact:

Re: From MySQL to MySQLi

Post by wrichards8 »

Good advice and, even though the mysql_ functions do work, people should be switching over. I switched from using MySQL to MySQLi a while back. You can also pass the connection variable as a function parameter which I usually do.
<?php function some_database_thing($mysqli)
{
      some query code;
     return something;
}
Post Reply