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 coolumn 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
}

Answer 2

This is simple

function conx(){

$link = new mysqli($db_host, $db_user, $db_pass, $db_name);

if ($link->connect_error) {
    die("Connection failed: " . $link->connect_error);
} 
 $sql = "SET NAMES utf8"; 
 $result = $link->query($sql); 
return $link;
}

now you have a connexion lets pass a $table value

$link = conx();
$sql = "SELECT * FROM $table";  <------ REMOVE {} from your table var!Worked for me
$result = $link->query($sql);
if(!$result) {echo 'Failed to query';};
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo $row["title"];
    }
} 
else {
    echo "0 results";
}
$link->close();
}

This is as basic as it gets!

Answer 3

Actually - I don't think you're far off; ignoring namespacing or any specific design patterns for the moment, there are some scope foibles but...

Let's assume a directory structure where you've got your classes and an application file (index.php for instance) to glue it all together:

/class/Database.php

<?php

class Database {
    protected $con;

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

        //error trapping
        if($this->con->connect_error) {
            die($this->con->connect_error);
        }

        //ok set charset; should add error trapping here too
        else {
            $this->con->set_charset('UTF8'); //probably
        }
    }
}

/class/Select.php

<?php

class Select extends Database {

    // Class Members
    private $id;
    public $table;
    public $column;
    public $sql;
    public $result = array();

    // Methods - Behavior
    public function __construct() {
        parent::__construct();
    }

    public function selectQuery($table) {

        //since there's a $table class member... 
        $this->table = $this->con->real_escape_string($table);

        //query string
        $sql = "SELECT * FROM {$this->table}";

        //should be some error trapping here
        $response = $this->con->query($sql);

        //result
        while($row = $response->fetch_object()) {
            $this->result[] = $row;
        }

        //return
        return $this->result;
    }
}

index.php

<?php

//require is a language construct not a function
// - it doesn't need parentheses
require_once LIB_PATH.DS . 'config.php';
require_once '/path/to/class/Database.php';
require_once '/path/to/class/Select.php';

//build the object and run the query
$s = new Select;

// this should hold your resultset as an array of objects
// though it would also be accessible via $s->result since
// that's a public class member
$r = $s->selectQuery('my_table'); 

This is all very bare-bones though and not terribly practical (but you said it was for an exam, so...).

In reality you'd probably not want to crank up a new database connection for every query so it might be worth looking at static class members: http://php.net/manual/en/language.oop5.static.php

... or the Singleton pattern (though you may or may not want a Singleton database connection) : http://www.phptherightway.com/pages/Design-Patterns.html

... also encapsulation, public class members are not (generally) desirable: What is encapsulation with simple example in php?

Answer 4

db_connection.php

class db{
    private $db_host = '';
    private $db_user = 'user';
    private $db_pass = 'pass';
    private $db_name = 'your database name';
    protected $con;

    public function __construct(){
        $this->con = new mysqli($this->db_host,$this->db_user,$this->db_pass,$this->db_name);
        if ($this->con -> connect_errno) {
          echo "Failed to connect to MySQL: " . $this->con -> connect_error;
          exit();
        }
        return false;
    }

}    

query.php

require 'db_connection.php';
class query extends db{
    public function select($tableName,$column = null,$clause = null){
        $columns = null;
        if ($column == null) {
            $columns = '*';
        }else{
            $values = Array();
            foreach($column as $key => $value){
              array_push($values,"$value");
            }
            $columns = join(',',$values);
        }
        $select = null;
        $select .= "SELECT ".$columns." FROM {$tableName} ";
        if ($clause != null) {
            $select .= $clause;
        }
        $s_sql =  $this->con->prepare($select);
        $s_sql->execute();
        // It will return mysqli_stmt Object
        return $s_sql;
    }   

}

$s will return mysqli_stmt Object.

index.php

$query_ob = new query();
// The first parameter is required And other parameters is optional. 
// The second parameter must be Array[].
$s = $query_ob->select(parameter1,parameter1,parameter3);     
$r = $s->get_result();
while ($f = $r->fetch_assoc()) {
    // code here
}
$t->close();