(Select query) How to make it more dynamic?

Question

I have the following method select in my CRUD class and use it throughout the application, but I need to make it more dynamic, for example I need to use an ORDER BY, also use other fields other than $user_id, some hint?

//Select on database
public function select($where = null, $user_id, $fields = '*')
{
    if($where != null){
        $where = " WHERE ";
    }

    $query  = "SELECT {$fields} FROM `{$this->table}` {$where} `user_id`=:user_id ORDER BY id DESC";
    $stmt   = $this->db->prepare($query);
    $stmt->bindValue(":user_id", $user_id);
    $stmt->execute();
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}

Answer 1

I do this way using PDO:

/** @var PDO */
private static $connect = null;

/**
 * Conecta com o banco de dados com o pattern singleton
 * Retorna um objeto PDO!
 */
private static function Conectar(){

    try {

        Setup::checkLocal();

        if(self::$connect == null) {
            $dsn = 'mysql:host='.Setup::$host.';dbname='.Setup::$base;
            $options = [ PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8" ];
            self::$connect = new PDO($dsn, Setup::$user, Setup::$pass, $options);
        }

    } catch (PDOException $e) {
        echo 'PDOException: erro ao conectar ao banco de dados.';
        //PHPErro($e->getCode(), $e->getMessage(), $e->getFile(), $e->getLine());
        die;
    }

    self::$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    //self::$connect->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING);

    return self::$connect;
}

/** Retorna um objeto PDO Singleton Pattern */
public static function getConn(){
    return self::Conectar();
}

public static function prepareQuery($query, $params){

    $preparedQuery = self::getConn()->prepare($query);
    foreach ($params as $key => $value) {
        $preparedQuery->bindValue($key, $value);
    }

    $preparedQuery->execute();
    $result = $preparedQuery->fetchAll(PDO::FETCH_OBJ);

    self::$connect = null;

    return $result;
}

And I usually access it this way:

$qrSelect = "SELECT campo1, campo2 FROM tabela WHERE campo1 = :campo1 ORDER BY campo1 ASC";

$params = [":campo1" => $valor];
$retorno = Conexao::prepareQuery($qrSelect, $params);

I always pass the whole query. What always changes are the parameters. I don’t see much profit in saving words select and from when calling the method select, if to compensate you will have to pass various parameters and do other validations.