You must specify the username, password, and database name before running any queries. If you don't specify a host, it defaults to localhost. The database connection won't actually be established until the first time you run a query. This means you can set these variables in a globally included setup file, and never worry about actually opening or closing your database connection.

    DB::$user = 'my_database_user';
    DB::$password = 'my_database_password';
    DB::$dbName = 'my_database_name';
    DB::$host = '123.111.10.23'; //defaults to localhost if omitted
    DB::$port = '12345'; // defaults to 3306 if omitted
    DB::$encoding = 'utf8'; // defaults to latin1 if omitted
  

If a query returns an error, MeekroDB will normally print out some debugging information (error, the query it happened on, and a backtrace) and exit. You can change this behavior by setting your own callback function which will get called on errors. You can set this to false if you don't want any function to be called on errors.

Default: Setting this back to true will restore the default error handler.

    DB::$error_handler = 'my_error_handler';
    
    function my_error_handler($params) {
      echo "Error: " . $params['error'] . "
\n";
      echo "Query: " . $params['query'] . "
\n";
      die; // don't want to keep going if a query broke
    }
    
    // this broken query will cause my_error_handler() to run
    DB::query("SELCT * FROM accounts"); // misspelled SELECT
    
    class Errors {
      public static function static_error_handler($params) {
        echo "Error: " . $params['error'] . "
\n";
        echo "Query: " . $params['query'] . "
\n";
        die; // don't want to keep going if a query broke
      }
      
      public function error_handler($params) {
        echo "Error: " . $params['error'] . "
\n";
        echo "Query: " . $params['query'] . "
\n";
        die; // don't want to keep going if a query broke
      }
    }
    
    // use a static class method as an error handler
    DB::$error_handler = array('Errors', 'static_error_handler'); 
    
    // use an object method as an error handler
    $my_object = new Errors();
    DB::$error_handler = array($my_object, 'error_handler');
    
    //restore default error handler
    DB::$error_handler = true;
    
    //ignore errors (BAD IDEA)
    DB::$error_handler = false;
  

If you set this to true, errors will cause a MeekroDBException to be thrown.

    DB::$error_handler = false; // since we're catching errors, don't need error handler
    DB::$throw_exception_on_error = true;
    
    try {
      // try to insert something with a primary key that already exists
      // will cause an exception to get thrown, and we'll catch it
      DB::insert('accounts', array(
        'id' => 2, // duplicate primary key 
        'username' => 'Joe',
        'password' => 'asd254890s'
      ));
    } catch(MeekroDBException $e) {
      echo "Error: " . $e->getMessage() . "
\n"; // something about duplicate keys
      echo "SQL Query: " . $e->getQuery() . "
\n"; // INSERT INTO accounts...
    }
    
    // restore default error handling behavior
    // don't throw any more exceptions, and die on errors
    DB::$error_handler = 'meekrodb_error_handler';
    DB::$throw_exception_on_error = false;
  

Works just like DB::$error_handler, except it gets triggered on non-SQL errors. For example, this will be run to alert you if you mess up the MeekroDB syntax, or if you can't connect to the MySQL server.

Default: null (for the default nonsql error handler)

Works just like DB::$throw_exception_on_error, except it gets triggered on non-SQL errors. For example, this will be run to alert you if you mess up the MeekroDB syntax, or if you can't connect to the MySQL server.

Default: false (don't throw exceptions, instead use the nonsql error handler)

If set to true, the system will echo a report after each query with the query string and how long it took to run. You can also set a custom function or class method that will run after every command.

 
    DB::$success_handler = true; // echo out each SQL command being run, and the runtime
    $results = DB::query("SELECT * FROM accounts WHERE password=%s", 'hello'); // some command
    
    DB::$success_handler = 'my_success_handler'; // run this function after each successful command
    function my_success_handler($params) {
      echo "Command: " . $params['query'] . "
\n";
      echo "Time To Run It: " . $params['runtime'] . " (milliseconds)
\n";
    }
    $results = DB::query("SELECT * FROM accounts"); // some command
    
    class Success {
      public static function static_success_handler() {
        echo "Command: " . $params['query'] . "
\n";
        echo "Time To Run It: " . $params['runtime'] . " (milliseconds)
\n";
      }
      
      public function success_handler() {
        echo "Command: " . $params['query'] . "
\n";
        echo "Time To Run It: " . $params['runtime'] . " (milliseconds)
\n";
      }
    }
    
    // use a static class method as an success handler
    DB::$success_handler = array('Success', 'static_success_handler'); 
    
    // use an object method as an success handler
    $my_object = new Success();
    DB::$success_handler = array($my_object, 'success_handler');
    
    DB::$success_handler = false; // disable success handler
  

Set this to the character or string that will preceed query parameters. The default is '%'.

    // don't want the DATE_FORMAT string to be evaluated by MeekroDB
    // pass it directly to MySQL as written instead, and use ##i to refer
    // to the MeekroDB 'integer' (which is normally %i)
    DB::$param_char = '##';
    $row = DB::queryFirstRow( "SELECT DATE_FORMAT( c.sent, '%b %d %h:%i %p' ) 
      FROM `call` c WHERE pk=##i", 4 );
    
    DB::$param_char = '%'; // revert to normal behavior
  

Set to true to enable nested transactions (it is disabled by default). You can then use DB::startTransaction() from within a transaction to start another one. This relies internally on MySQL SAVEPOINT.

  DB::$nested_transactions = true;
  
  $depth = DB::startTransaction();
  echo "We are now " . $depth . " transactions deep.\n"; // 1
  DB::query("UPDATE accounts SET weight=%i WHERE username=%s", 150, 'Joe');
  
  $depth = DB::startTransaction();
  echo "We are now " . $depth . " transactions deep.\n"; // 2
  DB::query("UPDATE accounts SET weight=%i WHERE username=%s", 160, 'Joe');
  $depth = DB::rollback(); // rollback just the inner transaction
  echo "We are now " . $depth . " transactions deep.\n"; // 1
  
  $depth = DB::commit(); // commit the outer transaction
  echo "We are now " . $depth . " transactions deep.\n"; // 0
  
  $weight = DB::queryFirstField("SELECT weight FROM accounts WHERE username=%s", 'Joe');
  echo "Joe's weight is " . $weight . "\n"; // 150
  

You can check how many transactions are open by calling DB::transactionDepth(), or checking return values from DB::startTransaction(), DB::commit(), and DB::rollback().

  DB::$nested_transactions = true;
  
  DB::startTransaction();
  $depth = DB::startTransaction();
  // $depth is 2
  
  $depth = DB::transactionDepth();
  // $depth is still 2
  

You can rollback or commit all active transactions by passing true to DB::commit() or DB::rollback().

  DB::$nested_transactions = true;
  
  DB::startTransaction();
  DB::startTransaction();
  $depth = DB::transactionDepth(); // $depth is 2
  
  DB::commit(true);
  $depth = DB::transactionDepth(); // $depth is 0
  

The first parameter is a query string with placeholders variables. Following that, you must have an additional parameter for every placeholder variable.

If you need to refer to a specific parameter, rather than just getting them in the order that they were passed in, you can put a number after the placeholder. The first parameter passed will have number 0. You can even re-use the same parameter multiple times!

You can also pass in an array of named parameters. These will be accessed through placeholders of the form %s_somename, %i_somenumber, and the like.

Placeholder Variables
%s	string
%i	integer
%d	decimal/double
%ss	search string (string surrounded with % for use with LIKE)
%l	literal (no escaping or parsing of any kind -- BE CAREFUL)
%ls	list of strings (array)
%li	list of integers
%ld	list of decimals/doubles
%ll	list of literals (no escaping or parsing of any kind -- BE CAREFUL)

Return: Returns an array of associative arrays. If your query produced no results, you get an empty array.

    // no placeholders
    DB::query("SELECT * FROM tbl");
    
    // string, integer, and decimal placeholders
    DB::query("SELECT * FROM tbl WHERE name=%s AND age > %i AND height <= %d", $name, 15, 13.75);
    
    // use the parameter number to refer to parameters out of order
    DB::query("SELECT * FROM tbl WHERE name=%s2 AND age > %i0 AND height <= %d1", 15, 13.75, $name);
    
    // use named parameters
    DB::query("SELECT * FROM tbl WHERE name=%s_name AND age > %i_age AND height <= %d_height", 
      array(
        'name' => $name,
        'age' => 15,
        'height' => 13.75
      )
    );
    
    // list of strings and list of integers placeholders
    $results = DB::query("SELECT * FROM tbl WHERE name IN %ls AND age NOT IN %li", array('John', 'Bob'), array(12, 15));
    
    // using the results from the last query
    // you get an array of associative arrays, so you can interate over the rows
    // with foreach
    foreach ($results as $row) {
      echo "Name: " . $row['name'] . "\n";
      echo "Age: " . $row['age'] . "\n";
      echo "Height: " . $row['height'] . "\n";
      echo "-------------\n";
    }
  

Retrieve the first row of results for the query, and return it as an associative array. If the query returned no rows, this returns null.

    // get information on the account with the username Joe
    $account = DB::queryFirstRow("SELECT * FROM accounts WHERE username=%s", 'Joe');
    echo "Username: " . $account['username'] . "\n"; // will be Joe, obviously
    echo "Password: " . $account['password'] . "\n";
  

Retrieve the first row of results for the query, and return it as a numbered index (non-associative) array. If the query returned no rows, this returns null.

    // get information on the account with the username Joe
    list($username, $password) = DB::queryFirstList("SELECT username, password FROM accounts WHERE username=%s", 'Joe');
    echo "Username: " . $username . "\n"; // will be Joe, obviously
    echo "Password: " . $password . "\n";
  

Retrieve the first column of results for the query, and return it as a regular array. If the query returned no rows, this returns an empty array.

    // get a list of DISTINCT usernames in the accounts table (skip duplicates, if any)
    $usernames = DB::queryFirstColumn("SELECT DISTINCT username FROM accounts");
    foreach ($usernames as $username) {
      echo "Username: " . $username . "\n";
    }
  

Retrieve the requested column of results from the query, and return it as a regular array. If the query returned no rows, or the requested column isn't in the result set, this returns an empty array.

    // get a list of ALL usernames in the accounts table
    $usernames = DB::queryOneColumn('username', "SELECT * FROM accounts");
    foreach ($usernames as $username) {
      echo "Username: " . $username . "\n";
    }
  

Get the contents of the first field from the first row of results, and return that. If no rows were returned by the query, this returns null.

    // get Joe's password and print it out 
    $joePassword = DB::queryFirstField("SELECT password FROM accounts WHERE username=%s", 'Joe');
    echo "Joe's password is: " . $joePassword . "\n";
  

Get the contents of the requested field from the first row of results, and return that. If no rows were returned by the query, this returns null.

    // get Joe's password and print it out 
    $joePassword = DB::queryOneField('password', "SELECT * FROM accounts WHERE username=%s", 'Joe');
    echo "Joe's password is: " . $joePassword . "\n";
  

Like DB::query(), except it returns a standard MySQLi_Result object instead of an array of associative arrays. This is intended for situations where the result set is huge, and PHP's memory is not enough to store the whole thing all at once.

    $mysqli_result = DB::queryRaw("SELECT * FROM accounts WHERE username=%s", 'Joe');
    $row = $mysqli_result->fetch_assoc();
    echo "Joe's password is: " . $row['password'] . "\n";
  

Either INSERT or REPLACE a row into a table. You can use DB::sqleval() to force something to be passed directly to MySQL and not escaped. You may insert multiple rows at once by passing an array of associative arrays.

    // insert a new account
    DB::insert('accounts', array(
      'username' => 'Joe',
      'password' => 'hello'
    ));
    
    // change Joe's password (assuming username is a primary key)
    DB::replace('accounts', array(
      'username' => 'Joe',
      'password' => 'asd254890s'
    ));
    
    // use DB::sqleval() to pass things directly to MySQL
    // sqleval() supports the same parameter structure as query()
    DB::insert('accounts', array(
      'username' => 'Joe',
      'password' => 'hello',
      'data' => DB::sqleval("REPEAT('blah', %i)", 4), // REPEAT() is evaluated by MySQL
      'time' => DB::sqleval("NOW()") // NOW() is evaluated by MySQL
    ));
    
    // insert two rows at once
    $rows = array();
    $rows[] = array(
      'username' => 'Frankie',
      'password' => 'abc'
    );
    $rows[] = array(
      'username' => 'Bob',
      'password' => 'def'
    );
    DB::insert('accounts', $rows);
  

Works like INSERT, except it does an INSERT IGNORE statement. Won't give a MySQL error if the primary key is already taken.

  // insert new account, don't throw an error if primary key id is already taken
  DB::insertIgnore('accounts', array(
    'id' => 5, //primary key
    'username' => 'Joe',
    'password' => 'hello'
  ));
  

Similar to INSERT, except it does an INSERT ... ON DUPLICATE KEY UPDATE. After the usual insert syntax, you can specify one of three things: a query-like string with the update component, a second associative array with the keys and values to update, or nothing, in which case the INSERT associative array gets re-used.

  // insert new account, if id 5 is already taken
  // then change Joe's password to goodbye instead
  DB::insertUpdate('accounts', array(
    'id' => 5, //primary key
    'username' => 'Joe',
    'password' => 'hello'
  ), 'password=%s', 'goodbye');
  
  // same as above
  DB::insertUpdate('accounts', array(
    'id' => 5, //primary key
    'username' => 'Joe',
    'password' => 'hello'
  ), array(
    'password' => 'goodbye'
  ));
  
  // insert new account, if id 5 is taken then the username and password fields
  // will be set to 'Joe' and 'hello' respectively and all other fields ignored
  // this is a bit like REPLACE INTO, except we leave any other columns in the table
  // untouched
  DB::insertUpdate('accounts', array(
    'id' => 5, //primary key
    'username' => 'Joe',
    'password' => 'hello'
  ));
  

Run an UPDATE command by specifying an array of changes to make, and a WHERE component. The WHERE component can have parameters in the same style as the query() command. As with insert() and replace(), you can use DB::sqleval() to pass a function directly to MySQL for evaluation.

  // change Joe's password
  DB::update('accounts', array(
    'password' => 'sdfdd'
    ), "username=%s", 'Joe');
  
  // set Joe's password to "joejoejoe"
  // WARNING: Passing user-submitted data to sqleval() will probably create a security flaw!!
  DB::update('accounts', array(
    'password' => DB::sqleval("REPEAT('joe', 3)")
    ), "username=%s", 'Joe');
  

Run the MySQL DELETE command with the given WHERE conditions.

  // delete Joe's account
  DB::delete('accounts', "username=%s", 'Joe');
  

Returns the auto incrementing ID for the last insert statement. The insert could have been done through DB::insert() or DB::query().

    // insert a new account
    DB::insert('accounts', array(
      'id' => 0, // auto incrementing column
      'username' => 'Joe',
      'password' => 'hello'
    ));
    
    $joe_id = DB::insertId(); // which id did it choose?!? tell me!!
  

Counts the number of rows returned by the last query. Ignores queries done with DB::queryFirstRow() and DB::queryFirstField().

 
    DB::query("SELECT * FROM accounts WHERE password=%s", 'hello');
    $counter = DB::count();
    echo $counter . " people are using hello as their password!!\n";
  

Returns the number of rows changed by the last update statement. That statement could have been run through DB::update() or DB::query().

 
    // give a better password to everyone who is using hello as their password
    DB::query("UPDATE accounts SET password=%s WHERE password=%s", 'sdfwsert4rt', 'hello');
    $counter = DB::affectedRows();
    echo $counter . " people just got their password changed!!\n";
  

An alias for DB::$success_handler.

 
    DB::debugMode(); // echo out each SQL command being run, and the runtime
    $results = DB::query("SELECT * FROM accounts WHERE password=%s", 'hello'); // some command
    
    DB::debugMode('my_debugmode_handler'); // run this function after each successful command
    function my_debugmode_handler($params) {
      echo "Command: " . $params['query'] . "
\n";
      echo "Time To Run It: " . $params['runtime'] . " (milliseconds)
\n";
    }
    $results = DB::query("SELECT * FROM accounts"); // some command
    
    DB::debugMode(false); // disable debug mode
  

Switch to a different database.

 
    DB::useDB('my_other_database');
    $result = DB::query("SELECT * FROM my_table");
  

These are merely shortcuts for the three standard transaction commands: START TRANSACTION, COMMIT, and ROLLBACK.

When DB::$nested_transactions are enabled, these commands can be used to have multiple layered transactions. Otherwise, running DB::startTransaction() when a transaction is active will auto-commit that transaction and start a new one.

 
    // give a better password to everyone who is using hello as their password
    // but ONLY do this if there are more than 3 such people
    DB::startTransaction();
    DB::query("UPDATE accounts SET password=%s WHERE password=%s", 'sdfwsert4rt', 'hello');
    $counter = DB::affectedRows();
    if ($counter > 3) {
      echo $counter . " people just got their password changed!!\n";
      DB::commit();
    } else {
      echo "No one got their password changed!\n";
      DB::rollback();
    }
  

Get an array of the tables in either the current database, or the requested one.

 
    $current_db_tables = DB::tableList();
    $other_db_tables = DB::tableList('other_db');
    
    foreach ($other_db_tables as $table) {
      echo "Table Name: $table\n";
    }
  

Get an array of the columns in the requested table.

 
    $columns = DB::columnList('accounts');
    
    foreach ($columns as $column) {
      echo "Column: $column\n";
    }
  

A helper class for building the WHERE part of an SQL string out of pieces. It has methods add, addClause, negateLast, and negate, which are demonstrated below.

 
    $where = new WhereClause('and'); // create a WHERE statement of pieces joined by ANDs
    $where->add('username=%s', 'Joe');
    $where->add('password=%s', 'mypass');
    
    // SELECT * FROM accounts WHERE (`username`='Joe') AND (`password`='mypass')
    $results = DB::query("SELECT * FROM accounts WHERE %l", $where->text());
    
    $subclause = $where->addClause('or'); // add a sub-clause with ORs
    $subclause->add('age=%i', 15);
    $subclause->add('age=%i', 18);
    $subclause->negateLast(); // negate the last thing added (age=18)
    
    // SELECT * FROM accounts WHERE (`username`='Joe') AND (`password`='mypass') AND ((`age`=15) OR (NOT(`age`=18)))
    $results = DB::query("SELECT * FROM accounts WHERE %l", $where->text());
    
    $subclause->negate(); // negate this entire subclause
    
    // SELECT * FROM accounts WHERE (`username`='Joe') AND (`password`='mypass') AND (NOT((`age`=15) OR (NOT(`age`=18))))
    $results = DB::query("SELECT * FROM accounts WHERE %l", $where->text());
  

If you're going to use WhereClause() with an object-oriented instance of MeekroDB, you have to pass the MeekroDB instance to the WhereClause constructor.

    // going to use object-oriented MeekroDB
    $mdb = new MeekroDB($host, $user, $pass, $dbName, $port, $encoding);
    
    // need a WhereClause? pass the instance to the constructor!
    $where = new WhereClause('or', $mdb);
  

All MeekroDB functions and variables can also be used in an object-oriented approach. We don't recommend this because most projects don't need multiple database connections, and it's annoying to pass $db objects around all the time.

The MeekroDB() constructor accepts the parameters $host, $user, $pass, $dbName, $port, $encoding. You can omit any or all of them, and any that are omitted will use either the value that was set for the static instance of MeekroDB, or the defaults shown in the standard variables section.

 
    DB::$user = 'my_database_user'; // configure MeekroDB like normal
    DB::$password = 'my_database_password';
    DB::$dbName = 'my_database_name';
    
    // ... (code passes)
    $mdb = new MeekroDB(); // don't need to pass any config parameters
                           // it'll just read them from the above
    
  

If you want to configure your object instance of MeekroDB separately, you can. The parameters are the same as described in the standard variables section.

$mdb = new MeekroDB($host, $user, $pass, $dbName, $port, $encoding);

Once connected, you can run all the MeekroDB functions described in the documentation. You can also set all the same parameters.

    $row = $mdb->queryFirstRow("SELECT name, age FROM tbl WHERE name=%s LIMIT 1", 'Joe');
    echo "Name: " . $row['name'] . "\n"; // will be Joe, obviously
    
    $mdb->param_char = '##';
    $row2 = $mdb->queryFirstRow("SELECT name, age FROM tbl WHERE name=##s LIMIT 1", 'Frank');
    echo "Name: " . $row2['name'] . "\n"; // will be Frank, obviously
  

This helper function operates on an array of associative arrays, such as the kind returned by DB::query(). It lets you get a simple array of all the values for one column in the original array of associative arrays.

    $users = DB::query("SELECT name, age, address FROM users");
    
    $names = DBHelper::verticalSlice($users, 'name');
    // Above line is equivalent to:
    
    foreach ($users as $user) {
      $names[] = $user['name'];
    }
  

It also accepts an optional third parameter, which lets you set keys for the indexes in the new array.

    $users = DB::query("SELECT name, age, address FROM users");
    
    $ages = DBHelper::verticalSlice($users, 'age', 'name');
    // Above line is equivalent to:
    
    $names = array();
    foreach ($users as $user) {
      $names[$user['name']] = $user['age'];
    }
  

This helper function operates on an array of associative arrays, such as the kind returned by DB::query(). It re-formats the array with a column serving as the index.

In the example below, if there are two people named Frank, only one of them will be included.

    $users = DB::query("SELECT name, age, address FROM users");
    
    $users_by_name = DBHelper::reIndex($users, 'name');
    $frank = $users_by_name['Frank']
    echo "Frank's age is " . $frank['age'] . "\n"; // 15
  

You can index the associative arrays by multiple columns as well.

    $users = DB::query("SELECT name, age, address FROM users");
    
    $users_by_name_and_age = DBHelper::reIndex($users, 'name', 'age');
    $frank = $users_by_name_and_age['Frank']['15'];
    echo "Frank's address is " . $frank['address'] . "\n";
    }