modularr / database
Simple Database Library
README
An easy to use class for Database queries in PHP.
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:
- Download Release Or copy file manually
- Include Main.php found under src/ (this includes both Database.php and Facade.php)