MySQLi
on steroids. Easy to use, but
powerful and customizable. An essential addition to your development toolbox.
mysqli->query('select * from users')
<?php require "library/dhtmlsql.php"; // Connection data (server_address, name, password, database) $db=DHTMLSQL::connect('localhost','admin','pass','dbtest'); if(!$db->connected()) { exit("Unable to connect to database"); } else { $result=$db->query('select * from users'); while($row=$result->fetch_assoc()) { var_dump($row); } }
$db->import("dbtest.sql");
$db->export('*','dbtest.sql');
PHP 5+ with the mysqli extension activated, MySQL 4.1.22+.
Simply place the library anywhere comfy and include the dhtmlsql.php
file from there.
require "library/dhtmlsql.php"; // Connection data (server_address, name, password, database) $db=DHTMLSQL::connect('localhost','admin','pass','dbtest'); if(!$db->connected()) { exit("Unable to connect to database. Reason: ".$db->connect_error()); } //optionally set your character-set and collation here $db->set_charset('utf8','utf8_general_ci');
You can save this script somewhere and call it config.php
.
How about re-using an already existing SQL connection?
//connecting directly to MySQL Server $mysqli = @new mysqli('localhost','admin','pass','dbtest'); require "library/dhtmlsql.php"; //Re-using already existing connection $db=DHTMLSQL::connect($mysqli); if(!$db->connected()) { exit("Unable to connect to database. Reason: ".$db->connect_error()); } //optionally set your character-set and collation here $db->set_charset('utf8','utf8_general_ci');
You need to require the config.php
from now henceforth. This little sample is to check if your connection is doing fine
require "config.php"; echo $db->version();Result
1.0.0
The connection will be closed automatically when the script ends. To close the connection before, use the following:
$db->close();
select LastName from Employees
1.1.1: fetch all rows and dump using result object
$result=$db->query('select LastName from Employees'); while($row=$result->fetch_assoc()) { var_dump($row); }
1.1.2: fetch all rows and dump using internal methods
$db->query('select LastName from Employees'); while($row=$db->fetch_assoc()) { var_dump($row); }
1.1.3: fetch all rows and dump using fetch_assoc_all
$db->query('select LastName from Employees'); foreach($db->fetch_assoc_all() as $row) { var_dump($row); }
1.1.4: fetch all rows and dump using select method
$db->select('LastName','Employees'); foreach($db->fetch_assoc_all() as $row) { var_dump($row); }Sample result:
array (size=1) 'LastName' => string 'J.' (length=2) array (size=1) 'LastName' => string 'David' (length=5) array (size=1) 'LastName' => string 'Randel' (length=6) array (size=1) 'LastName' => string 'Andy' (length=4)
select FirstName,LastName from Employees where id > 1 and FirstName!='San'
1.2.1: Using Direct SQL
$db->query("select FirstName,LastName from Employees where id > 1 and FirstName!='San'"); while($row=$db->fetch_assoc()) { var_dump($row); }
1.2.2: Using the select method
$db->select('FirstName,LastName','Employees','id > ? and FirstName!=?',array(1,'San')); while($row=$db->fetch_assoc()) { var_dump($row); }Sample result:
array (size=2) 'FirstName' => string 'Owen' (length=4) 'LastName' => string 'David' (length=5) array (size=2) 'FirstName' => string 'Mathew' (length=6) 'LastName' => string 'Randel' (length=6)
select FirstName,LastName from Employees where id > 1 order by FirstName asc
1.3.1: Using Direct SQL
$db->query("select FirstName,LastName from Employees where id > 1 order by FirstName asc"); while($row=$db->fetch_assoc()) { var_dump($row); }
1.3.2: Using the select method
$db->select('FirstName,LastName','Employees','id > ?',array(1),'FirstName asc'); while($row=$db->fetch_assoc()) { var_dump($row); }Sample result:
array (size=2) 'FirstName' => string 'Mathew' (length=6) 'LastName' => string 'Randel' (length=6) array (size=2) 'FirstName' => string 'Owen' (length=4) 'LastName' => string 'David' (length=5) array (size=2) 'FirstName' => string 'San' (length=3) 'LastName' => string 'Andy' (length=4)
select FirstName,LastName from Employees where id > 1 order by FirstName asc limit 1
1.4.1: Using Direct SQL
$db->query("select FirstName,LastName from Employees where id > 1 order by FirstName asc limit 1"); while($row=$db->fetch_assoc()) { var_dump($row); }
1.4.2: Using the select method
$db->select('FirstName,LastName','Employees','id > ?',array(1),'FirstName asc','1'); while($row=$db->fetch_assoc()) { var_dump($row); }Sample result:
array (size=2) 'FirstName' => string 'Mathew' (length=6) 'LastName' => string 'Randel' (length=6)
select FirstName,LastName from Employees where id>1 limit 1
1.5.1: Using Direct SQL
$db->query("select FirstName,LastName from Employees where id>1 limit 1"); $row=$db->fetch_assoc(); var_dump($row);
1.5.2: Using the select method
$db->select('FirstName,LastName','Employees','id > ?',array(1),'','1'); $row=$db->fetch_assoc(); var_dump($row);
1.5.3: Using the dlookup method
$row=$db->dlookup('FirstName,LastName','Employees','id > ?',array(1)); var_dump($row);Sample result:
array (size=2) 'FirstName' => string 'Owen' (length=4) 'LastName' => string 'David' (length=5)
select FirstName from Employees where id>1 limit 1
1.6.1: Using Direct SQL
$db->query("select FirstName from Employees where id>1 limit 1"); $row=$db->fetch_assoc(); if (count($row) == 1) $row=array_pop($row); var_dump($row);
1.6.2: Using the select method
$db->select('FirstName','Employees','id > ?',array(1),'','1'); $row=$db->fetch_assoc(); if (count($row) == 1) $row=array_pop($row); var_dump($row);
1.6.3: Using the dlookup method
$row=$db->dlookup('FirstName','Employees','id > ?',array(1)); var_dump($row);Sample result:
string 'Owen' (length=4)
SELECT f.first,f.last FROM users u left join profiles p on p.id=u.pid WHERE p.id = '2' and p.status>'5'
1.7.1: Using Direct SQL
$db->query("SELECT f.first,f.last FROM users u left join profiles p on p.id=u.pid WHERE p.id = '2' and p.status>'5' ");
1.7.2: Using the select method
$db->select("f.first,f.last","users u left join profiles p on p.id=u.pid","p.id = ? and p.status>?",array(2,5));
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first` varchar(255) NOT NULL, `last` varchar(255) NOT NULL, PRIMARY KEY (`id`) )
2.1.1: Creating the table with query method
$sql = "CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first` varchar(255) NOT NULL, `last` varchar(255) NOT NULL, PRIMARY KEY (`id`) )"; //create table $db->query($sql);
2.1.2: Checking for table existence before creation
$sql = "CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first` varchar(255) NOT NULL, `last` varchar(255) NOT NULL, PRIMARY KEY (`id`) )"; if($db->table_exists('users')) { echo "users already exists"; } else { $db->query($sql); echo "users table created"; }
INSERT INTO `users`(first,last) VALUES ('Stanley','J.')
3.1.1: Insert with query method
$sql = "INSERT INTO `users`(first,last) VALUES ('Stanley','J.')"; $db->query($sql);
3.1.2: Insert and show affected rows with insertion id
$sql = "INSERT INTO `users`(first,last) VALUES ('Stanley','J.')"; $db->query($sql); echo "Affected rows ".$db->affected_rows." and the insertion id is ".$db->insert_id;
3.1.3: Using the Insert method
$db->insert( 'users', array( 'first' => 'Stanley', 'last' => 'J.', ) );Sample output
Affected rows 1 and the insertion id is 3
INSERT INTO `users` (`first`,`last`) VALUES ('Owen','David'),('Mathew','Randel'),('San','Andy')
3.2.1: Insert with query method
$sql = "INSERT INTO `users` (`first`,`last`) VALUES ('Owen','David'),('Mathew','Randel'),('San','Andy')"; $db->query($sql);
3.2.2: Using the Insert_bulk method
$db->insert_bulk( 'users', array('first', 'last'), array( array('Owen', 'David'), array('Mathew', 'Randel'), array('San', 'Andy'), ) );
INSERT IGNORE INTO `mails` (`username`,`email`) VALUES ('support','support@africoders.com')
3.3.1: Insert with query method
$sql = "INSERT IGNORE INTO `mails` (`username`,`email`) VALUES ('support','support@africoders.com')"; $db->query($sql);
3.3.2: Using the insert method
$db->insert( 'mails', array( 'username' => 'support', 'email' => 'support@africoders.com', ),true );
INSERT INTO `mails` (`username`,`email`) VALUES ('support','support@africoders.com') ON DUPLICATE KEY UPDATE `username` = 'tony', `email` = 'tony@africoders.com'
3.4.1: Insert with query method
$sql = "INSERT INTO `mails` (`username`,`email`) VALUES ('support','support@africoders.com') ON DUPLICATE KEY UPDATE `username` = 'tony', `email` = 'tony@africoders.com'"; $db->query($sql);
3.4.2: Using the insert method
$db->insert( 'mails', array( 'username' => 'support', 'email' => 'support@africoders.com', ), array( 'username' => 'tony', 'email' => 'tony@africoders.com', ) );
delete FROM users WHERE id>'16'
4.1.1: Insert with query method
$sql = "delete FROM users WHERE id>'16'"; $db->query($sql);
4.1.2: Using del method
$db->del('users','id>?',array(16));
truncate users
5.1.1: Truncate with query method
$sql = "truncate users "; $db->query($sql);
5.1.2: Using truncate method
$db->truncate('users');
UPDATE `users` SET `first` = 'Stan', `last` = 'Jacobs' WHERE id='2'
6.1.1: Update with query method
$sql = "UPDATE `users` SET `first` = 'Stan', `last` = 'Jacobs' WHERE id='2' "; $db->query($sql);
6.1.2: Using update method
$db->update( 'users', array( 'first' => 'Stan', 'last' => 'Jacobs', ), 'id=?', array(2) );
Replace INTO `users`(id,first,last) VALUES (1,'Mathew','Junior.')
7.1.1: Replace with query method
$sql = "Replace INTO `users`(id,first,last) VALUES (1,'Mathew','Junior.')"; $db->query($sql);
7.1.3: Using the replace method
$db->replace( 'users', array( 'id' => '1', 'first' => 'Mathew', 'last' => 'Junior', ) );
insert INTO `users`(first,last) VALUES ('John','Doe')
8.1.1: Using the prepare and bind_param methods
$stmt = $db->prepare("INSERT INTO users (first, last) VALUES (?, ?)"); $stmt->bind_param("ss", $first, $last); // set parameters and execute $first = "John"; $last = "Doe"; $stmt->execute();
8.1.2: Using the query method
$first = "John"; $last = "Doe"; $db->query("INSERT INTO users (first, last) VALUES (?, ?)",array($first,$last));
Please note that all the library methods that make use of array as input automatically prepare statements internally e.g. select,dlookup,insert,insert_bulk,update and replace.
If you already have your database exported from another source e.g. phpmyadmin, you can import it as shown below.
if($db->import("dbtest.sql")) { echo "Database was imported"; } else { echo "Unable to import database"; }
If you wish to backup your entire database to an sql file:
$db->export('*','dbtest.sql');
Backup a single table to file
$db->export(array('users'),'users.sql');
Backup multiple tables to file
$db->export(array('users','mails','variables'),'dbtables.sql');
Forcing the browser to prompt for download (no backup file)
$db->export('*'); //ommit the output file
You can retrieve the actual SQL statement used in previous query after execution:
echo $db->fetch_sql();
The preview mode will render the actual SQL statements without execution:
$db->preview(true); $db->select("f.first,f.last","users u left join profiles p on p.id=u.pid","p.id = ? and p.status>?",array(2,5));
If you have a query with a lot of results, you can break it down using the following:
$db->pquery('select * from country',5,'page'); while($row=$db->fetch_assoc()) {var_dump($row);} if($db->num_rows>0) {echo $db->show_navigation();}
DHTMLSQL can re-use any existing MySQLi connection. To implement this library in developing your plugins, you need to look at the Advanced Start section and figure out how to get the MySQLi handle of your framework.
Please do not forget that there is more to this library than what you have read. You might want to read the Full API documentation to learn more about this library.
Issues and feature requests are very welcome.
Inspired by the general open-source community with special thanks to Github for making the life of programmers more fun.
Check out an awesome developer portal created by yours truly.
Anthony Ogundipe a.k.a. dhtml
Special thanks to Marcellinus Okeke and Oyebanji Jacob Mayowa for their contributions to this library.
DHTMLSQL is licensed under the MIT License.
Leave a comment
comments powered by Disqus