Note: Converting from mysql_ to mysqli_ may not be optimal. Consider PDO if you're prepared to convert all of your code to OOP.
It can be tempting to try to replace all instances of mysql_ with mysqli_ and pray it works. You'd be close but not quite on point.
Connecting to the database:
Fortunately, mysqli_connect works closely enough to mysql_query that you can just swap out their function names.
mysql_:
$con = mysql_connect($host, $username, $password);
mysqli_:
$con = mysqli_connect($host, $username, $password);
Selecting a database
Now, with most of the other functions in the mysqli_ library, you'll need to pass mysqli_select_db the database connection as its first parameter. Most of the mysqli_ functions require the connection object first.
For this function, you can just switch the order of the arguments you pass to the function. If you didn't pass it a connection object before, you have to add it as the first parameter now.
mysql_:
mysql_select_db($dbname, $con);
mysqli_:
mysqli_select_db($con, $dbname);
As a bonus, you can also pass the database name as the fourth parameter to mysqli_connect - bypassing the need to call mysqli_select_db.
$con = mysqli_connect($host, $username, $password, $dbname);
Sanitize user input
Using mysqli_real_escape_string is very similar to mysql_real_escape_string. You just need to pass the connection object as the first parameter.
mysql_:
$value1 = mysql_real_escape_string($input_string);
mysqli_:
$value1 = mysqli_real_escape_string($con, $input_string);
Very Important: Preparing and Running a Query
One reason the mysql_ functions were deprecated to begin with was their inability to handle prepared statements. If you simply convert your code to mysqli_ without taking this important step, you are subject to some of the largest weaknesses of the mysql_ functions.
It's worth reading these articles on prepared statements and their benefits:
Wikipedia - Prepared Statements
PHP.net - MySQLi Prepared Statements
Note: When using prepared statements, it's best to explicitly list each column you're attempting to query, rather than using the * notation to query all columns. This way you can ensure you've accounted for all of the columns in your call to mysqli_stmt_bind_result.
mysql_:
$query = 'SELECT * FROM table1 WHERE table1.col1=' . $value1 . '';
$result = mysql_query($query, $con);
while($row = mysql_fetch_assoc*$result)
{
$col1 = $row['col1'];
$col2 = $row['col2'];
echo $col1 . ' ' . $col2 . '<br />';
}
mysqli_:
$query = 'SELECT col1,col2 FROM table1 WHERE table1.col1=?';
if ($stmt = mysqli_prepare($link, $query)) {
/* pass parameters to query */
mysqli_stmt_bind_param($stmt, "s", $value1);
/* run the query on the database */
mysqli_stmt_execute($stmt);
/* assign variable for each column to store results in */
mysqli_stmt_bind_result($stmt, $col1, $col2);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
/*
on each fetch, the values for each column
in the results are automatically stored in
the variables we assigned using
"mysqli_stmt_bind_result"
*/
echo $col1 . ' ' . $col2 . '<br />';
}
/* close statement */
mysqli_stmt_close($stmt);
}
Showing errors
Showing errors works a little differently with mysqli_. mysqli_error requires the connection object as its first parameter. But what if the connection failed? mysqli_ introduces a small set of functions that don't require the connection object: the mysqli_connect_* functions.
mysql_:
if (!$con) {
die('Could not connect: ' . mysql_error());
}
if (!$result) {
die('SQL Error: ' . mysql_error());
}
mysqli_:
/* check connection error*/
if (mysqli_connect_errno()) {
die( 'Could not connect: ' . mysqli_connect_error() );
}
/* check query error */
if ($stmt = mysqli_prepare($link, $query)) {
// ... execute query
if (mysqli_stmt_error($stmt)) {
echo 'SQL Error: ' . mysqli_stmt_error($stmt);
}
}
