need to refine the OOP PDO prepared statement

[Supervan]

Hi,

 

Im using OOP and PDO prepared statement.

 

Will it be possible to construct a class and shorten the following 3 lines of code, the array portion of the code.
A lot of repetition.

$parms = array();
$parms[] = array(':id',$idnr,PDO::PARAM_INT);
$parms[] = array(':name',$name1,PDO::PARAM_STR);

 Code Sample

$idnr = 123;
$name1 = "tom";

$parms = array();
$parms[] = array(':id',$idnr,PDO::PARAM_INT);
$parms[] = array(':name',$name1,PDO::PARAM_STR);

// then use the $parms array as the second parameter in your query calling statement -
 $users = DB::getInstance()->query("SELECT * FROM users WHERE id = :id AND name = :name",$parms);

[maxxd]

This is untested, but should work:

$parms = array(
	array(
		':id',
		$idnr,
		PDO::PARAM_INT
	),
	array(
		':name',
		$name1,
		PDO::PARAM_STR
	)
);

Of course, whether it's actually shorter or not is up for debate....

[IThinkMyBrainHurts]

The original is simplest in my view, however a wrapper class for this specific query could look like this (untested):

 

class my_xyz_query{
    private $id = null;
    private $name = null;
    private $parms = null;
    
    public function__construct($name="",$id=""){
        $this->parms=array();
        $this->parms[] = array(':id',$id,PDO::PARAM_INT);
        $this->parms[] = array(':name',$name,PDO::PARAM_STR);
    }
    
    public function set_name($name){    $this->name=$name;    }
    public function set_id($id){    $this->id=$id;    }
    
    public function get_parms(){
        $this->parms[0][1]=$this->id;
        $this->parms[1][1]=$this->name;
        return $this->params;
    }
}

[Supervan]

Thanks for your time and feedback. Trying to create generic classes for CRUD operations...

[Supervan]

 

The original is simplest in my view, however a wrapper class for this specific query could look like this (untested):

class my_xyz_query{
    private $id = null;
    private $name = null;
    private $parms = null;
    
    public function__construct($name="",$id=""){
        $this->parms=array();
        $this->parms[] = array(':id',$id,PDO::PARAM_INT);
        $this->parms[] = array(':name',$name,PDO::PARAM_STR);
    }
    
    public function set_name($name){    $this->name=$name;    }
    public function set_id($id){    $this->id=$id;    }
    
    public function get_parms(){
        $this->parms[0][1]=$this->id;
        $this->parms[1][1]=$this->name;
        return $this->params;
    }
}

thx

[Supervan]

This is untested, but should work:

$parms = array(
	array(
		':id',
		$idnr,
		PDO::PARAM_INT
	),
	array(
		':name',
		$name1,
		PDO::PARAM_STR
	)
);

Of course, whether it's actually shorter or not is up for debate....

thx

[Supervan]

I not fond of repetition in code, any way of using abbreviation shortening the array code.

 

':id',$idnr,INT

':name',$name1,STR

$parms = array();
$parms[] = array(':id',$idnr,PDO::PARAM_INT);
$parms[] = array(':name',$name1,PDO::PARAM_STR);

Im trying to create a generic class that I can use with all my queries CRUD.

 

Can someone please assist.

$idnr = 123;
$name1 = "tom";

$parms = array();
$parms[] = array(':id',$idnr,PDO::PARAM_INT);
$parms[] = array(':name',$name1,PDO::PARAM_STR);

$users = DB::getInstance()->query("SELECT * FROM users WHERE id = :id AND name = :name",$parms);

class DB {
    private static $_instance = null;
    private $_pdo,
            $_query,
            $_error = false,
            $_results,
            $_count = 0;
    private function __construct() {
        try {
            $this->_pdo = new PDO('mysql:host=' . Config::get('mysql/host') . ';dbname=' . Config::get('mysql/db'), Config::get('mysql/username'), Config::get('mysql/password'));
            // echo "connected";
        } catch (PDOException $e) {
            die($e->getMessage());
        }
    }
    public static function getInstance() {
        if (!isset(self::$_instance)) {
            self::$_instance = new DB();
        }
        return self::$_instance;
    }
   
     public function query($sql, $data_in = array()) {
        $this->_error = false;
        if ($data_in) {// prepared query
            $this->_query = $this->_pdo->prepare($sql); // this example extends the pdo class
            foreach ($data_in as $arr) {
                if (isset($arr[2])) {// type supplied
                    $this->_query->bindValue($arr[0], $arr[1], $arr[2]);
                } else {// no type supplied
                    $this->_query->bindValue($arr[0], $arr[1]); // defaults to string type
                }
            }
            if ($this->_query->execute()) {
                $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
                $this->_count = $this->_query->rowCount();
            } else {
                $this->_error = true;
            }
        } else {// non-prepared query
            $this->_query = $this->_pdo->prepare($sql);
            if ($this->_query->execute()) {
                $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
                $this->_count = $this->_query->rowCount();
            } else {
                $this->_error = true;
            }
        }// code to retrieve the result from the query....
        return $this;
    }
    public function results() {
        return $this->_results;
    }
    public function first() {
        return $this->results()[0];
    }
    public function error() {
        return $this->_error;
    }
    public function count() {
        return $this->_count;
    }
}

Edited April 24, 2015 by Supervan

[Muddy_Funster]

PDO is generic, and I'm not seeing this repetition you're talking about.  you are attaching different variables to different place holders with each line, that's not repetition, that's just the job.  PDO is already an abstraction, why do so many people think it's a good idea to layer another abstraction on top of it?