These classes are based on a much improved set of classes and functions that were originally built on CodeAcademy and that we have found extremely useful. They can serve as a basis for not only UserSpice or for your project as a whole.

Usage

To instantiate (use) the database class and access the database, you need to call it once on the page.

$db = DB::getInstance();

To insert into the database, you make an array of your fields then run the insert query, like this…
$fields=array('user_id'=>$user_id, 'profile_pic'=>'dan.jpg'); //column_name=>entry
$db->insert('table_name',$fields);
To delete from the database it is tablename, selector, and value so something like…
$db->delete('users',array('id','=',4));

Updating is similar. Table name, id and the same fields array

PLEASE NOTE: This method assumes that the first column in your table is called id.  It can’t be userID or forumID or it won’t work and will drive you crazy.
$db->update('camps',5,$fields);

Find All
$userQ = $db->findAll('camps');
$camps = $campQ->results(); //will give you a multidimensional array
Or you can just find the first
$first_camp = $campQ->first();

Find By Id
Relational SQL databases live and die by ids.  It’s really common to need to search a database table by id.  We make that easy.  Let’s say you want to find a user based on the id in your variable.
$id = 1;
$player = $db->findById($id,'users');

Get (A Quick Select * Feature)

If you want all results from the users table where the deactivated field is set to 0)…
$users = $db->get('users',['deactivated','=',0]);

If you only want a SINGLE result (the first one), that’s where the false comes in…
$user = $db->get('users',['id','=',11],false);

Frustrated with the classes? Just run a basic or bound query, like this…
$director_id = 1;
$query = $db->query("SELECT * FROM camps WHERE director_id = ?", array($director_id));
$x = $query->results();

Don’t want to deal with objects? No problem.  Just pass the word true into your results query.

$x = $query->results(true);
BAM, no more OOP results!

Binding. You should do it.  That’s all those question marks in the queries.  It’s super simple.

$thing1 = “thisthing”;
$thing2 = “thatthing”;
$query = $db->query(“SELECT * FROM users WHERE name = ? AND job = ?”,[$thing1,$thing2]);

DB.php

If you copy and paste this code, please get rid of the space before the opening php tag.


<?php
class DB {
 private static $_instance = null;
 private $_pdo, $_query, $_error = false, $_results, $_count = 0, $_lastId;

 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(PDOException $e){
 die($e->getMessage());
 }
 }

 public static function getInstance(){
 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();
 $this->_lastId = $this->_pdo->lastInsertId();
 } else{
 $this->_error = true;
 }
 }
 return $this;
 }

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

 public function findById($id,$table){
 return $this->action('SELECT *',$table,array('id','=',$id));
 }

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

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

 if(in_array($operator, $operators)){
 $sql .= " WHERE {$field} {$operator} ?";
 }
 }
 if (!$this->query($sql, array($value))->error()) {
 return $this;
 }
 return false;
 }

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

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

 public function deleteById($table,$id){
 return $this->action('DELETE',$table,array('id','=',$id));
 }

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

 foreach ($fields as $field) {
 $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){
 $set = '';
 $x = 1;

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

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

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

 public function results(){
 return $this->_results;
 }

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

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

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

 public function lastId(){
 return $this->_lastId;
 }
}