Some PHP copy/pasta for working with databases

databases.php

<?php
/**
 * Purpose:
 *  This class provides and easy to use API for accessing databases with a single
 *  method call. Create a DB connection, then run queries against that single
 *  connection easily.
 *
 * Attributes:
 *  - $host: string
 *  - $user: string
 *  - $password: string
 *  - $dbName: string
 *  - $link: object | false
 *
 * Methods
 *  + __construct( $host : string, $user : string, $password : string, $dbName : string ) : void
 *  + __destruct() : void
 *
 *  + setDbName( $dbName : string ) : variant_mod
 *  + setHost( $host : string ) : void
 *  + setPassword( $password : string ) : void
 *  + setUser( $user : string ) : void
 *
 *  + getDbName() : string
 *  + getHost() : string
 *  + getPassword() : string
 *  + getUser() : string
 *  + getLink() : object | false
 *
 *  + openConnection() : void
 *  + closeConnection() : void
 *
 *  + exec( $query : string ) : array
 *  + exec_count( $query : string ) : int | string
 *  + exec_arr( $query : string, $callback : callback ) : void
 *  + exec_obj( $query : string, $callback : callback ) : void
 *  + exec_assoc( $query : string, $callback : callback ) : void
 *
 *  + *arr( &$rs : array, $callback : callback ) : void
 *  + *obj( &$rs : array, $callback : callback ) : void
 *  + *assoc( &$rs : array, $callback : callback ) : void
 */
class DbAccess {
    private $host;
    private $dbName;
    private $user;
    private $password;
    private $link;

    /**
     * @param $host
     * @param $dbName
     * @param $user
     * @param $password
     *
     * Purpose:
     *  Construct the Object. Set all of the object's properties.
     */
    public function __construct($_host, $_dbName, $_user, $_password, $_link = null) {
        $this->host     = $_host;
        $this->dbName   = $_dbName;
        $this->user     = $_user;
        $this->password = $_password;
        $this->link     = $_link;
    }

    /**
     * Purpose:
     *  The destruct function is called when the object is no longer
     *  needed, either cleaned up by the garbage collector, or anything
     *  else.
     */
    public function __destruct() {
        if ($this->$link != null) {
            $this->closeConnection();
        }
    }

    ##################################### Accessors and mutators ###################################

    //set
    /**
     * In each of my mutator methods, I'm closing the DB connection since once
     * the access information has changed, you're essentially on a new
     * connection. Whenever you name a request, the connection will
     * automatically be re-opened.
     *
     * For each of the following:
     * @param string $host | $user | $password | $dbName
     * @return void
     */
    public function setDbName($dbName) {
        $this->dbName = $dbName;
        mysqli_select_db($link, $dbName);
    }

    public function setHost($host) {
        $this->host = $host;
        $this->closeConnection();
    }

    public function setPassword($password) {
        $this->password = $password;
        $this->closeConnection();
    }

    public function setUser($user) {
        $this->user = $user;
        $this->closeConnection();
    }

    //get
    public function getDbName() {
        return $this->dbName;
    }

    public function getHost() {
        return $this->host;
    }

    public function getPassword() {
        return $this->password;
    }

    public function getUser() {
        return $this->user;
    }

    public function getLink() {
        return $this->link;
    }

    ######################################## Methods ###############################################

    /**
     * Purpose:
     *  This method will open a database connection, select the database to be
     *  working with, and ensure that the db is using the encoding format we
     *  want it to be using. It will set the static link variable so that
     *  the connection is only opened once it is actually needed.
     *
     * @return void
     */
    private function openConnection() {
        if (!isset($this->link)) {
            $this->link = mysqli_connect($this->host, $this->user, $this->password) or die("dead");
            // or die("Could not connect: " . mysqli_error());

            if (!$this->link) {
                die('Invalid select db: ' . mysqli_error($this->link));
            }

            mysqli_select_db($this->link, $this->dbName);
            $this->exec("SET NAMES 'utf8'");
            $this->exec("SET CHARACTER SET utf8 ");
        }
    }

    /**
     * Purpose:
     *  Provide an alias method to the mysqli_close method so that it can be
     *  used more cleanly in conjunction with the openConnection() method.
     *
     * @return void
     */
    private function closeConnection() {
        if ($this->link != null) {
            mysqli_close($this->link);
        }
    }

    /**
     * @param $query
     *
     * Purpose:
     *  This method will check if the link with the database is open. If the
     *  connection with the database is not open, it will open the connection,
     *  and then execute the SQL query passed in.
     *
     *  It's important that this method is static to make it as simple as
     *  possible to use this method. All you need to do to run a SQL query
     *  is:
     *      DbAccess::exec($query);
     *
     * @return array
     */
    public function exec($query) {
        $this->openConnection();
        $rs = mysqli_query($this->link, $query) or die(mysqli_error($this->link));
        return $rs;
    }

    /**
     * @param string $query - a SQL query as a string.
     *
     * Purpose:
     *  Provide another wrapper method for this class. Executes the SQL
     *  query and then returns the number of rows affected in the result set.
     *
     * @return int - row <= PHP_INT_MAX
     * @return string - row > PHP_INT_MAX
     */
    public function exec_count($query) {
        $rs = $this->exec($query);
        return mysqli_num_rows($rs);
    }

    ########################### sqli_query -> sqli_fetch_method wrappers ###########################
    /**
     * @param string $query - the SQL query to be executed
     * @param callback - the callback function to be run when looping.
     *
     * Purpose:
     *  The following 3 methods provide wrappers for both the exec method and the static arr, obj
     *  and assoc methods. It will call the exec function, get the result set, and then pass it and
     *  the callback to the static method relating to the function.
     *
     *  NOTE: exec_obj should be the method used. It has the best peformance of the three.
     *
     * @return void
     */
    public function exec_arr($query, $callback) {
        $rs = $this->exec($query);
        self::arr($rs, $callback);
    }

    public function exec_assoc($query, $callback) {
        $rs = $this->exec($query);
        self::assoc($rs, $callback);
    }

    /**
     * NOTE: This is the most efficient of the three methods.
     *      sqli_fetch_object has the best performance
     */
    public function exec_obj($query, $callback) {
        $rs = $this->exec($query);
        self::obj($rs, $callback);
    }

    ##################################### sqli_fetch_method wrappers ###############################
    /**
     * @param &$rs
     * @param $callback
     *
     * Purpose:
     *  The purpose of the following 3 method is to provide wrappers for the default sqli_fetch
     *  methods. Each of these methods will use a different method of fetching the data and
     *  providing a method of looping through the data by providing the function with a
     *  callback.
     *
     *  The best way to use these methods is through the DbAccess::obj($rs, $callback)
     *  method. sqli_fetch_object is the best performing of the 3 different methods.
     *  Source: http://www.spearheadsoftwares.com/tutorials/php-performance-benchmarking/50-mysql-fetch-assoc-vs-mysql-fetch-array-vs-mysql-fetch-object
     *
     *  Now, one thing to consider with this is that since you will not be looping in a standard
     *  loop inside your own code, you will need to explicitely tell the callback function to use
     *  the variables from outside the functions scope. For example:
     *
     *  $counter = 0;
     *  $rs = $dbs["sort"]->exec($query); //Result has 10 rows
     *  DbAccess::obj($rs, function() use (&$counter) {
     *      counter++;
     *  });
     *
     *  print counter;
     *  // 10
     *
     * @return void
     */

    public static function arr(&$rs, $callback) {
        while ($row = mysqli_fetch_array($rs)) {
            $callback($row);
        }
    }

    public static function assoc(&$rs, $callback) {
        while ($row = mysqli_fetch_assoc($rs)) {
            $callback($row);
        }
    }

    public static function obj(&$rs, $callback) {
        while ($row = mysqli_fetch_object($rs)) {
            $callback($row);
        }
    }

    ################################################################################################
    /**
     * The following SQL operations do not operate on tables themselves, but rather on the database.
     * They allow you to access data such as the names of all the schemas in a database, the names 
     * of all of the tables in a schema, etc.
     */

    /**
     * Purpose:
     *  Return all of the schemas on the current database server. 
     * 
     * @return array
     */
    public function getAllSchemas() {
        $schemas = [];
        $this->exec_obj("SHOW SCHEMAS", function($row) use (&$schemas) {
            $schemas[] = $row->Database;
        });

        return $schemas;
    }

    /**
     * Purpose: 
     *  Return all of the tables in the current database schema.
     * 
     * @return array 
     */
    public function getAllTables() {
        $tables = [];
        $q_getAllTable = 
            "SELECT table_name 
            FROM information_schema.tables 
            where table_schema='$this->dbName'";

        $this->exec_obj($q_getAllTable, function($row) use (&$tables) {
            $tables[] = $row->table_name;
        });

        return $tables;
    }
}

?>