asked on

mysqli

I am converting a 10 year old program that uses "mysql_connect" and I want to use mysqli procedural without having to re-write the entire program. The program has a DB class but when I try to use mysqli none of my queries appear to work.

The commented code is mysqli that does not appear to work.

here is my db.php file:

<?php
//Create mysql databse transaction class
class db {
        var $con; //Connection Identifer
        var $host; //Host name localhost
        var $username; // database usernmae
        var $password; // database password
        var $dbname; // database name
        var $dbtype; // database type now I have created only support mysql functions.
        var $result;
        function db($host,$username,$password,$dbname,$dbtype){ //construct db class and initilaize primary variables.
                $this->host = $host;
                $this->username = $username;
                $this->password = $password;
                $this->dbname = $dbname;
                $this->dbtype = $dbtype;
        }

        function dbConnect(){ // mysql normal connect
                if($this->dbtype=="mysql"){
                        $this->con=mysql_connect($this->host, $this->username, $this->password) or die("Connection Error"  . mysql_error());
                        mysql_select_db($this->dbname);

                        //$this->con=mysqli_connect($this->host, $this->username, $this->password) or die("Connection Error"  . mysqli_error());
                        //mysqli_select_db($this->dbname);

                        return $this->con;
                }
        }
        function Query($sql){ //execute query and return output
                if($this->dbtype=="mysql"){
                        $this->result =mysql_query($sql) or die("Mysql query error - " . mysql_error() . "<!--[$sql]-->");
                        //$this->result =mysqli_query($sql)  or die("Mysql query error - " . mysqli_error() . "$sql");
                        return $this->result;
                }
        }
        function GetObject($result){ //fetch Object type query result
                if($this->dbtype=="mysql"){
                        return mysql_fetch_object($result);
                        //return $mysqli_fetch_object($result);
                }
        }
        function GetRow($result){ //fetch Row type query result
                if($this->dbtype=="mysql"){
                        return mysql_fetch_row($result);
                        //return $mysqli_fetch_row($result);
                }
        }
        function GetArray($result){
                if($this->dbtype=="mysql"){ //fetch Array type query result
                        return mysql_fetch_array($result);
                        //return $mysqli_fetch_array($result);
                }
        }
        function Execute($sql){ //Execuat mysql query for non return result queries ie. Update, Delete and Insert
                if($this->dbtype=="mysql"){
                        mysql_query($sql) or die("Mysql query error - " . mysql_error() . "<!--[$sql]-->");
                        //mysqli_query($sql) or die("Mysql query error - " . mysqli_error() . "<!--[$sql]-->");
                }
        }
        function LastInsertID(){ //return mysql last return id
                if($this->dbtype=="mysql"){
                        return mysql_insert_id();
                        //return mysqli_insert_id();
                }
        }
        function AffectedRows(){ //return mysql last affected rows
                if($this->dbtype=="mysql"){
                        return mysql_affected_rows($this->con);
                        //return mysqli_affected_rows($this->con);
                }
        }
        function NumRows($result){ // return total number of fetch result
                if($this->dbtype=="mysql"){
                        return mysql_num_rows($result);
                        //return mysqli_num_rows($result);
                }
        }
        function MysqlClose(){ // mysql close for the end of the mysql connection but it should need for mysql normat connect
                if($this->dbtype=="mysql"){
                        mysql_close($this->con);
                        //mysqli_close($this->con);
                }
        }
}
?>

Open in new window

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

Here's a little more of a 21st century implementation. I'm not sure of the intended use of $result vs $this->result. You might want to watch out for that. The choice would depend on whether you wanted to have a variable you could pass around in your main code (outside of this object).

<?php // demo/temp_robert_saylor.php
/**
 * https://www.experts-exchange.com/questions/28992086/mysqli.html
 */
error_reporting(E_ALL);

Class DB
{
    public $con; //Connection Identifer
    public $host; //Host name localhost
    public $username; // database usernmae
    public $password; // database password
    public $dbname; // database name
    public $dbtype; // database type now I have created only support mysql functions.
    public $result;

    public function __construct($host,$username,$password,$dbname,$dbtype='mysql'){ 
        $this->host = $host;
        $this->username = $username;
        $this->password = $password;
        $this->dbname = $dbname;
        $this->dbtype = $dbtype;
    }

    public function dbConnect(){
        if($this->dbtype=="mysql"){
            $this->con = new mysqli($this->host, $this->username, $this->password, $this->dbname);
            if ($this->con->connect_errno)
            {
                $err
                = "CONNECT FAIL: "
                . $this->con->connect_errno
                . ' '
                . $this->con->connect_error
                ;
                trigger_error($err, E_USER_ERROR);
            }

            return $this->con;
        }
    }

    public function Query($sql){
        if($this->dbtype=="mysql"){
            $this->result = $this->con->query($sql);
            if (!$this->result)
            {
                $err
                = "QUERY FAIL: "
                . $sql
                . ' ERRNO: '
                . $this->con->errno
                . ' ERROR: '
                . $this->con->error
                ;
                trigger_error($err, E_USER_ERROR);
            }

            return $this->result;
        }
    }

    // BEWARE: DO YOU WANT $result OR $this->result ??
    public function GetObject($result){
        if($this->dbtype=="mysql"){
            return $result->fetch_object();
        }
    }

    /** OMIT THIS FUNCTION - NOBODY DOES THIS ANY MORE
    function GetRow($result){
        if($this->dbtype=="mysql"){
            return mysql_fetch_row($result);
            //return $mysqli_fetch_row($result);
        }
    }
    **/

    // BEWARE: DO YOU WANT $result OR $this->result ??
    public function GetArray($result){
        if($this->dbtype=="mysql"){
            return $result->fetch_array();
        }
    }

    public function Execute($sql){
        if($this->dbtype=="mysql"){
            $res = $this->con->query($sql);
            if (!$res)
            {
                $err
                = "QUERY FAIL: "
                . $sql
                . ' ERRNO: '
                . $this->con->errno
                . ' ERROR: '
                . $this->con->error
                ;
                trigger_error($err, E_USER_ERROR);
            }

            return $res;
        }
    }

    public function LastInsertID(){
        if($this->dbtype=="mysql"){
            return $this->con->insert_id;
        }
    }

    public function AffectedRows(){
        if($this->dbtype=="mysql"){
            return $this->con->affected_rows;
        }
    }

    // BEWARE: DO YOU WANT $result OR $this->result ??
    public function NumRows($result){
        if($this->dbtype=="mysql"){
            return $result->num_rows;
        }
    }

    /** UNNECESSARY - THE SCRIPT TERMINATOR WILL TAKE CARE OF THIS
    public function MysqlClose(){
        if($this->dbtype=="mysql"){
            unset($this->con);
        }
    }
    **/
}

// TRY IT WITH YOUR CREDENTIALS
$db_host = "???";       
$db_user = "???";
$db_word = "???";
$db_name = "???";    

$db = new DB($db_host, $db_user, $db_word, $db_name);
$db->dbConnect();
$res = $db->query("SELECT 2+2 AS num");
$ans = $db->getObject($res);
var_dump($ans);

Open in new window

Robert Saylor

ASKER

Thanks will re-write this to OO in the near future before we put PHP 7 on the servers. Got a little time left but not much. I agree with the OO. This is a old program I inherited.