First, Executable code as a configuration file is a bad practice. I recommend using ini files - the code to parse over them is internal to the PHP engine so no performance loss. Information on the formatting is in the entry for [fphp]parse_ini_file[/fphp]
Second, unless you have a specific reason to use the mysqli extension, use [fphp]pdo[/fphp]. There is currently a debate on whether or not to remove all non pdo libraries from PHP core by default, relagating them to pecl libraries for inclusion by those remaining projects that need them, starting with PHP 5.4. Whether or not this will occur is not settled, but signs point to probably.
The major advantage of PDO is it is object oriented, allowing you to extend it. This is my extension to it.
<?php
namespace Gazelle;
/**
* Gazelle core database extends the PDO library.
* @author Michael
*
*/
class Database extends \\PDO {
/**
* CONSTRUCT. Convert the configuration array into a DSN and pass that down to
* PDO.
* @param array $config
*/
public function __construct( array $config ) {
assert ($config['database'] && $config['user'] && $config['password']);
if (isset($config['dsn'])) {
// If a DSN is set use it without question.
parent::__construct($config['dsn'], $config['user'], $config['password']);
} else {
// Otherwise hash the vars we were given into a DSN and pass that.
$params = array(
'driver' => isset($config['driver']) ? $config['driver'] : 'mysql',
'database' => $config['database'],
'user' => $config['user'],
'password' => $config['password'],
'server' => isset($config['server']) ? $config['server'] : 'localhost',
'port' => isset($config['port']) ? $config['port'] : '3306'
);
// Start underlying PDO library.
parent::__construct("{$params['driver']}:dbname={$params['database']};host={$params['server']};port={$params['port']}",
$params['user'],
$params['password']
);
}
}
/**
* Prepare and execute sql with it's parameters.
* @param string $sql
* @param array|null $params
*/
protected function prepareAndExecute( $sql, $params = null ) {
$s = $this->prepare( $sql );
if ( is_array($params)) {
$s->execute( $params );
} else {
$s->execute();
}
if ($s->errorCode() != '00000') {
throw new DatabaseException ( print_r($s->errorInfo(), true) );
}
return $s;
}
/**
* Run a query and return the result in a form suited to the result.
* You should be able to predict the return based on the text of
* your query, but if unsure use query all.
*
* For any query you need to issue multiple times you will realize
* better performance by using #prepare and then executing the
* received statement object.
*
* @param string sql statement - required
* @param array sql parameters - optional
*
* @return mixed
*/
public function quickQuery($sql, $params ) {
$s = $this->prepareAndExecute($sql, $params);
$columns = $s->columnCount();
$rows = $s->rowCount();
// A single column and row - return the value found.
if ( $rows == 1 && $columns == 1 ) {
return $s->fetchColumn();
}
// A pair of columns we presume column 1 is the index and column 2 the value.
// We maintain this format even if there was only a single row.
else if ( $columns == 2 ) {
$r = array();
while ($row = $s->fetch( parent::FETCH_NUM )){
$r[$row[0]] = $row[1];
}
return $r;
}
// Single row - return it.
else if ( $rows == 1 ) {
return $s->fetch( parent::FETCH_ASSOC );
}
// Single column - return it
else if ( $columns == 1 ) {
return $s->fetchAll( parent::FETCH_COLUMN );
}
// Just shoot back everything.
else {
return $s->fetchAll( parent::FETCH_ASSOC );
}
}
/**
* Get all results of a query. Unlike quickQuery, queryAll
* doesn't try to figure out your intent - it returns
* a full associative array.
*
* @param string $sql
* @param array $params
*/
public function queryAll( $sql, array $params = array() ) {
$s = $this->prepareAndExecute($sql, $params);
return $s->fetchAll( parent::FETCH_ASSOC );
}
}
The relevant section of my configuration file:
db[class] = Gazelle\\Database
;db[dsn]
db[driver] = mysql
db[user] = 'user'
db[password] = 'password' ; This is just a sample - not my real password of course.
db[server] = localhost
db[port] = 3306
db[database] = gazelle
This allows my outside code to read like this…
$config = parse_ini_file('path/to/config.ini');
$db = new \\Gazelle\\Database($config['db']);
$result = $db->quickQuery( 'SELECT name FROM user WHERE id = 3' );
Hope this helps.