Skip to content

Instantly share code, notes, and snippets.

@mloberg
Created August 30, 2011 18:00
Show Gist options
  • Save mloberg/1181537 to your computer and use it in GitHub Desktop.
Save mloberg/1181537 to your computer and use it in GitHub Desktop.
Simple PHP MySQL Class
<?php
class Mysql{
static private $link = null;
static private $info = array(
'last_query' => null,
'num_rows' => null,
'insert_id' => null
);
static private $connection_info = array();
static private $where;
static private $limit;
static private $order;
function __construct($host, $user, $pass, $db){
self::$connection_info = array('host' => $host, 'user' => $user, 'pass' => $pass, 'db' => $db);
}
function __destruct(){
if(is_resource(self::$link)) mysql_close(self::$link);
}
/**
* Setter method
*/
static private function set($field, $value){
self::$info[$field] = $value;
}
/**
* Getter methods
*/
public function last_query(){
return self::$info['last_query'];
}
public function num_rows(){
return self::$info['num_rows'];
}
public function insert_id(){
return self::$info['insert_id'];
}
/**
* Create or return a connection to the MySQL server.
*/
static private function connection(){
if(!is_resource(self::$link) || empty(self::$link)){
if(($link = mysql_connect(self::$connection_info['host'], self::$connection_info['user'], self::$connection_info['pass'])) && mysql_select_db(self::$connection_info['db'], $link)){
self::$link = $link;
mysql_set_charset('utf8');
}else{
throw new Exception('Could not connect to MySQL database.');
}
}
return self::$link;
}
/**
* MySQL Where methods
*/
static private function __where($info, $type = 'AND'){
$link =& self::connection();
$where = self::$where;
foreach($info as $row => $value){
if(empty($where)){
$where = sprintf("WHERE `%s`='%s'", $row, mysql_real_escape_string($value));
}else{
$where .= sprintf(" %s `%s`='%s'", $type, $row, mysql_real_escape_string($value));
}
}
self::$where = $where;
}
public function where($field, $equal = null){
if(is_array($field)){
self::__where($field);
}else{
self::__where(array($field => $equal));
}
return $this;
}
public function and_where($field, $equal = null){
return $this->where($field, $equal);
}
public function or_where($field, $equal = null){
if(is_array($field)){
self::__where($field, 'OR');
}else{
self::__where(array($field => $equal), 'OR');
}
return $this;
}
/**
* MySQL limit method
*/
public function limit($limit){
self::$limit = 'LIMIT '.$limit;
return $this;
}
/**
* MySQL Order By method
*/
public function order_by($by, $order_type = 'DESC'){
$order = self::$order;
if(is_array($by)){
foreach($by as $field => $type){
if(is_int($field) && !preg_match('/(DESC|desc|ASC|asc)/', $type)){
$field = $type;
$type = $order_type;
}
if(empty($order)){
$order = sprintf("ORDER BY `%s` %s", $field, $type);
}else{
$order .= sprintf(", `%s` %s", $field, $type);
}
}
}else{
if(empty($order)){
$order = sprintf("ORDER BY `%s` %s", $by, $order_type);
}else{
$order .= sprintf(", `%s` %s", $by, $order_type);
}
}
self::$order = $order;
return $this;
}
/**
* MySQL query helper
*/
static private function extra(){
$extra = '';
if(!empty(self::$where)) $extra .= ' '.self::$where;
if(!empty(self::$order)) $extra .= ' '.self::$order;
if(!empty(self::$limit)) $extra .= ' '.self::$limit;
// cleanup
self::$where = null;
self::$order = null;
self::$limit = null;
return $extra;
}
/**
* MySQL Query methods
*/
public function query($qry, $return = false){
$link =& self::connection();
self::set('last_query', $qry);
$result = mysql_query($qry);
if(is_resource($result)){
self::set('num_rows', mysql_num_rows($result));
}
if($return){
if(preg_match('/LIMIT 1/', $qry)){
$data = mysql_fetch_assoc($result);
mysql_free_result($result);
return $data;
}else{
$data = array();
while($row = mysql_fetch_assoc($result)){
$data[] = $row;
}
mysql_free_result($result);
return $data;
}
}
return true;
}
public function get($table, $select = '*'){
$link =& self::connection();
if(is_array($select)){
$cols = '';
foreach($select as $col){
$cols .= "`{$col}`,";
}
$select = substr($cols, 0, -1);
}
$sql = sprintf("SELECT %s FROM %s%s", $select, $table, self::extra());
self::set('last_query', $sql);
if(!($result = mysql_query($sql))){
throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_error());
$data = false;
}elseif(is_resource($result)){
$num_rows = mysql_num_rows($result);
self::set('num_rows', $num_rows);
if($num_rows === 0){
$data = false;
}elseif(preg_match('/LIMIT 1/', $sql)){
$data = mysql_fetch_assoc($result);
}else{
$data = array();
while($row = mysql_fetch_assoc($result)){
$data[] = $row;
}
}
}else{
$data = false;
}
mysql_free_result($result);
return $data;
}
public function insert($table, $data){
$link =& self::connection();
$fields = '';
$values = '';
foreach($data as $col => $value){
$fields .= sprintf("`%s`,", $col);
$values .= sprintf("'%s',", mysql_real_escape_string($value));
}
$fields = substr($fields, 0, -1);
$values = substr($values, 0, -1);
$sql = sprintf("INSERT INTO %s (%s) VALUES (%s)", $table, $fields, $values);
self::set('last_query', $sql);
if(!mysql_query($sql)){
throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_error());
}else{
self::set('insert_id', mysql_insert_id());
return true;
}
}
public function update($table, $info){
if(empty(self::$where)){
throw new Exception("Where is not set. Can't update whole table.");
}else{
$link =& self::connection();
$update = '';
foreach($info as $col => $value){
$update .= sprintf("`%s`='%s', ", $col, mysql_real_escape_string($value));
}
$update = substr($update, 0, -2);
$sql = sprintf("UPDATE %s SET %s%s", $table, $update, self::extra());
self::set('last_query', $sql);
if(!mysql_query($sql)){
throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_error());
}else{
return true;
}
}
}
public function delete($table){
if(empty(self::$where)){
throw new Exception("Where is not set. Can't delete whole table.");
}else{
$link =& self::connection();
$sql = sprintf("DELETE FROM %s%s", $table, self::extra());
self::set('last_query', $sql);
if(!mysql_query($sql)){
throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_error());
}else{
return true;
}
}
}
}
<?php
include_once('mysql.php');
$mysql = new MySQL('host', 'user', 'password', 'database');
// get all posts
try{
$posts = $mysql->get('posts');
print_r($posts);
echo $mysql->num_rows(); // number of rows returned
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
// get all post titles and authors
try{
$posts = $mysql->get('posts', array('title', 'author');
// or
$posts = $mysql->get('posts', 'title,author');
print_r($posts);
echo $mysql->last_query(); // the raw query that was ran
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
// get one post
try{
$post = $mysql->limit(1)->get('posts');
print_r($post);
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
// get post with an id of 1
try{
$post = $mysql->where('id', 1)->get('posts');
// or
$post = $mysql->where(array('id', 1))->get('posts');
print_r($post);
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
// get all posts by the author of "John Doe"
try{
$posts = $mysql->where(array('author' => 'John Doe'))->get('posts');
print_r($posts);
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
// insert post
try{
$mysql->insert('posts', array('title' => 'New Title', 'content' => 'post content', 'author' => 'Matthew Loberg'));
echo $mysql->insert_id(); // id of newly inserted post
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
// update post 1
try{
$mysql->where('id', 1)->update('posts', array('title' => 'New Title'));
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
// delete post 1
try{
$mysql->where('id', 1)->delete('posts');
}catch(Exception $e){
echo 'Caught exception: ', $e->getMessage();
}
Copy link

ghost commented Jun 28, 2018

You dont need ?> on a pure php file

@rvercesi
Copy link

Can I Limit and Offset?

@shak18
Copy link

shak18 commented May 24, 2019

this code was very usefull and practicall for small projects like reports and stuff fo first of thank you @mloberg, now for those interested in a PHP7 compatible version (@vanowm) i have updated this code here, improvements:

  • Working with mysqli (since mysql library is deprecated and removed from PHP7)
  • Joins implemented
  • Fixed multiple instances (the original code only support one connection at the time since it was using static variables)

@pirozok123
Copy link

Missing ?> at end of each file

it normal. php practice.

@rvwilliams
Copy link

A very useful class that I have updated to mysqli (see my fork). One minor correction is to the patterns for preg_match with LIMIT. These don't work if a LIMIT of 10, say, has been set. My version is '/LIMIT 1[^\d]/' and this seems to work OK.

elseif (preg_match ( '/LIMIT 1[^\d]/', $sql )) {

@pirozok123
Copy link

[^\d] here you says that you wanna using everything, but not digit [0-9] - only 1 digit. [0-9]+ 1 or more digits [0-9]* no digits or few digit
[0-9] {2} - 2 digit. [0-9] {, 2}. From n to 2.
See documentation of pregmatch

@rvwilliams
Copy link

The preg_match is being used to decide how many results to return. The choice is between 1 (LIMIT 1) or more than 1. So 1\d limits the choice to a LIMIT of 1 (and not 10, 11, 12 etc). The original pattern failed on a LIMIT of 10 and returned just 1 match.

@pirozok123
Copy link

It clear. Just i wrote about pregmatch ))

@developermcsd
Copy link

Where can I get step by step tutorial regarding this php script ?

@rvwilliams
Copy link

Where can I get step by step tutorial regarding this php script ?

There are very many howto's on how to use MySQL or MariaDB with PHP. Or you could try the PHP page https://www.php.net/manual/en/book.mysqli.php

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment