Database

An easy to use class for Database queries in PHP.

• API
• Connections
• Examples
• Install

API

DB::connect($db='test',$pass='',$user='root',$host='localhost',$type='mysql');
DB::getPdo();
DB::setPdo($db);
DB::quote($string,$remove_quotes=false);
DB::query($query, $params = array());
DB::fetchAll($query);
DB::fetchAll_safe($query);
DB::fetch_assoc($query);
DB::fetch_safe_assoc($query);
DB::fetch_object($query);
DB::fetch_safe_object($query);
DB::num_rows($query);

See Below for usage.

Connections

This class lets you connect to PDO whichever way you choose, This maximizes flexibility by letting you simply give us your already existing object, or by using our class as your primary Database package and asking for the PDO object if you need to pass it to other libraries.

Connection Syntax

You can connect using multiple syntax to make it easy to use, available both in the __construct() as well as connect()

You can either use an associative array or the default which uses reverse order to let you define the most important values first, and lets you default irrelevant values such as host or type to it's defaults ('mysql' and 'localhost')

Via Instantiation

$db = new Database($db='test',$pass='',$user='root',$host='localhost',$type='mysql'); # Default Syntax
$db = new Database(['host'=>$host,'dbname'=>$database,'user'=>$username,'pass'=>$password]); # Alternative Syntax

Via Method

$db->connect(DB,PASS,USER,HOST); # Establish a Connection With PDO

Via Existing PDO Object

$db->setPdo($pdo); # Assign PDO Connection to the Database Class

Usage and Use Case

A facade is optional but has all the same functionality of the main class.

Regular Use Case

$db = new Database(DB,PASS,USER,HOST); # Establish a Connection
$query = $db->query("SELECT * FROM table");
while($item = $db->fetch_object($query))
{
    echo'#'.htmlspecialchars($item->id).': '.htmlspecialchars($item->name).'<br>';
}

Facades Use Case

Create Facade from PDO Object

$db = new Database(DB,PASS,USER,HOST); # Establish a Connection
DB::Facade($db); # Initiate Database object Facade

Connect Via Facade

DB::connect('database','pass','user','host');

Use Case

$query = DB::query("SELECT * FROM table");
while($item = DB::fetch_object($query))
{
    echo'#'.htmlspecialchars($item->id).': '.htmlspecialchars($item->name).'<br>';
}

Query

$query = DB::query("SELECT * FROM table WHERE id = ?", [$_GET['id']]);

This is a query with bind parameters. First argument is the statement, second argument is an array of parameters (optional)

Note: We passed the query into a variable for later re-use.

Quote

$quoted_string = DB::quote($_GET['id']);

# Remove Quotes after quoting, and right before output,
# giving you a similar string as mysql_real_escape_string
$quoted_string = DB::quote($_GET['id'], 1);

Escaping in PDO adds quotes around the escaped string, which is an issue if you try doing a LIKE query:

# Default Quote adds '' quotes around the field, forcing you to do:
DB::query("SELECT * FROM table WHERE field LIKE ?", ['%'.$input.'%']);
DB::query("SELECT * FROM table WHERE field LIKE ".DB::quote('%'.$input.'%'));

# Removed Quoting, quotes but removes added quotes
DB::query("SELECT * FROM table WHERE field LIKE '%".DB::quote($input,1)."%'";

PDO does not provide a way to turn off quotes around escaped strings so, we created a function that simply removes the quotes (first and last characters). This returns a string similar to the old mysql_real_escape_string function.

Please note that this requires you to start adding quotes yourself. Escaping is the default when you bind parameters in PDO. As such, escaping is turned on by default as per the original function (passthrough).

Fetch and Safe Fetch

This is regular returned object. You still need to apply htmlspecialchars yourself.

$table = DB::fetch_object($query);

This is safe returned object. htmlspecialchars is applied to all the objects's properties.

$table = DB::fetch_safe_object($query);

Num Rows

DB::num_rows($query); # Equivalent of $pdo->rowCount();

Data Examples

# Loop Objects
while($entry = DB::fetch_safe_object($query))
{
	# Because of fetch_safe_object we don't need to apply htmlspecialchars
    echo '<a href="page?id='.$entry->id.'">'.$entry->name.'</a><br />';
}
# Single Object
$entry = DB::fetch_safe_object($query);
echo $entry->name;

# Loop Objects Using Foreach instead with Fetchall
foreach(DB::fetchAll_safe($query) as $entry)
{
	# Because of fetchAll_safe we don't need to apply htmlspecialchars
    echo '<a href="page?id='.$entry->id.'">'.$entry->name.'</a><br />';
}
# Single Object
$entry = DB::fetchAll_safe($query);
echo $entry[0]->name;

Installation

via Composer:

composer require modularr/database

Or install like so:

{
    "require": {
        "modularr/database": "2.*"
    }
}

Manual:

1. Download Release Or copy file manually
2. Include Main.php found under src/ (this includes both Database.php and Facade.php)