Simple MySQLi Class

This is a simple MySQL Abstraction Layer for PHP>5.3 that provides a simple and secure interaction with your database using mysqli_* functions at its core.

This is perfect for small scale applications such as cron jobs, facebook canvas campaigns or micro frameworks or sites.

This project is under construction, any feedback would be appreciated

Author: Jonathan Tavares Author: Lars Moelleken

##Get "Simple MySQLi" You can download it from here, or require it using composer.

{
    "require": {
		"voku/simple-mysqli": "dev-master"
	}
}

##Install via "composer require"

composer require voku/simple-mysqli

##Starting the driver

    use voku\db\DB;

    require_once 'composer/autoload.php';

    $db = DB::getInstance('yourDbHost', 'yourDbUser', 'yourDbPassword', 'yourDbName');
    
    // example
    // $db = DB::getInstance('localhost', 'root', '', 'test');

##Using the "DB"-Class

there are numerous ways of using this library, here are some examples of the most common methods

###Selecting and retrieving data from a table

  use voku\db\DB;

  $db = DB::getInstance();

  $result = $db->query("SELECT * FROM users");
  $users  = $result->fetchALL();

But you can also use a method for select-queries:

  $db->select( String $table, Array $where );               // generate an SELECT query

Example: SELECT

    $where = array(
        'page_type ='        => 'article',
        'page_type NOT LIKE' => '%öäü123',
        'page_id >='          => 2,
    );
    $resultSelect = $this->db->select('page', $where);

Here is a list of connectors for the "WHERE"-Array: 'NOT', 'IS', 'IS NOT', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', 'LIKE', 'NOT LIKE', '>', '<', '>=', '<=', '<>'

INFO: use a array as $value for "[NOT] IN" and "[NOT] BETWEEN"

Example: SELECT with "NOT IN"

    $where = array(
        'page_type NOT IN'     => array(
            'foo',
            'bar'
        ),
        'page_id >'            => 2,
    );
    $resultSelect = $this->db->select('page', $where);

###Inserting data on a table

to manipulate tables you have the most important methods wrapped, they all work the same way: parsing arrays of key/value pairs and forming a safe query

the methods are:

  $db->insert( String $table, Array $data );                // generate an INSERT query
  $db->replace( String $table, Array $data );               // generate an REPLACE query
  $db->update( String $table, Array $data, Array $where );  // generate an UPDATE query
  $db->delete( String $table, Array $where );               // generate a DELETE query

All methods will return the resulting mysqli_insert_id() or true/false depending on context. The correct approach if to always check if they executed as success is always returned

Example: DELETE

  $deleteArray = array('user_id' => 9);
  $ok = $db->delete('users', $deleteArray);
  if ($ok) {
    echo "user deleted!";
  } else {
    echo "can't delete user!";
  }

note: all parameter values are sanitized before execution, you don't have to escape values beforehand.

Example: INSERT

  $insertArray = array(
    'name'   => "John",
    'email'  => "[email protected]",
    'group'  => 1,
    'active' => true,
  );
  $newUserId = $db->insert('users', $insertArray);
  if ($newUserId) {
    echo "new user inserted with the id $new_user_id";
  }

Example: REPLACE

  $replaceArray = array(
      'name'   => 'lars',
      'email'  => '[email protected]',
      'group'  => 0
  );
  $tmpId = $this->db->replace('users', $replaceArray);

###binding parameters on queries

Binding parameters is a good way of preventing mysql injections as the parameters are sanitized before execution.

  $sql = "SELECT * FROM users 
    WHERE id_user = ? 
    AND active = ? 
    LIMIT 1
  ";
  $result = $db->query($sql, array(11,1));
  if ($result) {
    $user = $result->fetchArray();
    print_r($user);
  } else {
    echo "user not found";
  }

###Using the Result-Class

After executing a SELECT query you receive a Result object that will help you manipulate the resultant data. there are different ways of accessing this data, check the examples bellow:

####Fetching all data

  $result = $db->query("SELECT * FROM users");
  $allUsers = $result->fetchAll();

Fetching all data works as Object or Array the fetchAll() method will return the default based on the $_default_result_type config. Other methods are:

$row = $result->fetch();        // fetch a single result row as defined by the config (Array or Object)
$row = $result->fetchArray();   // fetch a single result row as Array
$row = $result->fetchObject();  // fetch a single result row as Object

$data = $result->fetchAll();        // fetch all result data as defined by the config (Array or Object)
$data = $result->fetchAllArray();   // fetch all result data as Array
$data = $result->fetchAllObject();  // fetch all result data as Object

$data = $result->fetchColumn(String $Column);                  // fetch a single column in a 1 dimention Array
$data = $result->fetchArrayPair(String $key, String $Value);   // fetch data as a key/value pair Array.

####Aliases

  $db->get()                  // alias for $db->fetch();
  $db->getAll()               // alias for $db->fetchAll();
  $db->getObject()            // alias for $db->fetchAllObject();
  $db->getArray()             // alias for $db->fetchAllArray();
  $db->getColumn($key)        // alias for $db->fetchColumn($key);

####Iterations To iterate a result-set you can use any fetch() method listed above.

  $result = $db->select('users');

  // using while
  while($row = $result->fetch()) {
    echo $row->name;
    echo $row->email;
  }

  // using foreach
  foreach($result->fetchAll() as $row) {
    echo $row->name;
    echo $row->email;
  }

####Logging and Errors

You can hook into the "DB"-Class, so you can use your personal "Logger"-Class. But you have to cover the methods:

$this->trace(String $text, String $name) { ... }
$this->debug(String $text, String $name) { ... }
$this->info(String $text, String $name) { ... }
$this->warn(String $text, String $name) { ... } 
$this->error(String $text, String $name) { ... }
$this->fatal(String $text, String $name) { ... }

You can also disable the logging of every sql-query, with the "getInstance()"-parameter "logger_level" from "DB"-Class. If you set "logger_level" to something other than "trace" or "debug", the "DB"-Class will only log errors anymore.

Showing the query log. the log comes with the SQL executed, the execution time and the result row count

  print_r($db->log());

to debug mysql errors:

use $db->errors() to fetch all errors (returns false if no errors) or $db->lastError() for information on the last error.

  if ($db->errors()) {
      echo $db->lastError();
  }