php - 如何为不同的选择操作编写辅助类？

Question

伙计们，我想要根据我的需要进行不同的“选择”操作，但我只需要一个辅助函数来完成所有选择操作，我如何获得它？

这是我的辅助类Database.php

<?php
class Database
{
    public $server = "localhost";
    public $user = "root";
    public $password = "";
    public $database_name = "employee_application";
    public $table_name  = "";
    public $database_connection = "";
    public $class_name = "Database";
    public $function_name = "";
    //constructor
    public function __construct(){
    //establishes connection to database
        $this->database_connection = new mysqli($this->server, $this->user, $this->password, $this->database_name);
        if($this->database_connection->connect_error)
            die ("Connection failed".$this->database_connection->connect_error);
    }
    //destructor
    public function __destruct(){
    //closes database connection
        if(mysqli_close($this->database_connection))
        {
            $this->database_connection = null;
        }
        else
            echo "couldn't close";
    }

    public function run_query($sql_query)
    {
        $this->function_name = "run_query";
        try{
            if($this->database_connection){
                $output = $this->database_connection->query($sql_query);
                if($output){
                    $result["status"] = 1;
                    $result["array"] = $output;
                }
                else{
                    $result["status"] = 0;
                    $result["message"] = "Syntax error in query";
                }
            }
            else{
                throw new Exception ("Database connection error");
            }
        }
        catch (Exception $error){
            $result["status"] = 0;
            $result["message"] = $error->getMessage();
            $this->error_table($this->class_name, $this->function_name, "connection error", date('Y-m-d H:i:s'));
        }
        return $result;
    }
    public function get_table($start_from, $per_page){
        $this->function_name = "get_table";
        $sql_query = "select * from $this->table_name LIMIT $start_from, $per_page";
        return $this->run_query($sql_query);
    }
}
?>  

在上面的代码中，get_table函数执行基本的选择操作......现在我希望 get_table 函数执行以下所有操作，

$sql_query = "select e.id, e.employee_name, e.salary, dept.department_name, desi.designation_name, e.department, e.designation
                                       from employees e
                                       LEFT OUTER JOIN designations desi ON e.designation = desi.id
                                       LEFT OUTER JOIN departments dept ON e.department = dept.id
                                       ORDER BY e.employee_name
                                       LIMIT $start_from, $per_page";


$sql_query = "select id, designation_name from designations ORDER BY designation_name";
$sql_query = "select * from departments";
$sql_query = "select * from departments Limit 15,10";

那么如何解决这个问题，有人可以帮助我吗？

Answer 1

如果您稍微扩展一下您的类(class)，您就可以实现您想要的目标。像这样的事情:

<?php
    class   Database
        {
            public      $sql;
            public      $bind;
            public      $statement;
            public      $table_name;

            protected   $orderby;
            protected   $set_limit;
            protected   $function_name;
            protected   $sql_where;
            protected   $i;

            protected   function reset_class()
                {
                    // reset variables
                    $this->sql              =   array();
                    $this->function_name    =   false;
                    $this->table_name       =   false;
                    $this->statement        =   false;
                    $this->sql_where        =   false;
                    $this->bind             =   array();
                    $this->orderby          =   false;
                    $this->set_limit        =   false;
                }

            protected   function run_query($statement)
                {
                    echo (isset($statement) && !empty($statement))? $statement:"";
                }

            public function get_table($start_from = false, $per_page = false)
                {
                    // Compile the sql into a statement
                    $this->execute();
                    // Set the function if not already set
                    $this->function_name    =   (!isset($this->function_name) || isset($this->function_name) && $this->function_name == false)? "get_table":$this->function_name;
                    // Set the statement
                    $this->statement        =   (!isset($this->statement) || isset($this->statement) && $this->statement == false)? "select * from ".$this->table_name:$this->statement;

                    // Add on limiting
                    if($start_from != false && $per_page != false) {
                            if(is_numeric($start_from) && is_numeric($per_page))
                                $this->statement    .=  " LIMIT $start_from, $per_page";
                        }

                    // Run your query
                    $this->run_query($this->statement);
                    // Reset the variables
                    $this->reset_class();

                    return $this;
                }

            public  function select($value = false)
                {
                    $this->sql[]    =   "select";

                    $this->function_name = "get_table";

                    if($value != false) {
                            $this->sql[]    =   (!is_array($value))? $value:implode(",",$value);
                        }
                    else
                        $this->sql[]    =   "*";

                    return $this;
                }

            public  function from($value = false)
                {
                    $this->sql[]    =   "from";
                    $this->sql[]    =   "$value";

                    return $this;
                }

            public  function where($values = array(), $not = false, $group = false,$operand = 'and')
                {

                    if(empty($values))
                        return $this;

                    $this->sql_where    =   array();

                    if(isset($this->sql) && !in_array('where',$this->sql))
                        $this->sql[]        =   'where';

                    $equals             =   ($not == false || $not == 0)? "=":"!=";

                    if(is_array($values) && !empty($values)) {
                            if(!isset($this->i))
                                $this->i = 0;


                            foreach($values as $key => $value) {

                                    $key    =   trim($key,":");


                                    if(isset($this->bind[":".$key])) {
                                            $auto   =   str_replace(".","_",$key).$this->i;
                                        //  $preop  =   $operand." ";
                                        }
                                    else {
                                        //  $preop  =   "";
                                            $auto   =   str_replace(".","_",$key);
                                        }

                                    $this->bind[":".$auto]  =   $value;
                                    $this->sql_where[]      =   $key." $equals ".":".$auto;
                                    $this->i++;
                                }

                            if($group == false || $group == 0)
                                $this->sql[]    =   implode(" $operand ",$this->sql_where);
                            else
                                $this->sql[]    =   "(".implode(" $operand ",$this->sql_where).")";
                        }
                    else {
                            $this->sql[]        =   $values;
                        }

                    if(is_array($this->bind))
                        asort($this->bind);

                    return $this;
                }

            public  function limit($value = false,$offset = false)
                {
                    $this->set_limit        =   "";

                    if(is_numeric($value)) {

                            $this->set_limit        =   $value;

                            if(is_numeric($offset)) 
                                $this->set_limit    =   $offset.", ".$this->set_limit;
                        }

                    return $this;
                }

            public  function order_by($column = array())
                {
                    if(is_array($column) && !empty($column)) {
                            foreach($column as $colname => $orderby) {
                                    $array[]    =   $colname." ".str_replace(array("'",'"',"+",";"),"",$orderby);
                                }
                        }
                    else
                        $array[]    =   $column;

                    $this->orderby  =   implode(", ",$array);

                    return $this;
                }

            public  function execute()
                {
                    $limit              =   (isset($this->set_limit) && !empty($this->set_limit))? " LIMIT ".$this->set_limit:"";
                    $order              =   (isset($this->orderby) && !empty($this->orderby))? " ORDER BY ".$this->orderby:"";

                    $this->statement    =   (is_array($this->sql))? implode(" ", $this->sql).$order.$limit:"";

                    return $this;
                }

            public  function use_table($table_name = 'employees')
                {
                    $this->table_name   =   $table_name;
                    return $this;
                }
        }

使用:

    // Create instance  
    $query  =   new Database();

    // Big query
    $query  ->select(array("e.id", "e.employee_name", "e.salary", "dept.department_name", "desi.designation_name", "e.department", "e.designation"))
            ->from("employees e LEFT OUTER JOIN designations desi ON e.designation = desi.id LEFT OUTER JOIN departments dept ON e.department = dept.id")
            ->order_by(array("e.employee_name"=>""))
            ->limit(1,5)->get_table();

    // More advanced select
    $query  ->select(array("id", "designation_name"))
            ->from("designations")
            ->order_by(array("designation_name"=>""))
            ->get_table();

    // Simple select
    $query  ->use_table("departments")
            ->get_table();

    // Simple select with limits
    $query  ->use_table("departments")
            ->get_table(10,15);

?>

给你:

// Big query
select e.id,e.employee_name,e.salary,dept.department_name,desi.designation_name,e.department,e.designation from employees e LEFT OUTER JOIN designations desi ON e.designation = desi.id LEFT OUTER JOIN departments dept ON e.department = dept.id ORDER BY e.employee_name LIMIT 5, 1

// More advanced select
select id,designation_name from designations ORDER BY designation_name 

// Simple select
select * from departments

// Simple select with limits
select * from departments LIMIT 10, 15