PHP MySQL class
This class is used to establish MySQL connection and to extract data in easy to use formats as arrays or objects. The main goal is to make the database operations quick and easy.
<?php
class samis_mysqli {
private $db = false;
private $db_server = false;
private $db_user = false;
private $db_pass = false;
private $db_base = false;
private $db_encoding = 'utf-8';
/**
* Set the connection data.
* NOTE: connection will be established only after the first query request.
* @param string $db_server - The MySQL host
* @param string $db_user - The database user
* @param string $db_pass - The database password
* @param string $db_base - The database
*/
public function __construct($db_server, $db_user, $db_pass, $db_base = false)
{
$this->db_server = $db_server;
$this->db_user = $db_user;
$this->db_pass = $db_pass;
$this->db_base = $db_base;
}
/**
* Establish the connection with the database.
*/
private function sql_connect() {
$this->db = mysqli_connect($this->db_server, $this->db_user, $this->db_pass, $this->db_base) or exit("Could not connect to MySql!");
mysqli_set_charset($this->db,$this->db_encoding);
if($this->db_base && !mysqli_select_db($this->db, $this->db_base)) {
exit("Could not select database {$this->db_base}");
}
}
/**
* Execute MySQL queries.
* @param string $query - The query to execute.
* @return The MySQL result
*/
public function sql_query($query) {
if(!$this->db) {
$this->sql_connect();
}
$result = mysqli_query($this->db, $query);
if(!$result) {
$this->error_handler();
}
return $result;
}
/**
* Returns the first value of the query.
* Usecase: SELECT COUNT(*) FROM users;
* @param string $query - The query to execute.
* @return string $v - The first value of the dataset.
*/
public function sql_value($query) {
$result = $this->sql_query($query);
$v = (mysqli_num_rows($result)) ? mysqli_fetch_row($result) : 0;
$v = $v[0];
mysqli_free_result($result);
return $v;
}
/**
* Get the result of the query as array with keys from the contents of the first column and values as the contents of the second column.
* Usecase: SELECT id, username FROM users;
* @param string $query - The query to execute.
* @return array $v - Array with the two columns as keys and values.
*/
public function sql_keys_values($query) {
$result = $this->sql_array($query);
$keys_values = array();
foreach($result as $v) {
$v = (array)$v;
$v = array_values($v);
$keys_values[$v[0]] = $v[1];
}
return $keys_values;
}
/**
* Returns array of objects that represent the rows. The keys of the array represent the values of the first column.
* Usecase: SELECT id, username, email FROM users;
* @param string $query - The query to execute.
* @param string $key_name - The column that contains the key values.
* @return array $v - Array with objects with the data of the rows.
*/
public function sql_key_array($query, $key_name) {
$v = array();
$result = $this->sql_query($query);
if(mysqli_num_rows($result)) {
while($row = mysqli_fetch_object($result)) {
$v[$row->{$key_name}] = $row;
}
}
mysqli_free_result($result);
return $v;
}
/**
* Returns array of values.
* Usecase: SELECT username FROM users;
* @param string $query - The query to execute.
* @return array $v - Array with the selected column.
*/
public function sql_values($query) {
$v = array();
$result = $this->sql_query($query);
if(mysqli_num_rows($result)) {
while($row = mysqli_fetch_row($result)) {
$v[] = $row[0];
}
}
mysqli_free_result($result);
return $v;
}
/**
* Returns an object with the values of a single row.
* Usecase: SELECT * FROM users WHERE user_id = 1;
* @param string $query - The query to execute.
* @return array $v - Object with the selected row.
*/
public function sql_object($query) {
$result = $this->sql_query($query);
$v = (mysqli_num_rows($result)) ? mysqli_fetch_object($result) : false;
mysqli_free_result($result);
return $v;
}
/**
* Returns array with objects that represent each row.
* Usecase: SELECT * FROM users;
* @param string $query - The query to execute.
* @return array $v - Array with objects that represent each row.
*/
public function sql_array($query) {
$v = array();
$result = $this->sql_query($query);
if(mysqli_num_rows($result)) {
while($row = mysqli_fetch_object($result)) {
$v[] = $row;
}
}
mysqli_free_result($result);
return $v;
}
/**
* Escape the strings that will affect the query.
* @param string $str - String to be escaped.
* @return string $str - The escaped string.
*/
public function sql_escape($str) {
if(!$this->db) {
$this->sql_connect();
}
$str = mysqli_real_escape_string($this->db, $str);
return $str;
}
/**
* Escapes all values in multidimentional array.
* @param array $array - Array to be escaped.
* @return array $array - The array with escaped values.
*/
public function sql_escape_array($array) {
if(!$this->db) {
$this->sql_connect();
}
foreach($array as $k => $v) {
if(is_array($v)) {
$array[$k] = $this->sql_escape_array($v);
} else {
$array[$k] = $this->sql_escape($v);
}
}
return $array;
}
/**
* Close the connection to the database
*/
public function close() {
mysqli_close($this->db);
}
/**
* Get the last inserted ID
*/
public function get_insert_id()
{
return mysqli_insert_id($this->db);
}
/**
* This function may be used to send notifications, log the errors and so on.
*/
private function error_handler() {
$error = mysqli_errno($this->db) . ": " . mysqli_error($this->db);
exit($error);
}
}
Download...