entomb / obj_mysql
MySQL abstraction layer
Installs: 210
Dependents: 0
Suggesters: 0
Security: 0
Stars: 28
Watchers: 8
Forks: 25
Open Issues: 4
Requires
- php: >=5.2.0
This package is not auto-updated.
Last update: 2024-01-09 00:20:49 UTC
README
OBJ MySQL is a simple MySQL Abstraction Layer for PHP>5.2 that provides a simple and secure interaction with your database using mysqli_* functions at its core.
OBJ-MySQL is perfect for small scale applications such as cron jobs, facebook canvas campaigns or micro frameworks or sites.
This project is under construction, any feedback would be appreciated
Author: Jonathan Tavares
checkout the changelog for info on the latest changes
##Get OBJ_MySQL You can download it from here, or require it using composer.
{ "require": { "entomb/obj_mysql": "dev-master" } }
Or you can require it by cloning this repo
$ git clone https://github.com/entomb/OBJ-MySQL.git
If you are already using GIT on you project you can add it as a submodule
$ git submodule add https://github.com/entomb/OBJ-MySQL.git libs/db
##Starting the driver To start the db driver you must include the main class file and pass the '$config' array described bellow. you can have multiple instances of the Class each one with its own $config (one for Reads and one for Writes for example).
//include de main OBJ_mysql class file include("bin/OBJ_mysql.php"); //configuration array $config = array(); $config["hostname"] = "YOUR_HOST"; $config["database"] = "YOUR_DATABASE_NAME"; $config["username"] = "USER_NAME"; $config["password"] = "PASSWORD"; //other configurations $config["port"] = "PORT"; //defaults to 3306 $config["charset"] = "CHARSET"; //defaults to UTF-8 $config["exit_on_error"] = "TRUE|FALSE"; //defaults to true $config["allow_logging"] = "TRUE|FALSE"; //defaults to true //class instantiation $db = new OBJ_mysql($config);
##Using OBJ_MySQL
there are numerous ways of using this library, here are some examples of the most common methods
###Selecting and retrieving data from a table
$Result = $db->query("SELECT * FROM users"); $Users = $Result->fetchALL();
###Inserting data on a table
To manipulate tables you have the most important methods wrapped, they all work the same way: parsing arrays of key/value pairs and forming a safe query
the methods are:
$db->insert( String $Table, Array $Data); //generates an INSERT query $db->replace(String $Table, Array $Data); //generates an INSERT OR UPDATE query $db->update( String $Table, Array $Data, Array $Where); //generates an UPDATE query $db->delete( String $Table, Array $Where); //generates a DELETE query
All methods will return the resulting mysqli_insert_id()
or true/false depending on context.
The correct approach if to always check if they executed as success is always returned
$ok = $db->delete('users', array( 'user_id' => 9 ) ); if($ok){ echo "user deleted!"; }else{ echo "can't delete user!"; }
note: All parameter values are sanitized before execution, you don't have to escape values beforehand.
$new_user_id = $db->insert('users', array( 'name' => "jothn", 'email' => "johnsmith@email.com", 'group' => 1, 'active' => true, ) ); if($new_user_id){ echo "new user inserted with the id $new_user_id"; }
###binding parameters on queries
Binding parameters is a good way of preventing mysql injections as the parameters are sanitized before execution.
$Result = $db->query("SELECT * FROM users WHERE id_user = ? AND active = ? LIMIT 1",array(11,1)); if($Result){ $User = $Result->fetchArray(); print_r($User); }else{ echo "user not found"; }
###Using the OBJ_mysql_result Class
After executing a SELECT
query you receive a OBJ_mysql_result
object that will help you manipulate the resultant data.
There are different ways of accessing this data, check the examples bellow:
####Fetching all data
$Result = $db->query("SELECT * FROM users"); $AllUsers = $Result->fetchAll();
Fetching all data works as Object
or Array
the fetchAll()
method will return the default based on the $_default_result_type
config.
Other methods are:
$Row = $Result->fetch(); // Fetch a single result row as defined by the config (Array or Object) $Row = $Result->fetchArray(); // Fetch a single result row as Array $Row = $Result->fetchObject(); // Fetch a single result row as Object $Data = $Result->fetchAll(); // Fetch all result data as defined by the config (Array or Object) $Data = $Result->fetchAllArray(); // Fetch all result data as Array $Data = $Result->fetchAllObject(); // Fetch all result data as Object $Data = $Result->fetchColumn(String $Column); // Fetch a single column in a 1 dimension Array $Data = $Result->fetchArrayPair(String $key, String $Value); // Fetch data as a key/value pair Array.
####Aliases
$db->get() // Alias for $db->fetch(); $db->getAll() // Alias for $db->fetchAll(); $db->getObject() // Alias for $db->fetchAllObject(); $db->getArray() // Alias for $db->fetchAllArray(); $db->getColumn($key) // Alias for $db->fetchColumn($key);
####Iterations To iterate a resultset, you can use any fetch() method listed above
$Result = $db->query("SELECT * FROM users"); //using while while( $row = $Result->fetch() ){ echo $row->name; echo $row->email; } //using foreach foreach( $Result->fetchAll() as $row ){ echo $row->name; echo $row->email; }
####Logging and Errors
Showing the query log. the log comes with the SQL executed, the execution time and the result row count (if any)
print_r($db->log());
To debug mysql errors:
Use $db->errors()
to fetch all errors (returns false if no errors) or $db->lastError()
for information on the last error.
if( $db->errors() ){ echo $db->lastError(); }