带有prepared where子句的PDO数据库类选择函数

我花了一些时间查看代码，得出了这个

<?php
class Database {
    private static $handler;
    private $db;
    
    protected function __construct() {
        try {
            $this->db = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname='  . Config::get('mysql/database'), Config::get('mysql/username'), Config::get('mysql/password'));
        } catch(PDOException $e) {
            die($e->getMessage());
        }
    }
    
    public static function getInstance() {
        if(!isset(self::$handler)) { //if there is no instance jet we want to make an instance one would also use this to excute things methods inside this this class
            self::$handler = new Database();
        }
        return self::$handler; //return the instance
    }
    
    public function insert($table, $columnsArrayAndValues) {
        $sql = "INSERT INTO `{$table}` ("; // Make the first part of the Insert query with the table and (. example "INTERT INTO post (";
        
        $countColums = count($columnsArrayAndValues);//make a variable to hold the amount of colums which the user provides with the array.
        /*
        if the array is 
        
        array(
                        ':message' => $message, 
                        ':user_ID' => $user_ID, 
                        ':board_ID' => $board_ID
        )
        this would return 3 because it has 3 elements
        */
        
        
        $i = 0; //initialize an iterator
        foreach($columnsArrayAndValues  as $colum => $value) { // cut the colums array into parts and loop threw them
            $i++; // add one to the iterator
            if($i < $countColums){ //if the Iterator is smallen then the amount of colums
                $sql .= "`{$colum}`, "; // add the colum name to the sql string and add a ,    
                // i do this because when you have multipe colums you need an , to differentiate the colums
            } else {
                $sql .= "`{$colum}`)";// the last ellement should not have an ,!!!!!!!!!!!!!!!!!!! it should also have an ) at the end of the colum declartion
            }
        }
        $sql .= " VALUES ("; // add the values clause...
        
        $i = 0; // reset the iterator
        foreach($columnsArrayAndValues  as $colum => $value) { // cut the colums array into parts and loop threw them
            $i++; // add one to the iterator
            if($i < $countColums){ //if the Iterator is smallen then the amount of colums
                $sql .= "?, "; // add the prepared name to the sql string and add a ,  
                // i do this because when you have multipe prepared names you need an , to differentiate the colums
            } else {
                $sql .= "?)"; /// the last element should not have an ,......
            }
        }
        
        $ar = array(); //initialize an array
        
        foreach($columnsArrayAndValues  as $colum => $value) { // cut the colums array into parts and loop threw them
            $ar[]= $this->replaceQuotesAndBackslashes($value); // make the link...
        }
        if($this->action($sql, $ar)) {
            return $this->db->lastInsertId();
        } else {
            return NULL;
        }
    }
    
    public function select($fieldAndTableArray, $where = NULL) {
        $sql = "SELECT ";
        $fieldAndTableArray;
        $countTabels = count($fieldAndTableArray);//make a variable to hold the amount of colums which the user provides with the array.
        
        /*
        if the array is 
        
        array(
                "tabel" => array("colum1a", "colum2a", "colum3a", "colum4a"),
                "tabel2" => array("colum1b", "colum2b", "colum3b", "colum4b")
            )
        this would return 2 because it has 2 elements
        */
        foreach($fieldAndTableArray as $table => $fields){ // cut the colums array into parts and loop threw them
            $countFields = count($fields);//make a variable to hold the amount of colums which the user provides with the array.
            $i =0;
            foreach($fields as $field) {
                $i++;
                $sql .= "`{$table}`.`{$field}`";
                if($i <= $countFields) {
                    $sql .= ", ";
                }
            }
        }
        
        $sql = substr($sql, 0, -2);
        $sql .= " FROM ";
        $i = 0;
        foreach($fieldAndTableArray as $table => $fields){ // cut the colums array into parts and loop threw them
            $i++;
            $sql .= "`{$table}`";
            if($i < $countTabels) {
                $sql .=", ";
            }
        }
        if($where != NULL) {
            $sql .= " WHERE ";
            foreach($where as $wherePart) {
                
                if(is_array($wherePart)) {
                    $elementCounter = 0;
                    foreach($wherePart as $element){
                        $elementCounter++;
                        if($elementCounter >= 3){
                            $sql .= "?";
                        } else {
                            $sql .= $element . " ";
                        }
                    }
                } else {
                    $sql .= " " . $wherePart . " ";
                }
            }
            $ar = array(); //initialize an array
    
            foreach($where as $wherePart) {
                $elementCounter++;
                if(is_array($wherePart)){
                    $elementCounter = 0;
                    foreach($wherePart as $value) {
                        $elementCounter++;
                        if($elementCounter == 3){
                            $ar[] = $this->replaceQuotesAndBackslashes($value);
                        }
                    }
                }
            }
        }
//      $this->query($query, $ar);
        return $this->query($sql, $ar);
    }
    
    public function query($sql, $params) {
        $query = $this->db->prepare($sql); //prepare the statement
        $query->execute($params);// execute the query
        return $query;
    }
    public function action($sql, $params) {
        $query = $this->db->prepare($sql); //prepare the statement
        return $query->execute($params);// execute the query
    }
    
    private function replaceQuotesAndBackslashes($fieldValue){
//      $fieldValue = mysqli_real_escape_string($conn, $fieldValue);
        $fieldValue = str_replace("''", "''''", $fieldValue);
        $fieldValue = str_replace("'", '''''', $fieldValue);
        
        return $fieldValue;
    }
}
/* EXAMPLES AND TESTING

Database::getInstance()->insert(
        "tabelname", array(
    "column1" => $value1,
    "column2" => $value1
));
$result = Database::getInstance()->select(
        array(
                "post" => array("post_ID", "post_name", "image_ID", "message"),
                "image" => array("image_path")
            ),
        array(
            array("`post`.`image_ID`", "=", "1"), "AND",
            array("`post`.`post_ID`", "!=", "34567")
        )
);

while ($row = $result->fetch()) {
    echo"<pre>", print_r($row), "<pre />";
}

/*$sth = $db->prepare('SELECT message, post_name
    FROM post
    WHERE post_name < ?');
$sth->execute(array('red'));
$red = $sth->fetchAll();*/
/*
119470ef20ab9907b293fb36e5200b7c
Kevin Neven 2016 5-6 23:34*/
`