Converting MYSQL to MYSQLI

james_martin_187 · July 19, 2015

Could anyone please help me to convert the below file from MYSQL to MYSQLI, I have replaced MYSQL with MYSQLI, however I have read about MYSQLI which requires two parameters as only one is required for MYSQL. Could someone please help me with the below file, as I need to convert it from MYSQL to MYSQLI or PDO. I have attached the file so that somebody is able to help me and convert it.DB_Functions.php

<?php

class DB_Functions {

    private $db;

    //put your code here
    // constructor
    function __construct() {
        require_once 'DB_Connect.php';
        // connecting to database
        $this->db = new DB_Connect();
        $this->db->connect();
    }

    // destructor
    function __destruct() {
        
    }


    /**
     * Random string which is sent by mail to reset password
     */

public function random_string()
{
    $character_set_array = array();
    $character_set_array[] = array('count' => 7, 'characters' => 'abcdefghijklmnopqrstuvwxyz');
    $character_set_array[] = array('count' => 1, 'characters' => '0123456789');
    $temp_array = array();
    foreach ($character_set_array as $character_set) {
        for ($i = 0; $i < $character_set['count']; $i++) {
            $temp_array[] = $character_set['characters'][rand(0, strlen($character_set['characters']) - 1)];
        }
    }
    shuffle($temp_array);
    return implode('', $temp_array);
}


public function forgotPassword($forgotpassword, $newpassword, $salt){
	$result = mysqli_query("UPDATE `users` SET `encrypted_password` = '$newpassword',`salt` = '$salt' 
						  WHERE `email` = '$forgotpassword'");

if ($result) {
 
return true;

}
else
{
return false;
}

}
/**
     * Adding new user to mysqli database
     * returns user details
     */

    public function storeUser($fname, $lname, $email, $uname, $password) {
        $uuid = uniqid('', true);
        $hash = $this->hashSSHA($password);
        $encrypted_password = $hash["encrypted"]; // encrypted password
        $salt = $hash["salt"]; // salt
        $result = $this->db->query("INSERT INTO users(unique_id, firstname, lastname, email, username, encrypted_password, salt, created_at) VALUES('$uuid', '$fname', '$lname', '$email', '$uname', '$encrypted_password', '$salt', NOW())");      
  // check for successful store
        if ($result) {
            // get user details 
            $uid = mysqli_insert_id(); // last inserted id
            $result = mysqli_query("SELECT * FROM users WHERE uid = $uid");
            // return user details
            return mysqli_fetch_array($result);
        } else {
            return false;
        }
    }

    /**
     * Verifies user by email and password
     */
    public function getUserByEmailAndPassword($email, $password) {
        $result = mysqli_query("SELECT * FROM users WHERE email = '$email'") or die(mysqli_error());
        // check for result 
        $no_of_rows = mysqli_num_rows($result);
        if ($no_of_rows > 0) {
            $result = mysqli_fetch_array($result);
            $salt = $result['salt'];
            $encrypted_password = $result['encrypted_password'];
            $hash = $this->checkhashSSHA($salt, $password);
            // check for password equality
            if ($encrypted_password == $hash) {
                // user authentication details are correct
                return $result;
            }
        } else {
            // user not found
            return false;
        }
    }

 
 /**
     * Check user is existed or not
     */
    public function isUserExisted($email) {
        $result = mysqli_query("SELECT email from users WHERE email = '$email'");
        $no_of_rows = mysqli_num_rows($result);
        if ($no_of_rows > 0) {
            // user existed 
            return true;
        } else {
            // user not existed
            return false;
        }
    }

    /**
     * Encrypting password
     * returns salt and encrypted password
     */
    public function hashSSHA($password) {

        $salt = sha1(rand());
        $salt = substr($salt, 0, 10);
        $encrypted = base64_encode(sha1($password . $salt, true) . $salt);
        $hash = array("salt" => $salt, "encrypted" => $encrypted);
        return $hash;
    }

    /**
     * Decrypting password
     * returns hash string
     */
    public function checkhashSSHA($salt, $password) {

        $hash = base64_encode(sha1($password . $salt, true) . $salt);

        return $hash;
    }

}

?>

scootstah · July 19, 2015

What is DB_Connect?

james_martin_187 · July 20, 2015

What is DB_Connect?

This is another file which is called DB_Connect.PHP

<?php
class DB_Connect {

    // constructor
    function __construct() {
        
    }

    // destructor
    function __destruct() {
        // $this->close();
    }

    // Connecting to database
    public function connect() {
        require_once 'include/config.php';
        // connecting to mysql
        $con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
        // selecting database
        mysqli_select_db($con, "DB_DATABASE");
		
     // return database handler
        return $con;
    }

    // Closing database connection
    public function close() {
        mysqli_close();
    }

}

?>

Config.PHP is the configuration file which has the database connection and the features such as the below.

<?php

/**
 * Database config variables
 */
define("DB_HOST", "127.0.0.1");
define("DB_USER", "User");
define("DB_PASSWORD"Password");
define("DB_DATABASE", "bradvisor_login_api");
?>

Edited July 20, 2015 by james_martin_187

Ch0cu3r · July 21, 2015

Ok so the DB_Connect object returns the mysqli instance. The mysqli instanced is stored in the $db property for the DB_Functions class on line 12

        $this->db = new DB_Connect();

In that case in your DB_functions class wherever you see mysqli_query( you replace it with either $this->db->query( or as mysqli_query($this->db,

Example for the query used in the forgotPassword method

$result = $this->db->query("UPDATE `users` SET `encrypted_password` = '$newpassword',`salt` = '$salt' 
						  WHERE `email` = '$forgotpassword'");

// or written as
$result = mysqli_query($this->db, "UPDATE `users` SET `encrypted_password` = '$newpassword',`salt` = '$salt'
        WHERE `email` = '$forgotpassword'");

Where you use mysqli_insert_id you need to pass the mysqli instance to it, eg mysqli_insert_id($this->db) or write it as $this->db->insert_id;

james_martin_187 · July 21, 2015

Ch0cu3r I have tried what you have told me to do, however I am now getting an error on line 72 which says syntax error, unexpected '$result' (T_VARIABLE) in C:\wamp\www\bradvisor_login_api\include\DB_Functions.php on line 72</th></tr>



// check for successful store

        if ($result) {

            // get user details 

            $uid = mysqli_insert_id($this->db) // last inserted id

            $result = $this->db->query("SELECT * FROM users WHERE uid = $uid");

            // return user details

            return mysqli_fetch_array($result);

        } else {

            return false;

        }

    }

Ch0cu3r · July 21, 2015

You have left off the semi-colon on line 71. It should be

            $uid = mysqli_insert_id($this->db); // last inserted id

Ch0cu3r · July 21, 2015

You don't need to convert those, they are fine as they are.

Their alternative syntax would be

$result->fetch_array();  // mysqli_fetch_array($result);
$result->num_rows;       // mysqli_num_rows($result);

See documentation

http://php.net/mysqli-result.fetch-array

http://php.net/mysqli-result.num-rows

Ideally in your code you should stick with using either the procedural or oop mysqli interface. You shouldn't really swap between the two interfaces.

james_martin_187 · July 21, 2015

Ch0cu3r I now get another error on line 67 which says the following Fatal error: Call to undefined method DB_Connect::query() in C:\wamp\www\bradvisor_login_api\include\DB_Functions.php on line 67</th></tr>.

/**
     * Adding new user to mysqli database
     * returns user details
     */

    public function storeUser($fname, $lname, $email, $uname, $password) {
        $uuid = uniqid('', true);
        $hash = $this->hashSSHA($password);
        $encrypted_password = $hash["encrypted"]; // encrypted password
        $salt = $hash["salt"]; // salt
        $result = $this->db->query("INSERT INTO users(unique_id, firstname, lastname, email, username, encrypted_password, salt, created_at) VALUES('$uuid', '$fname', '$lname', '$email', '$uname', '$encrypted_password', '$salt', NOW())");      
  // check for successful store
        if ($result) {
            // get user details 
            $uid = mysqli_insert_id($this->db); // last inserted id
            $result = $this->db->query("SELECT * FROM users WHERE uid = $uid");
            // return user details
            return mysqli_fetch_array($result);
        } else {
            return false;
        }
    }

james_martin_187 · July 21, 2015

When I use the below I get another error which says Warning: mysqli_query() expects parameter 1 to be mysqli, object given in C:\wamp\www\bradvisor_login_api\include\DB_Functions.php on line 67</th></tr>

 $result = mysqli_query($this->db, "unique_id, firstname, lastname, email, username, encrypted_password, salt, created_at) VALUES('$uuid', '$fname', '$lname', '$email', '$uname', '$encrypted_password', '$salt', NOW())");

scootstah · July 21, 2015

Ok so the DB_Connect object returns the mysqli instance. The mysqli instanced is stored in the $db property for the DB_Functions class on line 12
        $this->db = new DB_Connect();

No it's not. The DB_Connect constructor does not return anything, and he is not assigning to the return from connect().

You should do this instead:

$db = new DB_Connect();
$this->db = $db->connect();

Now $this->db will be the MySQLI object. Edited July 21, 2015 by scootstah

Ch0cu3r · July 21, 2015

No it's not. The DB_Connect constructor does not return anything, and he is not assigning to the return from connect().
You should do this instead:

Thnaks for correcting me, I was just about to correct myself and suggest just that

Also, you're connecting with the procedural functions so therefore you cannot use the object-oriented methods like you are trying to do ($this->db->query()). You either need to use procedural functions everywhere (mysqli_query($this->db, "SELECT ...")), or OOP everywhere.

Yes, You can use the object and procedural functions interchangeably like that. Though I do agree you should stick to one style.

scootstah · July 21, 2015

Yes, You can use the object and procedural functions interchangeably like that. Though I do agree you should stick to one style.

Yeah I tested it and I was wrong. You caught me before I edited.

james_martin_187 · July 21, 2015

Could anyone please advise which method shall I use (mysqli_query($this->db, or $result = $this->db->query, could you please advise?. I need to stick to one method so could you please rewrite line 67 for me.

$result = mysqli_query($this->db, "unique_id, firstname, lastname, email, username, encrypted_password, salt, created_at) VALUES('$uuid', '$fname', '$lname', '$email', '$uname', '$encrypted_password', '$salt', NOW())");

scootstah · July 21, 2015

Either will work, I misspoke before. Pick whichever flavor you prefer and be consistent.

james_martin_187 · July 23, 2015

I Now get an error on line 85 which says that Warning: mysqli_error() expects exactly 1 parameter, 0 given in C:\wamp\www\bradvisor_login_api\include\DB_Functions.php on line 85</.

/**
     * Verifies user by email and password
     */
    public function getUserByEmailAndPassword($email, $password) {
        $result = mysqli_query($this->db, "SELECT * FROM users WHERE email = '$email'") or die(mysqli_error());
        // check for result 
        $no_of_rows = mysqli_num_rows($result);
        if ($no_of_rows > 0) {
            $result = mysqli_fetch_array($result);
            $salt = $result['salt'];
            $encrypted_password = $result['encrypted_password'];
            $hash = $this->checkhashSSHA($salt, $password);
            // check for password equality
            if ($encrypted_password == $hash) {
                // user authentication details are correct
                return $result;
            }
        } else {
            // user not found
            return false;
        }
    }

Ch0cu3r · July 23, 2015

The error is saying you are missing an argument. What do you think needs to be passed as the argument?

See http://php.net/mysqli_error to see what the required argument is.

james_martin_187 · July 23, 2015

Ch0cu3r will the below work.

$result = mysqli_query($this->db, "SELECT * FROM users WHERE email = '$email'") or die(mysqli_error($this->db));

Ch0cu3r · July 23, 2015

Try it an find out :thumb-up:

james_martin_187 · July 23, 2015

Try it an find out

This has worked however I have another problem, I need to convert the below file to mysqli as well.

<?php
class DB_Connect {


    // constructor
    function __construct() {
        
    }


    // destructor
    function __destruct() {
        // $this->close();
    }


    // Connecting to database
    public function connect() {
        require_once 'include/config.php';
        // connecting to mysql
        $con = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
        // selecting database
        mysql_select_db(DB_DATABASE);


        // return database handler
        return $con;
    }


    // Closing database connection
    public function close() {
        mysql_close();
    }


}


?>

scootstah · July 23, 2015

Why? The last time you posted it, it was already MYSQLI.

james_martin_187 · July 23, 2015

Why? The last time you posted it, it was already MYSQLI.

I thought the below code is wrong as it does not select my database.

<?php
class DB_Connect {


    // constructor
    function __construct() {
        
    }


    // destructor
    function __destruct() {
        // $this->close();
    }


    // Connecting to database
    public function connect() {
        require_once 'include/config.php';
        // connecting to mysql
        $con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD);
        // selecting database
        mysqli_select_db($con, "DB_DATABASE");


     // return database handler
        return $con;
    }


    // Closing database connection
    public function close() {
        mysqli_close();
    }


}


?>

scootstah · July 23, 2015

mysqli_select_db($con, "DB_DATABASE");

Should be:

mysqli_select_db($con, DB_DATABASE);

Actually with MYSQLI you can just do everything in the constructor.

$con = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

james_martin_187 · July 23, 2015

I am now getting another error which says "Column count doesn't match value count at row 1"

Could you please help

$result = mysqli_query($this->db,"INSERT INTO users(`uid`, `unique_id`, `firstname`, `lastname`, `username`, `email`, `encrypted_password`, `salt`, `created_at`) VALUES('$uuid', '$fname', '$lname', '$email', '$uname', '$encrypted_password', '$salt', NOW())") or die(mysqli_error($db));

I have amended it as uid is auto_incremented

$result = mysqli_query($this->db,"INSERT INTO `users`(`unique_id`, `firstname`, `lastname`, `username`, `email`, `encrypted_password`, `salt`, `created_at`) VALUES('$uuid', '$fname', '$lname', '$email', '$uname', '$encrypted_password', '$salt', NOW())");

Edited July 23, 2015 by james_martin_187

scootstah · July 23, 2015

Man, at some point you're going to need to figure out how to solve problems. You can't just ask for help every time some little thing goes wrong in your program. Programming is about problem solving and troubleshooting. We're happy to help if you're truly stuck on something and have put forth due diligence to try to solve it.

Have you tried Googling your error, to see what it means?

james_martin_187 · July 23, 2015

Man, at some point you're going to need to figure out how to solve problems. You can't just ask for help every time some little thing goes wrong in your program. Programming is about problem solving and troubleshooting. We're happy to help if you're truly stuck on something and have put forth due diligence to try to solve it.

Have you tried Googling your error, to see what it means?

I have resolved the problem myself.

Sign In

Converting MYSQL to MYSQLI

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information