Convert a mysql script to mysqli

[Paul_Withers]

Hi, I am trying to convert the register & login script from mysql to mysqli. I have converted the easy parts and have the connection to the database, but the following functions all need changing and I can't work out the correct solution mainly due to the deprecation of mysql_result()

The code that needs updating is
 

<?php
function user_count() {
	return mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `active` = 1"), 0);
}

function users_online() {
	return mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `logged_in` = 1"), 0);
}

function change_profile_image($user_id, $file_temp, $file_extn) {
	$file_path = 'images/profile/' . substr(md5(time()), 0, 10) . '.' . $file_extn;
	move_uploaded_file($file_temp, $file_path);
	mysql_query("UPDATE `users` SET `profile` = '" . mysql_real_escape_string($file_path) . "' WHERE `user_id` = " . (int)$user_id);
}

function has_access($user_id, $type) {
	$user_id 	= (int)$user_id;
	$type 		= (int)$type;
	
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_id` = $user_id AND `type` = $type"), 0) == 1) ? true : false;
}

function activate($email, $email_code) {
	$email 		= mysql_real_escape_string($email);
	$email_code = mysql_real_escape_string($email_code);
	
	if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) == 1) {
		mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
		return true;
	} else {
		return false;
	}
}

function user_exists($username) {
	$username = sanitize($username);
	$query = mysql_query("SELECT COUNT('user_id') FROM `users` WHERE `username` = '$username'");
	return (mysql_result($query, 0) == 1) ? true : false;
}

function email_exists($email) {
	$email = sanitize($email);
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email'"), 0) == 1) ? true : false;
}

function user_id_from_username($username) {
	$username = sanitize($username);
	return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');
}

function user_id_from_email($email) {
	$email = sanitize($email);
	return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `email` = '$email'"), 0, 'user_id');
}

function login($username, $password) {
	$user_id = user_id_from_username($username);
	
	mysql_query("UPDATE `users` SET `logged_in` = 1 WHERE `user_id` = $user_id");

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

?>

And here is what the converter gave me:

function user_count() {
return mysql_result(mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(`user_id`) FROM `users` WHERE `active` = 1"), 0);
}

function users_online() {
return mysql_result(mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(`user_id`) FROM `users` WHERE `logged_in` = 1"), 0);
}

function change_profile_image($user_id, $file_temp, $file_extn) {
    $file_path = 'images/profile/' . substr(md5(time()), 0, 10) . '.' . $file_extn;
    move_uploaded_file($file_temp, $file_path);
    mysql_query("UPDATE `users` SET `profile` = '" . mysql_real_escape_string($file_path) . "' WHERE `user_id` = " . (int)$user_id);
}

function has_access($user_id, $type) {
    $user_id     = (int)$user_id;
    $type         = (int)$type;
    
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_id` = $user_id AND `type` = $type"), 0) == 1) ? true : false;
}

function activate($email, $email_code) {
    $email         = mysql_real_escape_string($email);
    $email_code = mysql_real_escape_string($email_code);
    
    if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) == 1) {
        mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
        return true;
    } else {
        return false;
    }
}

function user_exists($username) {
    $username = sanitize($username);
    $query = mysql_query("SELECT COUNT('user_id') FROM `users` WHERE `username` = '$username'");
    return (mysql_result($query, 0) == 1) ? true : false;
}

function email_exists($email) {
    $email = sanitize($email);
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email'"), 0) == 1) ? true : false;
}

function user_id_from_username($username) {
    $username = sanitize($username);
    return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');
}

function user_id_from_email($email) {
    $email = sanitize($email);
    return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `email` = '$email'"), 0, 'user_id');
}

function login($username, $password) {
    $user_id = user_id_from_username($username);
    
    mysql_query("UPDATE `users` SET `logged_in` = 1 WHERE `user_id` = $user_id");

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

?>

Please could someone point me in the right direction here?

 

 

Also my site works perfectly well with MySQL, do I have to convert it to MySQLi?

 

Many Thanks

 

Paul

If anyone knows how to solve this, it would be much appreciated. I already have a website template and would prefer to continue with mysqli instead of PDO.

Many Thanks

Paul

[Psycho]

Do you "have" to convert? No. But, the mysql_ extension is deprecated and will be completely disabled in a future version of PHP. Assuming you are on a hosted server, your host could update to a newer version of PHP at some point that would break your code. Since it still works in current PHP and hosts typically don't upgrade right away (or maintain support for older versions) it's likely it won't be an issue for quite some time. But, it's much better to be proactive and do it when you have time instead of being surprised and having to scramble when it does happen.

 

You are simply using mysql_result to get the value from queries that should be returning one record with one value. There is a mysqli_ method, but it is a different beast from the mysql_result function. I think the code needs a complete overhaul, there is on simple solution - create your own function. Create a function that takes the result of the query and uses the available mysqli_ functions to get the first record, then return the first value. If you were to look at the manual for the mysqli_result method you would see that the first entry has a comment specifically related to your issue and provides the exact same suggestion and sample code. You can just add that function to your code and rename all the mysql_function calls to whatever you decide to name the function.

[Ch0cu3r]

 

 

my site works perfectly well with MySQL, do I have to convert it to MySQLi?

The mysql_ functions will continue to work yes, But they will be removed completely at some point from future versions of PHP. The PHP developers have not released when this will take place though. However it is strongly advised to upgrade to your code to mysqli functions. 

 

The mysqli functions provided extra functionally lacking from the mysql_* functions. Such as prepared statements. Which is a more secure way of handling user input within a query, rather than sanitizing using mysql_real_escape_string

 

 

A quick guide for updating your could would be as follows. But you should read the note bellow my post

 

To convert your code you are going to need to first convert the mysql_ functions over to their mysqli_ function counter part. Consult the mysqli documentation (HINT look out for the procedural examples) to see what the necessary changes maybe for each function.

 

The hard part will be converting mysql_result(). As there is no mysqli function with similar functionally. Instead you are going to need to come with your own functionality. What you'd do instead is use mysqli_fetch_row to get the result from the query and then return the first array item. Example code to convert may look like this

return mysql_result(mysql_query(/* query */), 0)

OR

return mysql_result(mysql_query(/* query */), 0, 'user_id');

.

Will need to be converted to something like this

$result = mysqli_query(/* MySQLi Object*/, /* MySQL Query */); // execute query
$field = mysqli_fetch_row($result);                            // get the result from the query
return $field[0];                                              // return the first array item (value of the first field retuned by the query)

.

However in other instances of your code where it looks like

return (mysql_result(mysql_query(/* query */), 0) == 1) ? true : false;

It needs to be written as the conversion example I gave above, but the  return $field[0]  line will need to be changed to

return $field[0] == 1 ? true : false;

NOTE: This is a quick and dirty fix to get your code to work with mysqli. But I would strongly advise you to convert your queries that use user input (variables used inside the query) to be converted to prepared statements. These are far more secure than using mysqli_real_escape_string to sanitize the user input. Also you should add sufficient error checking to your queries, such as making sure the query has returned a result and take necessary action when the query issues an error.