寻找帮助以使mysqli动态执行select语句 [英] Looking for help to make a select statement dynamic with mysqli

问题描述

使用mysqli(PHP)进行动态选择语句
你好. Iam在一个可以使用
的简单Crud系统上工作 对于我的考试，我的问题是:

Makeing a dynamic select statement with mysqli (PHP)
Hello. Iam working on a simple crud system that i can use
for my exam, my problem is:

我想做一个动态选择语句，它可以
选择我要的任何表，与表列相同
在我的数据库中-Iam对
来说还很陌生 php和面向对象
进行编程，以便任何人都有简单的方法来完成它.

I want to make a dynamic select statement that can
select whatever table i ask for, same with table columns
in my database - Iam fairly new to
php and Object Oriented
programming so if anyone have a simple way to do it.

这是我到目前为止的精选课程:

   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();

这是我的数据库课程

    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();

因此，到目前为止，iam正在使用mysqli连接到我的数据库
然后从数据库类扩展我的选择类，以便获得连接
然后我要从

So what iam 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

推荐答案

首先，您的select类扩展了database类，因此在select类中重新声明public $result = array();没有意义，甚至没有必要.

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.

第二，由于您不在类之外使用对象属性，因此请将它们设置为private.

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

最后，由于您要处理可变数量的参数，因此请使用func_get_args()函数.

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

这里是参考:

• func_get_args()

根据您的要求，解决方案是将可变数量的参数发送到selectQuery()方法，并使用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.

• 第一个参数是表名，其余参数是列名(如果提供)
• 如果仅将一个参数传递给函数，则SELECT查询将为SELECT * FROM table_name
• 如果将多个参数传递给该函数，则SELECT查询将为SELECT column1, column2, column3, ... FROM table_name

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

这篇关于寻找帮助以使mysqli动态执行select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！