Jump to content

changing from mysql to mysqli

Recommended Posts

Hi All

I am busy changing from mysql to mysqli on my old scrips and I have one problem that I cannot seem to solve.


// checks a given username and password combination.
function validate_credentials($user_name, $user_password) {
	$conn = mysqli_connect("localhost", "root", "", "hebron_college");

	$user_name = mysqli_real_escape_string($conn, $user_name);
	$user_password = sha1($user_password);
	$result = mysqli_query($conn, "SELECT * FROM users WHERE user_name = '$user_name' AND user_password = '$user_password'");
   $num = mysqli_num_rows($result);
   echo 'number' . $num;
	if (mysqli_num_rows($result) != 1){
		return false;
	return mysqli_result($result, 0);

I have done some research and I believe there is no mysqli_result function.

How would I be able to replace the mysql_result line in mysqli

Share this post

Link to post
Share on other sites

You researched it? Really?


It would help if you read the manual.



As long as you are re-writing your code you should use PDO instead. https://phpdelusions.net/pdo


You have more problems than just changing to mysqli. You need to use password_hash and password_verify. Sha1 is long obsolete for passwords.


Additionally, you never ever put variables in a query. You need to use prepared statements.


You also need to specify the columns you want. Do not SELECT *


Any code that was written with the obsolete mysql_* needs a complete rewrite from the ground up. You cannot just throw an 'i' onto mysql and call it updated code.

Edited by benanamen

Share this post

Link to post
Share on other sites

I recommend you forget about mysqli and switch directly to PDO. It's much more programmer-friendly and much more versatile. I've seen quite a lot of people try to rewrite their old mysql_* code to mysqli, and it rarely worked out. mysqli is a hardcore low-level interface for nerds who love to study manuals, and let's be honest here, that's not what the average PHP programmer is.


The code has serious issues and is going to need more than adding an “i” to the mysql_* calls.

  • You do not establish a new database connection for every single query. This is insanely inefficient and leads to duplicate code all over the place.
  • Hashing passwords with SHA-1 has become a joke since standard PCs can calculate billions of hashes per second. We use bcrypt now which is available through the PHP password hash API.
  • Get rid of those num_rows gymnastics. Just fetch the data.

This is how a sane version of your code might look like:


 * Checks user credentials and obtains the user ID
 * @param PDO    $database_connection the connection to the database holding the user records
 * @param string $name                the username
 * @param string $password            the plaintext password
 * @return integer|null the user ID, or null if the credentials are wrong
function authenticate_user(PDO $database_connection, $name, $password)
    // use a prepared statement to prevent SQL injection attacks as well as syntax problems
    $user_stmt = $database_connection->prepare('
            user_id,  -- what do you want? the ID? then explicitly select it
            user_name = :name
        'name' => $name,
    $user = $user_stmt->fetch();

    if ($user && password_verify($password, $user['user_password']))
        return $user['user_id'];
        return null;
  • Like 1

Share this post

Link to post
Share on other sites

How would I be able to replace the mysql_result line in mysqli


In case you are not quite ready to switch to PDO (which I would also recommend changing to), you could do something like this:

$result = mysqli_query($conn, "SELECT * FROM users WHERE user_name = '$user_name' AND user_password = '$user_password'");
if($row = mysqli_fetch_row($result)) {
    return $row[0];
} else {
    return false;
Note that you may need to modify the query so that the column you are interested in appears first. Since the above solution (and mysql_result) only returns one column, you might as well specify the column. For example:
SELECT id FROM users WHERE user_name = '$user_name' AND user_password = '$user_password'

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.