1

I have a db class that I've been using for years. I noticed something interesting today.

function example_function($key)
{
    global $db; // db variable - class
    $a_id = 1; // example
    $admins = $db->table('admins')->where('id','=',$a_id)->order('id','desc')->limit(1)->get();
    $admin_data = $admins['data'][0];
    $return = $admin_data[$key];
    return $return;
}

$id = example_function('id');
$new = $db->table('rooms')->where('id','=',$id)->order('id','desc')->limit(2)->get();
print_r($new);
//WORKS


$new = $db->table('rooms')->where('id','=',2)->order('id','desc')->limit(2)->get();
print_r($new);
//WORKS

$new = $db->table('rooms')->where('id','=',example_function('id'))->order('id','desc')->limit(2)->get();
print_r($new);
//NOT WORKING ?

->where('id','=',example_function('id'))->

When I use an external function in this part, things get messy.

But the following also works. When doing another operation with the db in the relevant function, things get messy.

function example_function($key)
{
    return '1';
}


$new = $db->table('rooms')->where('id','=',example_function('id'))->order('id','desc')->limit(2)->get();

Is the problem in global usage? Or is the database class wrong?

DB CLASS

class DB
{
    protected $connect;
    protected $db_database = DB_DATABASE;
    protected $db_host = DB_HOST;
    protected $db_username = DB_USERNAME;
    protected $db_password = DB_PASSWORD;
    protected $db_name = DB_NAME;
    protected $db_prefix = DB_PREFIX;
    
    
    //parameters
    
    protected $error = null;
    protected $last_id = null;
    protected $query = null;
    protected $from = null;
    protected $select = '*';
    protected $where = null;
    protected $group = null;
    protected $order = null;
    protected $limit = null;
    protected $pagination_type = false;
    protected $per_page = 0;
    protected $get_arr = null;
    function __construct() 
    {      
        try {
             $this->connect = new PDO("{$this->db_database}:host={$this->db_host};dbname={$this->db_name};charset=utf8mb4", "{$this->db_username}", "{$this->db_password}");
             $this->connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
             $this->connect->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8mb4");
        } catch ( PDOException $e ){
             echo  "<b>Veritabanı bağlantısı sağlanamadı! - Hata Kodu: </b>".$e->getMessage();
             exit;
        }
    }
    public function now($type="datetime")
    {
        switch($type)
        {
            case 'timestamp';
            return time();
            case 'date';
            return date('-m-d');
            case 'datetime';
            return date('Y-m-d H:i:s');
            default:
            return date('Y-m-d H:i:s');
            
        }
        
    }
    public function m_empty($data)
    {
        if($data=='' and $data!='0')
        {
            return true;
        }
        else
        {
            return false;
        }
        
    }
    public function query($query,$arr=false)
    {
        $this->reset();
        if($query!="")
        {
            
            try 
            {
                
                $run =  $this->connect->prepare($query);
                if($arr)
                {
                    if(is_array($arr))
                    {
                        $run->execute($arr);
                        return $run;
                    }
                    else
                    {
                        $run->execute(array($arr));
                        return $run;
                    }
                }
                else
                {
                    $run->execute();
                    return $run;
                }
            } catch (PDOException $e) {
                $this->error = $e->getMessage();
                return false;
            }
        }
        
        return false;
    }
    public function table($table)
    {
         $this->from = $this->db_prefix . $table;
        return $this;
    }
    public function select($fields)
    {
        $this->select = $fields;
        return $this;
    }
    public function where($field,$op='=',$values,$and="AND")
    {
        if(!$this->m_empty($field))
        {
            if(is_null($this->where))
            {
                $this->where="{$field} {$op} ?";
                $this->get_arr[] = $values;
            }
            else
            {
                
                $this->where.=" {$and} {$field} {$op} ?";
                $this->get_arr[] = $values;
            }
        }
        return $this;
    }
    public function where_set($field,$op='=',$values,$and="AND")
    {
        if(!$this->m_empty($field) and !$this->m_empty($values))
        {
            if(is_null($this->where))
            {
                $this->where="{$field} {$op} {$values}";
            }
            else
            {
                
                $this->where.=" {$and} {$field} {$op} {$values}";
            }
        }
        return $this;
    }
    public function group($fields)
    {
        if(is_null($this->group))
        {
            $this->group = $fields;
        }
        else
        {
            $this->group .=",{$fields}";
        
        }
        return $this;
    }
    public function order($field,$type="desc")
    {
        if(!is_null($field))
        {
            if(is_null($this->order))
            {
                if(strtolower($field)=="rand()")
                {
                    $this->order = $field;
                }
                else
                {
                    $this->order = $field.' '.strtoupper($type);
                }
            }
            else
            {
                if(strtolower($field)=="rand()")
                {
                    $this->order .=",{$fields}";
                }
                else
                {
                    $this->order .= ','.$field.' '.strtoupper($type);
                }
            }
        
        }
        return $this;
    }
    public function limit($start,$end=null)
    {
        if(is_null($end))
        {
            if(is_numeric($start))
            {
                $this->limit = $start;
            }
        }
        else
        {
            if(is_numeric($start) and is_numeric($end))
            {
                $this->limit = "{$start},{$end}";
            }
        
        }
        return $this;
    }
    public function pagination($per_page)
    {
        if(is_numeric($per_page))
        {
            $this->per_page = $per_page;
            $this->pagination_type = true;
        }
        return $this;
    }
    public function get()
    {
        $query = "SELECT {$this->select} FROM {$this->from}";
        if(!is_null($this->where))
        {
            $query.=" where {$this->where}";
        }
        if(!is_null($this->group))
        {
            $query.=" group by {$this->group}";
        }
        if(!is_null($this->order))
        {
            $query.=" order by {$this->order}";
        }
        if($this->pagination_type)
        {
            $c_per_page = $this->per_page;
            $c_arr = $this->get_arr;
            $extra_query = preg_replace("#SELECT (.*?) FROM#","SELECT COUNT(*) as total_count FROM",$query);
            $all = $this->query($extra_query,$this->get_arr);
            if($all)
            {
                    $total_count = $all->fetchAll(PDO::FETCH_ASSOC)[0]['total_count'];
                    $page_count = ceil($total_count/$c_per_page);
                    $current_page = (integer)m_u_g(DB_PAGINATION_GET) ? (integer)m_u_g(DB_PAGINATION_GET) : 1;
                    $current_limit=($current_page - 1) * $c_per_page;
                    $query = $query." limit {$current_limit},{$c_per_page}";
                    $current_rows = $this->query($query,$c_arr);
                    $data = $current_rows->fetchAll(PDO::FETCH_ASSOC);
                    $return = array();
                    $return['total_count'] = $total_count;
                    $return['current_count'] = count($data);
                    $return['total_page'] = $page_count;
                    $return['current_page'] = $current_page;
                    $return['data'] = $data;
                    return $return;
            }
            else
            {
                return false;
            }
        }
        else
        {
            if(!is_null($this->limit))
            {
                $query.=" limit {$this->limit}";
            }
            
            $gets = $this->query($query,$this->get_arr);
            if($gets)
            {
                    $data = $gets->fetchAll(PDO::FETCH_ASSOC);
                    $return = array();
                    $return["total_count"] = count($data);
                    $return["data"] = $data;
                    return $return;
            }
            else
            {
                return false;
            }
        }
    }
    public function get_var($field)
    {
        if($this->m_empty($field))
        {
            return false;
        }
        else
        {
            $query = "SELECT {$field} FROM {$this->from}";
            if(!is_null($this->where))
            {
                $query.=" where {$this->where}";
            }
            if(!is_null($this->order))
            {
                $query.=" order by id desc limit 1";
            }
            $gets = $this->query($query,$this->get_arr);
            if($gets)
            {
                    return  $gets->fetchAll(PDO::FETCH_ASSOC)[0][$field];
            }
            else
            {
                return false;
            }
        }
        
    }
    public function get_vars($table,$where,$var,$type=true)
    {
        if($type)
        {
                $data = $this->connect->query("select $var from $table where $where order by id desc limit 1")->fetch(PDO::FETCH_ASSOC);
        }
        else
        {
               $data = $this->connect->query("select $var from $table where $where")->fetch(PDO::FETCH_ASSOC);
        }
    
        return $data["$var"];
        
    }
    public function count()
    {
        $grouped = false;
        $query = "SELECT count(*) as total_count FROM {$this->from}";
        if(!is_null($this->where))
        {
            $query.=" where {$this->where}";
        }
        if(!is_null($this->group))
        {
            $grouped = true;
            $query.=" group by {$this->group}";
        }
        if(!is_null($this->order))
        {
            $query.=" order by {$this->order}";
        }
        $gets = $this->query($query,$this->get_arr);
        if($gets)
        {
                if($grouped)
                {
                    $count = 0;
                    foreach($gets->fetchAll(PDO::FETCH_ASSOC) as $counts)
                    {
                        $count = $count+$counts["total_count"];
                    }
                    return $count;
                }
                else
                {
                    return $gets->fetch(PDO::FETCH_ASSOC)["total_count"];
                }
        }
        else
        {
            return false;
        }
    }
    public function insert(array $data)
    {
        if(is_array($data))
        {
            $query = 'INSERT INTO '.$this->from;
            $keys = array_keys($data);
            $query.=' ('.implode(',',$keys).') values (';
            $query_add='';
            $values = array_values($data);
            foreach($values as $val)
            {
                $query_add.='?,';
            }
            $query_add = trim($query_add,',');
            $query.=$query_add.')';
            if($this->query($query,$values))
            {
                
                $this->last_id = $this->connect->lastInsertId();
                return $this->last_id;
            }
            else
            {
                
                return false;
            }
        }
        return false;

    }
    public function update(array $data)
    {
        if(is_array($data))
        {
            $query = "UPDATE {$this->from} set";
            $keys = array_keys($data);
            $values = array_values($data);
            $query_add = '';
            foreach($keys as $key)
            {
                $query_add.=" {$key} = ?,";
            }
            $query_add = trim($query_add,',');
            $query.=$query_add;
            
            if(!is_null($this->where))
            {
                $query.=" where {$this->where}";
            }
            $new = array_merge($values,$this->get_arr);
            if($this->query($query,$new))
            {
                
                return true;
            }
            else
            {
                
                return false;
            }
        }
        return false;

    }
    public function delete()
    {
        $query = "DELETE FROM {$this->from}";
        if(!is_null($this->where))
        {
            $query.=" where {$this->where}";
        }
        if($this->query($query,$this->get_arr))
        {
            
            return true;
        }
        else
        {
            
            return false;
        }

    }
    public function error()
    {
        return $this->error;
        
    }
    public function last_id()
    {
        return $this->last_id;
    }
    protected function reset()
    {
        $this->error = null;
        $this->last_id = null;
        $this->query = null;
        $this->from = null;
        $this->select = '*';
        $this->where = null;
        $this->group = null;
        $this->order = null;
        $this->limit = null;
        $this->pagination_type = false;
        $this->per_page = 0;
        $this->get_arr = null;
    }
}
$db = new DB();
?>```
Improve this question
20
  • 4
    I suspect your DB class can't deal with the fact that the function being called performs another database query in the middle of constructing your query. Commented Jul 23, 2021 at 20:37
  • 1
    Does it work if you do $variable = example_function('id') and then use ->where('id', '=', $variable)? Commented Jul 23, 2021 at 20:39
  • 2
    If it works that way, this failure is a design problem in your database class. Commented Jul 23, 2021 at 20:40
  • 1
    You can add the class to the question if it's not too big. But maybe you should use an existing ORM (e.g. Laravel) instead of trying to design your own. Commented Jul 23, 2021 at 20:53
  • 2
    Make the connection a static property and it can be reused without duplication and then you have a separate DBAL for creating query strings. Commented Jul 23, 2021 at 21:03

1 Answer 1

Reset to default
2

The issue is caused by changing the mutable object properties prior to completing the desired query with Db::get(). Resulting in the Db::$table property being changed when being called on the same instance of the Db class.

This is one of the reasons behind best-practices discouraging the use of global, since the intention/context of the current variable state is not easily determined.

What's happening with your code is

Db::$table = 'rooms'
Db::$table = 'admin'
Db::$where = 'admin.id =  1'
Db::get() //SELECT * FROM admin WHERE admin.id = 1
Db::$table = null
Db::$where = null
Db::$where = 'rooms.id = 1'
Db::get() //SELECT * FROM WHERE rooms.id = 1

To avoid the issue and ensure the desired order of operations, you need to call Db::get() prior to calling any other DB methods that modify the properties of the query issued when calling DB::get().

$roomId = example_function('id'); // (int) 1
//SELECT * FROM admin WHERE id = 1

$db->table('rooms')->where('id','=', $roomId)->get(); 
//SELECT * FROM rooms WHERE id = 1

Alternatively, to ensure that the intention of two individual queries is preserved, you would need to create a separate instance of DB for your second query. However, this would also create a separate PDO instance with your current code.

To avoid a second instance of PDO you would have to change $this->connect to a static property self::$connect and refactor any method using $this->connect to self::$connect. The static property will ensure that only a single instance of PDO will ever exist whenever new Db() is called.

class Db
{
    protected static $connect;

    //...

    public function __construct() 
    {      
        try {
            if (!isset(self::$connect)) {
                self::$connect = new PDO("{$this->db_database}:host={$this->db_host};dbname={$this->db_name};charset=utf8mb4", "{$this->db_username}", "{$this->db_password}");
                self::$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                self::$connect->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8mb4");
            }
        } catch ( PDOException $e ){
            echo  "<b>Veritabanı bağlantısı sağlanamadı! - Hata Kodu: </b>".$e->getMessage();
            exit;
        }
    }

   //...
}

Afterwards replace all usages of global $db; in your application with $db = new Db();.

This approach will also ensure your entire codebase is protected from the mutable object property overwrite issue and better adheres to best-practices.

function example_function($key)
{
    $db = new Db();
    $a_id = 1; // example
    $admins = $db->table('admins')->where('id','=',$a_id)->order('id','desc')->limit(1)->get();
    $admin_data = $admins['data'][0];
    $return = $admin_data[$key];
    return $return;
}

$new = $db->table('rooms')->where('id','=', example_function('id'))->get();

Another approach could be to use clone to workaround the mutable object issue. However, using clone will have adverse affects on the PDO instance, since it resides in the Db class. Most likely requiring you to separate the PDO connection code from the Query Builder code.

function example_function($key)
{
    global $db;
    $db2 = clone $db;
    $a_id = 1; // example
    $admins = $db2->table('admins')->where('id','=',$a_id)->order('id','desc')->limit(1)->get();
    //...
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.