The CodexWorld DB Class

[iwato]

<?php
/*
 * DB Class
 * This class is used for database related (connect, insert, update, and delete) operations
 * @author    CodexWorld.com
 * @url       http://www.codexworld.com
 * @license   http://www.codexworld.com/license
 */
class DB{
    private $dbHost     = "...";
    private $dbUsername = "...";
    private $dbPassword = "...";
    private $dbName     = "...";
    
    public function __construct(){
        if(!$this->db){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
    
    /*
     * Returns rows from the database based on the conditions
     * @param string name of the table
     * @param array select, where, order_by, limit and return_type conditions
     */
    public function getRows($table,$conditions = array()){
        $sql = 'SELECT ';
        $sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
        $sql .= ' FROM '.$table;
        if(array_key_exists("where",$conditions)){
            $sql .= ' WHERE ';
            $i = 0;
            foreach($conditions['where'] as $key => $value){
                $pre = ($i > 0)?' AND ':'';
                $sql .= $pre.$key." = '".$value."'";
                $i++;
            }
        }
        
        if(array_key_exists("order_by",$conditions)){
            $sql .= ' ORDER BY '.$conditions['order_by']; 
        }
        
        if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            $sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
        }elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            $sql .= ' LIMIT '.$conditions['limit']; 
        }
        
        $result = $this->db->query($sql);
        
        if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
            switch($conditions['return_type']){
                case 'count':
                    $data = $result->num_rows;
                    break;
                case 'single':
                    $data = $result->fetch_assoc();
                    break;
                default:
                    $data = '';
            }
        }else{
            if($result->num_rows > 0){
                while($row = $result->fetch_assoc()){
                    $data[] = $row;
                }
            }
        }
        return !empty($data)?$data:false;
    }
 }

			$test = new DB();
			if ($test instanceof DB) {
				echo 'Instantiated';
			} else {
				echo 'Uninstantiated.  Include failed.';
			}
			$conditions = [];
			$tbl_name = 'rss2_podcast_item';
			$test->getRows($tbl_name, $conditions);

Quote

Call to a member function query() on a non-object ...

			function getRows($mysqli_obj, $table,$conditions = array()){
				$sql = 'SELECT ';
				$sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
				$sql .= ' FROM '.$table;
				if(array_key_exists("where",$conditions)){
					$sql .= ' WHERE ';
					$i = 0;
					foreach($conditions['where'] as $key => $value){
						$pre = ($i > 0)?' AND ':'';
						$sql .= $pre.$key." = '".$value."'";
						$i++;
					}
				}
		
				if(array_key_exists("order_by",$conditions)){
					$sql .= ' ORDER BY '.$conditions['order_by']; 
				}
		
				if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
					$sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
				}elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
					$sql .= ' LIMIT '.$conditions['limit']; 
				}
		
				$result = $mysqli_obj->query($sql);
		
				if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
					switch($conditions['return_type']){
						case 'count':
							$data = $result->num_rows;
							break;
						case 'single':
							$data = $result->fetch_assoc();
							break;
						default:
							$data = '';
					}
				}else{
					if($result->num_rows > 0){
						while($row = $result->fetch_assoc()){
							$data[] = $row;
						}
					}
				}
				return !empty($data)?$data:false;
			}
		?>
		<?php
			$tbl_name = 'rss2_podcast_item';
			$conditions = [];
			print_r(getRows($mysqli_obj,$tbl_name,$conditions));
		?>

Please find above two sets of code and a quoted error message from the first set. 

The first set of code is a portion of a PHP class called DB.  The second set of code is extracted from the first and modified in order to test the integrity of the function. At the bottom of each block of code is included the code necessary to call the getRows() method/function.  Whereas the method of the class fails, the extracted function does not.  In both cases the same database and table are accessed with success.

I am suspicious of the way in which the class is constructed.  I have tried several modifications, but none of them appear to work.

Roddy

 

[justsomeguy]

That class doesn't seem all that useful, it doesn't provide any protection and really only gives a way to send an array of conditions for a select query.  I would suggest using PDO instead, or looking into prepared statements with mysqli and using those.  The first error message sounds like the database connection failed.

[iwato]

Yes, I agree.  It appears that database connection is failing, but why?  My test for instantiation proves positive.  

Can you explain the likely meaning of the following statement?

$this->db

It does not appear to refer to anything.  Is it possible for it to refer to something that could appear later in the class.  I have only exhibited a portion of the entire class.  Also, what kinds of protection were you thinking of?  All of the parameters are private.

Please respond before the weekend begins, and have a good weekend yourself.  You have been very helpful.

Edited September 22, 2017 by iwato

[justsomeguy]

The connection is failing because one or more of the host name, username, password, or database name are incorrect.  In that case though, it should print a connection error message.  Does the error message you see refer to the line that calls the query method on $this->db?

Can you explain the likely meaning of the following statement?

That's how you refer to an object member.

http://php.net/manual/en/language.oop5.properties.php

I have only exhibited a portion of the entire class.

Well, I can only comment on what I'm seeing.  If the code you showed above is all that's there, then it shouldn't ever show that error.  The db object gets created in the constructor and then used in the getRows method, there's nothing else that would cause a problem.  So, yes, the cause of the error message is likely somewhere that I haven't seen.

Also, what kinds of protection were you thinking of?

Protection against SQL injection attacks.  Based on what's there, and again, apparently it's not everything, but based on what's there it's up to you to sanitize all of the values.  If you used prepared statements then you don't need to sanitize anything to protect the database.

All of the parameters are private.

That's just class member visibility.  Public, protected, or private members aren't going to affect database security.

[iwato]

I am now able to connect to the database via the DB class, and the error message for the statement

        if(!$this->db){

 

Quote

Notice: Undefined property: DB::$db in /Users/kiusau/Sites/reflexive/javascript_practice/ajax/crud/db.php on line 16

Although I agree that the property is undefined, I do not understand the purpose of the statement in the first place.

Also, what do you mean by the term prepared statements?

Edited September 23, 2017 by iwato

[iwato]

Please find below a copy of the entire class and a list of the occurrences of the property call $this->db.

In the end can you explain the overall use of this statement?  Why, for example, would $this->db not be listed as $db in the parameter list at the outset?  Is this because it is a working variable within the class that takes only dynamically assigned values?

~/Sites/reflexive/javascript_practice/ajax/crud/db.php:22:                 $this->db = $conn;
~/Sites/reflexive/javascript_practice/ajax/crud/db.php:56:         $result = $this->db->query($sql);
~/Sites/reflexive/javascript_practice/ajax/crud/db.php:102:             $insert = $this->db->query($query);
~/Sites/reflexive/javascript_practice/ajax/crud/db.php:103:             return $insert?$this->db->insert_id:false;
~/Sites/reflexive/javascript_practice/ajax/crud/db.php:138:             $update = $this->db->query($query);
~/Sites/reflexive/javascript_practice/ajax/crud/db.php:139:             return $update?$this->db->affected_rows:false;
~/Sites/reflexive/javascript_practice/ajax/crud/db.php:162:         $delete = $this->db->query($query);

<?php
/*
 * DB Class
 * This class is used for database related (connect, insert, update, and delete) operations
 * @author    CodexWorld.com
 * @url       http://www.codexworld.com
 * @license   http://www.codexworld.com/license
 */
class DB{
    private $dbHost     = "...";
    private $dbUsername = "...";
    private $dbPassword = "...";
    private $dbName     = "...";
    
    public function __construct(){
        if($this->dbName){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
    
    /*
     * Returns rows from the database based on the conditions
     * @param string name of the table
     * @param array select, where, order_by, limit and return_type conditions
     */
    public function getRows($table,$conditions = array()){
        $sql = 'SELECT ';
        $sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
        $sql .= ' FROM '.$table;
        if(array_key_exists("where",$conditions)){
            $sql .= ' WHERE ';
            $i = 0;
            foreach($conditions['where'] as $key => $value){
                $pre = ($i > 0)?' AND ':'';
                $sql .= $pre.$key." = '".$value."'";
                $i++;
            }
        }
        
        if(array_key_exists("order_by",$conditions)){
            $sql .= ' ORDER BY '.$conditions['order_by']; 
        }
        
        if(array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            $sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
        }elseif(!array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            $sql .= ' LIMIT '.$conditions['limit']; 
        }
        
        $result = $this->db->query($sql);
        
        if(array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
            switch($conditions['return_type']){
                case 'count':
                    $data = $result->num_rows;
                    break;
                case 'single':
                    $data = $result->fetch_assoc();
                    break;
                default:
                    $data = '';
            }
        }else{
            if($result->num_rows > 0){
                while($row = $result->fetch_assoc()){
                    $data[] = $row;
                }
            }
        }
        return !empty($data)?$data:false;
    }
    
    /*
     * Insert data into the database
     * @param string name of the table
     * @param array the data for inserting into the table
     */
    public function insert($table,$data){
        if(!empty($data) && is_array($data)){
            $columns = '';
            $values  = '';
            $i = 0;
            if(!array_key_exists('created',$data)){
                $data['created'] = date("Y-m-d H:i:s");
            }
            if(!array_key_exists('modified',$data)){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            foreach($data as $key=>$val){
                $pre = ($i > 0)?', ':'';
                $columns .= $pre.$key;
                $values  .= $pre."'".$val."'";
                $i++;
            }
            $query = "INSERT INTO ".$table." (".$columns.") VALUES (".$values.")";
            $insert = $this->db->query($query);
            return $insert?$this->db->insert_id:false;
        }else{
            return false;
        }
    }
    
    /*
     * Update data into the database
     * @param string name of the table
     * @param array the data for updating into the table
     * @param array where condition on updating data
     */
    public function update($table,$data,$conditions){
        if(!empty($data) && is_array($data)){
            $colvalSet = '';
            $whereSql = '';
            $i = 0;
            if(!array_key_exists('modified',$data)){
                $data['modified'] = date("Y-m-d H:i:s");
            }
            foreach($data as $key=>$val){
                $pre = ($i > 0)?', ':'';
                $colvalSet .= $pre.$key."='".$val."'";
                $i++;
            }
            if(!empty($conditions)&& is_array($conditions)){
                $whereSql .= ' WHERE ';
                $i = 0;
                foreach($conditions as $key => $value){
                    $pre = ($i > 0)?' AND ':'';
                    $whereSql .= $pre.$key." = '".$value."'";
                    $i++;
                }
            }
            $query = "UPDATE ".$table." SET ".$colvalSet.$whereSql;
            $update = $this->db->query($query);
            return $update?$this->db->affected_rows:false;
        }else{
            return false;
        }
    }
    
    /*
     * Delete data from the database
     * @param string name of the table
     * @param array where condition on deleting data
     */
    public function delete($table,$conditions){
        $whereSql = '';
        if(!empty($conditions)&& is_array($conditions)){
            $whereSql .= ' WHERE ';
            $i = 0;
            foreach($conditions as $key => $value){
                $pre = ($i > 0)?' AND ':'';
                $whereSql .= $pre.$key." = '".$value."'";
                $i++;
            }
        }
        $query = "DELETE FROM ".$table.$whereSql;
        $delete = $this->db->query($query);
        return $delete?true:false;
    }
}

 

[iwato]

Quote

Also, what do you mean by the term prepared statements?

Never mind, please.  I have since discovered the meaning and use of prepared statements.  I have employed them on my own pages in the past.  Simply I was not thinking about security when I employed them and did not make the connection between the mysqli_stmt object and the prepare( ) statement in this context.

By the way, I have since downloaded a different CRUD suite that does not use Bootstrap and Mysqli PDO,  but does use prepared statements.

Please respond to the rest of the question, however.

Roddy

[justsomeguy]

Why, for example, would $this->db not be listed as $db in the parameter list at the outset?

It should be, it's kind of lazy to leave it out.  It should be listed along with the visibility.  They don't need to give it a value in that list, they can just specify the visibility.