(Select query) how to leave more dynamic?

Question

(Select query) how to leave more dynamic?

Navigation

#1 by (1 votes)

1

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

//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);
}

php

asked by anonymous 19.04.2017 / 20:34

1 answer

Login with Facebook - Insert profile image into an input type="file" Can anyone explain to me what is a generic loop in Lua?

score 1 · Accepted Answer

I do this 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 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 entire query. What always changes are the parameters. I do not see much gain in saving the words select and from when calling the method select , if to compensate you will have to pass several parameters and make other validations.