This database utility class is static (you do not need to create any type of object to use it) for easy of use, performance, and convenient code completion. All of the code is in a single file to make it incredibly easy to install and learn. It uses an internal global PDO database connection to make it easier to retrofit into existing projects or use in new projects.
I also made every effort to take care of all the details like:
Incredibly easy-to-use and detailed debugging
Automatic SQL generation
Try/catch error checking
Error event handling and PHP error logging
Security
Full transaction processing
and using as little memory and being as lightweight as possible while still containing a lot of great features.
Some basic knowledge of how PDO "placeholders" work is helpful but not necessary. Every effort to use them is applied to stop SQL injection hacks and also because:
First, you'll always want to connect to the database. There is no need to create an object or variable that points to the database. All of this is handled for you using an internal session variable.
Optional $hostname: Host name of the server (if not specified, localhost is used)
Optional $silent_errors: If true then no errors are shown on queries
If there is an error connecting, a string containing the error is returned. If there is no error, false is returned.
// Connect to the database$error = DB::Connect('appuser', 'pass123', 'production');
// If there was an error, stop and display itif ($error) die($error);
If you have a SQL statement that needs to be executed and doesn't return anything (usually an INSERT, UPDATE, or DELETE), use this method. Use DB::Insert, DB::Update, or DB::Delete if you are not using SQL (these methods automatically generate and execute SQL for you on the backend).
Optional $placeholders: Associative array placeholders for binding to SQL. array('name' => 'Cathy', 'city' => 'Cape Cod')
Optional $debug: If set to true, will output results and query information
The return result varies depending on the situation. If the SQL was an INSERT statement, the primary key of the record will be returned. Otherwise, if there was an error, false is returned. If the execution was sucessful, true is returned.
// Execute a SQL query// (FYI: You can also use DB::Safe() to return a safe quoted string for SQL)$sql = "INSERT INTO test_table (name, age, active) VALUES ('Sophia', 20, true)";
$id = DB::Execute($sql);
// Execute a SQL query with placeholders (better because it stops SQL Injection hacks)$sql = 'DELETE FROM test_table WHERE name = :name AND age = :age AND active = :active';
$values = array('name' => 'Lucas', 'age' => 45, 'active' => true);
$success = DB::Execute($sql, $values);
// Execute the same SQL statement but only in debug mode// In debug mode, the record will not be saved$success = DB::Execute($sql, $values, true);
Optional $placeholders: Associative array placeholders for binding to SQL. array('name' => 'David', 'city' => 'Dallas')
Optional $debug: If set to true, will output results and query information
Optional $fetch_parameters: PDO fetch style record options (i.e. PDO::FETCH_ASSOC, PDO::FETCH_NUM, or PDO::FETCH_BOTH)
If there is an error executing the SQL, false is returned. Otherwise, the data is returned as a multi-dimensional array.
// Execute a SQL query to return an array containing all rows$sql = 'SELECT * FROM test_table';
$rows = DB::Query($sql);
// Show the arrayprint_r($rows);
// Execute a SQL query using placeholders; this will return an array with all rows$sql = 'SELECT id, name, age FROM test_table WHERE active = :active';
$values = array('active' => true);
$rows = DB::Query($sql, $values);
// Execute the same query in debug mode$rows = DB::Query($sql, $values, true);
Executes a SQL statement using PDO and returns a single row as an array.
Executes a SQL statement using PDO exactly the same way DB::Query does except only one row is returned. The syntax is identical. If more than more row would be returned by a given SQL query, using this method, only the first row would be returned. Don't forget to try setting debug mode to true.
Optional $placeholders: Associative array placeholders for binding to SQL. array('name' => 'Emma', 'city' => 'Eureka')
Optional $debug: If set to true, will output results and query information
Optional $fetch_parameters: PDO fetch style record options (i.e. PDO::FETCH_ASSOC, PDO::FETCH_NUM, or PDO::FETCH_BOTH)
If there is an error executing the SQL, false is returned. Otherwise, the data is returned as an array.
// Execute a SQL query to return only one row$sql = 'SELECT * FROM test_table WHERE id = 1';
$row = DB::QueryRow($sql);
// Show the arrayprint_r($row);
Executes a SQL statement using PDO and returns a single value.
Executes a SQL statement using PDO exactly the same way DB::Query does except only one value is returned. This is excellent for getting a single value like a name or a primary key. If more than more row or column would be returned by a given SQL query, using this method, only the first value on the first row would be returned.
Optional $placeholders: Associative array placeholders for binding to SQL. array('name' => 'Fred', 'city' => 'Fargo')
Optional $debug: If set to true, will output results and query information
If there is an error executing the SQL, false is returned. Otherwise, the data is returned as a single value.
// Execute a SQL query to return only one value$sql = 'SELECT name FROM test_table WHERE id = 1';
$value = DB::QueryValue($sql);
// Show the valueecho$value;