cywang886 / db Goto Github PK

This database class is a simple wrapper for mysqli interface.  It is for the Fuel PHP framework as a simple drop in replacement for the standard database handler.  I wrote it because I prefer to write my own SQL statements and the database handler in the framework is not really meant to work that way.

The reason I believe that it is better to write your own SQL statements rather than having the framework write it for you is that you have more control over your queries.  This may not be very important when writing smaller apps, but for scalability, you'll want to be able to optimize your queries as your site grows.  Instead if fighting the framework to make it do what you want.  I find it much easier to just write the SQL yourself.  It's also more efficient, less likely to be buggy, and easier to debug when there are problems.

The queries use vsprintf() to create the actual query and the parameters are all run through mysqli::escape_string();  There are a variety of select methods for return and also methods for insert, update, replace, delete.  If you need to run a query that doesn't fall into these categories, you can use the query() method.

When a query fails it will throw a Database_Exception Exception.

There is still much work to do and features that I would like to add.  But at the moment, it is very much usable.


Configuration
-------------

Your configuration in (fuel/app/config/db.php) should look something like this.

return array(
	'active' => \Fuel::$env,

	\Fuel::DEVELOPMENT => array(
		'options' => array(
			'master_on_write' => 'true',
		),
		'servers' => array(
			array(
				'hostname' => 'localhost',
				'username' => 'root',
				'password' => 'root',
				'database' => 'admin',
				'port'     => '3306',
				'charset'  => 'utf8'		// Optional
			),
		),
	),
);

You'll may that the configuration is an array within an array.  That is because you can define multiple servers for master-slave replication.  The first server definition is the master and any servers that you define after that are read servers.

The class will automatically select which server to use for your query.  If the query is a SELECT query, it chooses a random slave server to read from.

If you set master_on_write to true.  The master will be used for all read queries once a write occurs. This is to help prevent select after insert problem caused by slow replication.  You'll have to determine for your application whether the extra load on your master is worth it.

Usage
-----

To start, you'll need to get an instance of the database handler.  This code will also make the connection to the database if a connection has not already been established.

$db = DB::instance();

You can also have multiple named instances

$db = DB::instance('my-database');

The name of the default database instance is 'default'.  The follow two calls are identical:

$db = DB::instance()
$db = DB::instance('default');



Methods
-------

// Get all the users.  This returns an array of users
$users = $db->select('SELECT * FROM users');
foreach ($users as $user) {
	echo $user['username'];
}

// Get a single row from the database
$users = $db->selectRow('SELECT * FROM users WHERE user_id = "%d"', $user_id);
echo $users['username'];

// Get a single value from the database
$username = $db->selectValue('SELECT username FROM users WHERE user_id = "%d"', $user_id);
echo $username;

// Get a flatten array of values
$usernames = $db->selectFlat('SELECT username FROM users');
foreach ($usernames as $username) {
	echo $username;
}

// Get number of rows from last query
$rows = $db->rows();

// Get number of affected rows
$rows = $db->affectedRows();

// Get last insert ID
$id = $db->insertId();

// Insert a row.  Returns the insert ID value
$id = $db->insert('INSERT INTO users ('username') VALUES ("%d")', $username);

// Update a row
$db->update('UPDATE users SET username = "%s" WHERE user_id = "%d"', $username, $user_id);

// Replace a row
$db->replace('REPLACE users ('username') VALUES ("%d")', $username);

// Delete a row
$db->delete('DELETE FROM users WHERE user_id = "%d"', $user_id);

// Generic query
$db->query('TRUNCATE users');

// Begin a transaction
$db->begin();

// Commit a transaction
$db->commit();

// Rollback a transaction
$db->rollback();

// Get server status string
$db->stat();					// Server status of the read server
$db->stat('read');				// Server status of the read server
$db->stat('write');				// Server status of the write server

// Get the server version
$db->serverVersion();			// Server version of the read server
$db->serverVersion('read');	// Server version of the read server
$db->serverVersion('write');	// Server version of the write server