Skip to content

Instantly share code, notes, and snippets.

@nixin72
Created April 10, 2020 20:11
Show Gist options
  • Save nixin72/a5f8e893434c006c6f38a4a32f67fc36 to your computer and use it in GitHub Desktop.
Save nixin72/a5f8e893434c006c6f38a4a32f67fc36 to your computer and use it in GitHub Desktop.
Some PHP copy/pasta for working with databases
<?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;
}
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment