From MySQL to MySQLi

[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
[syntax=php]mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');[/syntax]
In MySQLi, this gets converted to a class declaration that combines the two functions.
This
[syntax=php]mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');[/syntax]
Becomes this!
[syntax=php]$mysqli = new mysqli('127.0.0.1', 'tutorial', 'password', 'example_blog');[/syntax]
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
[syntax=php]global $mysqli;[/syntax]
$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.
[syntax=php]mysql_real_escape_string()[/syntax]
becomes
[syntax=php]$mysqli->real_escape_string()[/syntax]
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
[syntax=php]$query = mysql_query("SELECT * FROM `users` WHERE `user_id` = '{$id}'");
return mysql_fetch_assoc($query);[/syntax]
becomes
[syntax=php]$query = $mysqli->query("SELECT * FROM `users` WHERE `user_id` = '{$id}'");
return $query->fetch_assoc();[/syntax]

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:
[syntax=php]mysql_query()[/syntax]
becomes
[syntax=php]mysqli_query()[/syntax]


I am relatively new to MySQLi. If you have any questions, I'll be happy to try to help.

[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. [syntax=php]<?php function some_database_thing($mysqli)
{
some query code;
return something;
}
[/syntax]