Connect to MySQL database using PHP OOP concept

Question

I'm writing a class and handful of functions to connect to the database and retrieve the information from the tables. I went through previous posts having similar titles, but most of them have written using mysql functions and I am using mysqli functions.

I want somebody who can go through this simple script and let me know where I am making my mistake.

This is my class.connect.php:

<?php

    class mySQL{
        var $host;
        var $username;
        var $password;
        var $database;
        public $dbc;

        public function connect($set_host, $set_username, $set_password, $set_database)
        {
            $this->host = $set_host;
            $this->username = $set_username;
            $this->password = $set_password;
            $this->database = $set_database;

            $this->dbc = mysqli_connect($this->host, $this->username, $this->password,           $this->database) or die('Error connecting to DB');        
        }

        public function query($sql)
        {
            return mysqli_query($this->dbc, $sql) or die('Error querying the Database');
        }

        public function fetch($sql)
        {        
            $array = mysqli_fetch_array($this->query($sql));          
            return $array;
        }

        public function close()
        {
            return mysqli_close($this->dbc);
        }
    }
    ?>

This is my index.php:

<?php
        require_once ("class.connect.php");

        $connection = new mySQL();

        $connection->connect('localhost', 'myDB', 'joker', 'names_list');
        $myquery = "SELECT * FROM list";
        $query = $connection->query($myquery);        

        while($array = $connection->fetch($query))
        {
            echo $array['first_name'] . '<br />';
            echo $array['last_name'] . '<br />';                
        }

        $connection->close();
?>

I am getting the error saying that Error querying the Database.

Solution 1

The problem is either this:

    public function fetch($sql)
    {        
        $array = mysqli_fetch_array($this->query($sql));          
        return $array;
    }

or this:

 while($array = $connection->fetch($query))

Because you are using the result from the query to query again. Basically, you are doing:

$r = mysqli_query($this->dbc, $sql);
$array = mysqli_fetch_array(mysqli_query($this->dbc, $r));

And you are getting an error, because $r is not a query string. When it's converted to a string, it's a "1" (from your other comment).

Try changing the function to (changed name of variable so you can see the difference):

    public function fetch($result)
    {        
        return mysqli_fetch_array($result);
    }

or just call the function directly.

Solution 2

Few problems :-

1. you don't die without provide a proper mysql error (and is good practice to exit gracefully)

2. fetch method is only FETCH the first row

3. mysqli have OO method, why you still using procedural function?

Solution 3

If you don't do your own db abstraction for learning php and mysql, you can use Medoo (http://medoo.in/).

It's a free and tiny db framework, that could save a huge work and time.

Solution 4

Try to check this

https://pramodjn2.wordpress.com/

$database = new db();

$query = $database->select(‘user’);

$st = $database->result($query);

print_r($st);

  class db {
                public $server = ‘localhost';
                public $user = ‘root';
                public $passwd = ‘*****';
                public $db_name = ‘DATABASE NAME';
                public $dbCon;

        public function __construct(){
                $this->dbCon = mysqli_connect($this->server, $this->user, $this->passwd, $this->db_name);
        }

        public function __destruct(){
                mysqli_close($this->dbCon);
        }

    /* insert function table name, array value
       $values = array(‘first_name’ => ‘pramod’,’last_name’=> ‘jain’);
    */            
       public function insert($table,$values)
       {
            $sql = “INSERT INTO $table SET “;
               $c=0;
            if(!empty($values)){
                foreach($values as $key=>$val){
                    if($c==0){
                        $sql .= “$key='”.htmlentities($val, ENT_QUOTES).”‘”;
                    }else{
                        $sql .= “, $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                    }
                    $c++;
                }
            }else{
              return false;
            }
            $this->dbCon->query($sql) or die(mysqli_error());
            return mysqli_insert_id($this->dbCon);
     }

     /* update function table name, array value
        $values = array(‘first_name’ => ‘pramod’,’last_name’=> ‘jain’);
        $condition = array(‘id’ =>5,’first_name’ => ‘pramod!’);
     */        
     public function update($table,$values,$condition)
     {
        $sql=”update $table SET “;
        $c=0;
        if(!empty($values)){
            foreach($values as $key=>$val){
                if($c==0){
                    $sql .= “$key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }else{
                    $sql .= “, $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }
                $c++;
            }
        }
        $k=0;    
        if(!empty($condition)){
            foreach($condition as $key=>$val){
                if($k==0){
                    $sql .= ” WHERE $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }else{
                    $sql .= ” AND $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }
                $k++;
            }
        }else{
          return false;
        }
        $result = $this->dbCon->query($sql) or die(mysqli_error());
        return $result;
     }

     /* delete function table name, array value
        $where = array(‘id’ =>5,’first_name’ => ‘pramod’);
     */    
    public function delete($table,$where)
    {
        $sql = “DELETE FROM $table “;
        $k=0;    
        if(!empty($where)){
            foreach($where as $key=>$val){
                if($k==0){
                    $sql .= ” where $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }else{
                    $sql .= ” AND $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }
                $k++;
            }
        }else{
            return false;
        }
           $del = $result = $this->dbCon->query($sql) or die(mysqli_error());
            if($del){
               return true;
            }else{
               return false;
            }
        }


    /* select function
       $rows = array(‘id’,’first_name’,’last_name’);
       $where = array(‘id’ =>5,’first_name’ => ‘pramod!’);
       $order = array(‘id’ => ‘DESC’);
       $limit = array(20,10);
    */
    public function select($table, $rows = ‘*’, $where = null, $order = null, $limit = null)
    {
       if($rows != ‘*’){
         $rows = implode(“,”,$rows);
       }

        $sql = ‘SELECT ‘.$rows.’ FROM ‘.$table;
        if($where != null){
            $k=0;
            foreach($where as $key=>$val){
                if($k==0){
                    $sql .= ” where $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }else{
                    $sql .= ” AND $key='”.htmlentities($val, ENT_QUOTES).”‘”;
                }
                $k++;
            }    
        }

        if($order != null){
            foreach($order as $key=>$val){
                    $sql .= ” ORDER BY $key “.htmlentities($val, ENT_QUOTES).””;
            }    
        }    

      if($limit != null){
             $limit = implode(“,”,$limit);
             $sql .= ” LIMIT $limit”;

        }
        $result = $this->dbCon->query($sql);
        return $result;

    }  

    public function query($sql){
    $result = $this->dbCon->query($sql);
    return $result;
    }

    public function result($result){
    $row = $result->fetch_array();
    $result->close();
    return $row;
    }

    public function row($result){
    $row = $result->fetch_row();
    $result->close();
    return $row;
    }

    public function numrow($result){
    $row = $result->num_rows;
    $result->close();
    return $row;
    }

 }

Solution 5

The mysqli_fetch_array function in your fetch method requires two parameters which are the SQL result and the kind of array you intend to return. In my case i use MYSQLI_ASSOC.

That is it should appear like this:

public function fetch($sql) {
$array = mysqli_fetch_array($this->query($sql), MYSQLI_ASSOC);
return $array; }

View more solutions

Comments

[125369]

thanks for your reply, I tried your way to find out what the error really means and it is saying "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1". Any idea why this is happening?

[Wesley van Opdorp]

Can you change it to: or die('Error:'.mysqli_error($this->dbc).', query: '.$sql); and tell us the query that it fails on?

[125369]

Hi Wesley van Opdorp, I followed your advice and this is what I am getting as error message "Error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1, query: 1"

[Your Common Sense]

In what ancient tomb did you unearthed it?