Converting MYSQL to MYSQLI

[james_martin_187]

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]

What is DB_Connect?

[james_martin_187]

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");
?>

[Ch0cu3r]

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]

 

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 <i>72</i></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]

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

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

[Ch0cu3r]

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]

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 <i>67</i></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]

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 <i>67</i></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]

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.

[Ch0cu3r]

 

 

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]

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]

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]

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

[james_martin_187]

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 <i>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]

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]

Ch0cu3r will the below work.

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

[Ch0cu3r]

Try it an find out 

[james_martin_187]

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]

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

[james_martin_187]

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]

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]

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())");

[scootstah]

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]

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.