Looking for help to make a select statement dynamic

Question

I want to make a dynamic select statement that can select whatever table I ask for, same with table columns in my database.

Here is my select class so far:

<?php

   class select extends database{
    // Instance variables
    private $id;
    public $table;
    public $column;
    public $sql;
    public $result = array();

    // Methods - Behavior
    public function selectQuery($table){
        
        global $con;
        
        $sql = $this->sql;
        
        $sql = "SELECT * FROM {$table}";
        
        $result = $this->con->query($sql);
        
        while($row = $result->fetch_object()){
            //iterate all columns from the selected table 
            //and return it someway.
        }
    }
}
$selectQuery = new select();

Here is my database class

require_once(LIB_PATH.DS."config.php");
class database
{
    public $con;
    public $result = array();

    public function __construct()
    {
        $this->con = new mysqli(DB_HOST,DB_USERNAME,DB_PASSWORD,DB);
        if($this->con->connect_error){
            die($this->con->connect_error);
        }
    }
}
$db = new database();

What I'm doing so far is connecting to my Database with mysqli then I extend my select class from my database class so I can get the connection and then I want to select all from.

Answer 1

First of all, your select class is extending the database class, so there's no point re-declaring public $result = array(); in select class, it's not even necessary actually.

Second, since you're not using object properties outside of the class, make them private.

And finally, since you're dealing with variable number of arguments, make use of func_get_args() function.

Here's the reference:

• func_get_args()

Well based on your requirement, the solution would be to send variable number of arguments to selectQuery() method and use func_get_args() to get an array comprising a function's argument list.

• The first argument is the table name and rest of the arguments are column names(if provided)
• If only one argument is passed to the function then the SELECT query would be SELECT * FROM table_name
• And if more than one argument is passed to the function then the SELECT query would be SELECT column1, column2, column3, ... FROM table_name

So your code should be like this:

require_once(LIB_PATH.DS."config.php");

class database
{
    public $con;

    public function __construct()
    {
        $this->con = new mysqli(DB_HOST,DB_USERNAME,DB_PASSWORD,DB);
        if($this->con->connect_error){
            die($this->con->connect_error);
        }
    }
}

class select extends database{
    // Instance variables
    private $table;
    private $columns;
    private $sql;

    // Methods - Behavior
    public function selectQuery(){
        
        // incrementally construct the query
        $this->sql = "SELECT ";
        
        // get the argments passed to the function
        $this->columns = func_get_args();
        
        // the first argument would be the table name and rest of the arguments are column names (if provided)
        $this->table = $this->columns[0];
        
        // if only one argument is passed to the function,
        // then SELECT query would be SELECT * FROM table_name
        if(count($this->columns) == 1){
            $this->sql .= "* ";
        }else{
            
            // if more than one argument is passed to the function,
            // then the SELECT query would be SELECT column1, column2, column3, ... FROM table_name
            for($i = 1; $i < count($this->columns); ++$i){
                $this->sql .= $this->columns[$i] . ",";
            }
            
            // remove the last , from the $sql string
            $this->sql = rtrim($this->sql, ",");
        }
        
        $this->sql .= " FROM $this->table";
        
        // execute the query
        $result = $this->con->query($this->sql);
        
        // return the result set
        return $result;
    }
}

$obj = new select();

$table = "YOUR_TABLE_NAME";
$column1 = "COLUMN_1";
$column2 = "COLUMN_2";

$result = $obj->selectQuery($table, $column1, $column2);
while($row = $result->fetch_assoc()){
    // display it
    echo $row[$column1] . " " . $row[$column2] . "<br />";
}

$result = $obj->selectQuery($table);
while($row = $result->fetch_assoc()){
    // display it
}