Flexible and fast PHP MySQL Class

I will share with you one php class that i use very often for making queries in MYSQL via php.

It is really fast, easy to understand and can be edited very easily.

Special thanks to the original author of the class, Ciprian Mocanu.



The class:

<?php

class mysql {
 var $con;
 function __construct($db=array()) {
  $default = array(
   'host' => 'localhost',
   'user' => 'root',
   'pass' => '',
   'db' => 'test'
  );
  $db = array_merge($default,$db);
  $this->con=mysql_connect($db['host'],$db['user'],$db['pass'],true) or die ('Error connecting to MySQL');
  mysql_select_db($db['db'],$this->con) or die('Database '.$db['db'].' does not exist!');
 }
 function __destruct() {
  mysql_close($this->con);
 }
 function query($s='',$rows=false,$organize=true) {
  if (!$q=mysql_query($s,$this->con)) return false;
  if ($rows!==false) $rows = intval($rows);
  $rez=array(); $count=0;
  $type = $organize ? MYSQL_NUM : MYSQL_ASSOC;
  while (($rows===false || $count<$rows) && $line=mysql_fetch_array($q,$type)) {
   if ($organize) {
    foreach ($line as $field_id => $value) {
     $table = mysql_field_table($q, $field_id);
     if ($table==='') $table=0;
     $field = mysql_field_name($q,$field_id);
     $rez[$count][$table][$field]=$value;
    }
   } else {
    $rez[$count] = $line;
   }
   ++$count;
  }
  if (!mysql_free_result($q)) return false;
  return $rez;
 }
 function execute($s='') {
  if (mysql_query($s,$this->con)) return true;
  return false;
 }
 function select($options) {
  $default = array (
   'table' => '',
   'fields' => '*',
   'condition' => '1',
   'order' => '1',
   'limit' => 50
  );
  $options = array_merge($default,$options);
  $sql = "SELECT {$options['fields']} FROM {$options['table']} WHERE {$options['condition']} ORDER BY {$options['order']} LIMIT {$options['limit']}";
  return $this->query($sql);
 }
 function row($options) {
  $default = array (
   'table' => '',
   'fields' => '*',
   'condition' => '1',
   'order' => '1'
  );
  $options = array_merge($default,$options);
  $sql = "SELECT {$options['fields']} FROM {$options['table']} WHERE {$options['condition']} ORDER BY {$options['order']}";
  $result = $this->query($sql,1,false);
  if (empty($result[0])) return false;
  return $result[0];
 }
 function get($table=null,$field=null,$conditions='1') {
  if ($table===null || $field===null) return false;
  $result=$this->row(array(
   'table' => $table,
   'condition' => $conditions,
   'fields' => $field
  ));
  if (empty($result[$field])) return false;
  return $result[$field];
 }
 function update($table=null,$array_of_values=array(),$conditions='FALSE') {
  if ($table===null || empty($array_of_values)) return false;
  $what_to_set = array();
  foreach ($array_of_values as $field => $value) {
   if (is_array($value) && !empty($value[0])) $what_to_set[]="`$field`='{$value[0]}'";
   else $what_to_set []= "`$field`='".mysql_real_escape_string($value,$this->con)."'";
  }
  $what_to_set_string = implode(',',$what_to_set);
  return $this->execute("UPDATE $table SET $what_to_set_string WHERE $conditions");
 }
 function insert($table=null,$array_of_values=array()) {
  if ($table===null || empty($array_of_values) || !is_array($array_of_values)) return false;
  $fields=array(); $values=array();
  foreach ($array_of_values as $id => $value) {
   $fields[]=$id;
   if (is_array($value) && !empty($value[0])) $values[]=$value[0];
   else $values[]="'".mysql_real_escape_string($value,$this->con)."'";
  }
  $s = "INSERT INTO $table (".implode(',',$fields).') VALUES ('.implode(',',$values).')';
  if (mysql_query($s,$this->con)) return mysql_insert_id($this->con);
  return false;
 }
 function delete($table=null,$conditions='FALSE') {
  if ($table===null) return false;
  return $this->execute("DELETE FROM $table WHERE $conditions");
 }
}

Example:

$connection_information = array(
 'host' => 'localhost',
 'user' => 'root',
 'pass' => '',
 'db' => 'test'
);
$m = new mysql($connection_information);
 
//simple and complex query (I recommend you use the select method of the class rather than this)
$result = $m->query('SELECT * FROM `users`');
var_dump($result);
//this will output an array like this: $array[$count][$table][$field]
 
//simple execute command (I recommend using the delete and the insert and the update methods of the class)
$result = $m->execute('DELETE FROM `pages` WHERE `id`=5');
var_dump($result); //returns true if ok and false if not
 
//method to select (clean and beautiful)
$result = $m->select(array(
 'table' => 'users',
 'condition' => 'active=1 AND type=1'
));
var_dump($result);
// Will output something like this:
/*
array (3) {
 [0] => array (1) {
  ['users'] => array (4) {
   ['id'] => int (5),
   ['name'] => string (7) "ciprian",
   ['active'] => int (1),
   ['type'] => int (1),
   ['email'] => string (14) "ciprian@mbe.ro"
  }
 },
 [1] => ...
}
*/
 
//you can also get only one row (and a simple array like this $array[$table_field])
$result = $m->row(array(
 'table' => 'users',
 'condition' => 'active=1 AND type=1'
));
var_dump($m); //returns only the first row in an array arranged like this $array[$table_field]
 
//or you can get only a field (for example you need the name of the user with id = 5)
$name=$m->get('users','name','id=5');
var_dump($name);
// Will output something like this:
/*
string (7) "ciprian"
*/
 
//you can also insert into the table
$data = array(
 'name' => 'ion',
 'active' => 0,
 'email' => 'ion@mbe.ro'
);
$result = $m->insert('users',$data);
var_dump($result); //returns true if ok and false if not
 
//update the table (let's assume we have a pages table and we need to set the views of the page with + 1
$data = array(
 'views' => array( 'views+1' )
);
$result = $m->update('pages',$data,'id='.$current_page_id);
var_dump($result); //returns true if ok and false if not
//PS: Notice I put the value of the views in another array. You can do that in the update as well as in the insert. If you put it like that, no mysql_real_escape_string will be called for that value when updating / inserting.
 
//deletion is also possible
$result = $m->delete('pages','id=5'); //deletes page with id 5
var_dump($result); //returns true if ok and false if not

@author     Ciprian Mocanu <http://www.mbe.ro> <ciprian@mbe.ro>