Database class, OOP - connect to mysql

Question

This is database class:

DB.php

<?php
    class DB {
        public static $instance = null;

        private     $_pdo = null,
                    $_query = null,
                    $_error = false,
                    $_results = null,
                    $_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'));
            } catch(PDOExeption $e) {
                die($e->getMessage());
            }
        }

        public static function getInstance() {
            // Already an instance of this? Return, if not, create.
            if(!isset(self::$instance)) {
                self::$instance = new DB();
            }
            return self::$instance;
        }

        public function query($sql, $params = array()) {

            $this->_error = false;

            if($this->_query = $this->_pdo->prepare($sql)) {
                $x = 1;
                if(count($params)) {
                    foreach($params as $param) {
                        $this->_query->bindValue($x, $param);
                        $x++;
                    }
                }

                if($this->_query->execute()) {
                    $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
                    $this->_count = $this->_query->rowCount();
                } else {
                    $this->_error = true;
                }
            }

            return $this;
        }

        public function get($table, $where) {
            return $this->action('SELECT *', $table, $where);
        }

        public function delete($table, $where) {
            return $this->action('DELETE', $table, $where);
        }

        public function action($action, $table, $where = array()) {
            if(count($where) === 3) {
                $operators = array('=', '>', '<', '>=', '<=');

                $field      = $where[0];
                $operator   = $where[1];
                $value      = $where[2];

                if(in_array($operator, $operators)) {
                    $sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?";

                    if(!$this->query($sql, array($value))->error()) {
                        return $this;
                    }

                }

                return false;
            }
        }

        public function insert($table, $fields = array()) {
            $keys   = array_keys($fields);
            $values = null;
            $x      = 1;

            foreach($fields as $value) {
                $values .= "?";
                if($x < count($fields)) {
                    $values .= ', ';
                }
                $x++;
            }

            $sql = "INSERT INTO {$table} (`" . implode('`, `', $keys) . "`) VALUES ({$values})";

            if(!$this->query($sql, $fields)->error()) {
                return true;
            }

            return false;
        }

        public function update($table, $id, $fields = array()) {
            $set    = null;
            $x      = 1;

            foreach($fields as $name => $value) {
                $set .= "{$name} = ?";
                if($x < count($fields)) {
                    $set .= ', ';
                }
                $x++;
            }

            $sql = "UPDATE users SET {$set} WHERE id = {$id}";

            if(!$this->query($sql, $fields)->error()) {
                return true;
            }

            return false;
        }

        public function results() {
            // Return result object
            return $this->_results;
        }

        public function first() {
            return $this->_results[0];
        }

        public function count() {
            // Return count
            return $this->_count;
        }

        public function error() {
            return $this->_error;
        }
    }

I was looking this database approach and it seems very practical and useful. I'm beginner at oop and still learning. The requestQuote would look something like this:

How do I bindParam in query like this?

requestQuote = DB::getInstance()->query(""); (form DB.class)

This is code I have right now:

$request = "";
    if ($_POST) {
        $request = $_POST["request"];
    } else if (isset($_GET["request"])) {
        $request = $_GET["request"];
    }

$requestQuote="%" . $request . "%";
            $sql = $conn -> prepare("SELECT * FROM users WHERE concat(name, ' ',lastname, ' ', user_id) LIKE :request limit " . (($page * 50)-50) . ",50");
            $sql->bindParam(":request", $requestQuote);
            $sql -> execute();
            $results = $sql -> fetchAll(PDO::FETCH_OBJ);

When I put it like this, then pagination works. But I need search form... and that won't work...

$sql= DB::getInstance()->query(
    "SELECT * FROM users
     WHERE (category='admin')
     LIMIT " . (($page* 5)-5) . ",5");

The absolute first thing I see in PHP when people are new to OOP is creating a PDO object within the constructor. Inject it into the constructor as an argument, then then assign it as `$this->pdo`. You then keep logic specific to your project/database out of that codebase, and in a config file, where it belongs. — Amelia, Jul 01 '15 at 12:36

score 1 · Answer 1 · answered Jul 01 '15 at 15:06

@Paul was close but you got one more issue:

Check this part of the class:

$x = 1;
if(count($params)) {
   foreach($params as $param) {
       $this->_query->bindValue($x, $param);
       $x++;
    }
}

It is not binding with named place holder, you need to change the code:

$limit = ($page * 50)-50;
$params = array('%lolcats%', $limit);
$query = 
   "SELECT * FROM users 
    WHERE concat(name, ' ',lastname, ' ', user_id) 
    LIKE ? 
    LIMIT ?,50";
$results = DB::getInstance()->query($query, $params);

or change the class code to bind by placeholder, something along the following lines:

#$params = array(':request' =>'%lolcats%', ':limit'=>$limit);
if(count($params)) {
   foreach($params as $key=>$value) {
       $this->_query->bindValue($key, $value);
    }
}

I don't think you can use limit as a bound parameter because it will be escaped IIRC, but good spot on the parameter not being bound correctly! — Paul Bain, Jul 02 '15 at 08:53

score 0 · Answer 2 · answered Jul 01 '15 at 12:33

0

Looking at this class, the second argument of query function is an optional array of parameters so use this to pass the parameters for your request:

$params = array(':request' => 'lolcats');
$limit = $page - 1 * 50;
$query = sprintf(
   "SELECT * FROM users 
    WHERE concat(name, ' ',lastname, ' ', user_id) 
    LIKE :request 
    LIMIT %d,50",
    $limt
);
$results = DB::getInstance()->query($query, $params);

answered Jul 01 '15 at 12:33

Paul Bain

4,364
1
16
30

I get this error: Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in DB.php line 52 – Tov Jul 01 '15 at 13:07

Database class, OOP - connect to mysql

2 Answers2