DHTMLSQL

Zero-setup replacement for MySQLi, and MySQL functions.

Introduction

What is it?

An advanced, compact and lightweight MySQL database wrapper library, built around PHP's mysqli extension.

In other words, it is MySQLi on steroids. Easy to use, but powerful and customizable. An essential addition to your development toolbox.

What's so great about it?

Or: why would you want to replace your trusty mysqli->query('select * from users')
Dead-simple to install and use.
  • Virtually no installation and no dependencies. 
    <?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);
	}
}
  • Or you can simply use Composer to handle installation and keeping up to date.
You Can Restore Your Database From SQL in just a single statement 
  • $db->import("dbtest.sql");
You can backup your entire database to an SQL file with just one statement 
  • $db->export('*','dbtest.sql');

Installation and config

Requirements

PHP 5+ with the mysqli extension activated, MySQL 4.1.22+.

Quick start

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.

Advanced Start

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');

Full API Documentation

Checking Library Version

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

Close the connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

$db->close();

Simple usage

1. Select Statement


1.1 Running Simple Select Statement

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)

1.2 Narrowing Down Results With Search

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)

1.3 Ordering Results

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)

1.4 Limiting Results

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)

1.5 Fetching A Single Row As An Array

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)

1.6 Fetching A Single Item As A String

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)

1.7 Joining Tables

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));

2. Create Table Statement


2.1 Table creation statement

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";
}

3. Insert Statement


3.1 Insert data into table

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

3.2 Insert multiple data into table

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'),
	)
);

3.3 Insert and Ignore Duplicates

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
);

3.4 Insert or update duplicates

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',
	)

);

4. Delete Statement


4.1 Delete data from table

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));

5. Truncate Statement


5.1 Truncate all data in a table

truncate users

5.1.1: Truncate with query method

$sql = "truncate users ";
$db->query($sql);

5.1.2: Using truncate method

$db->truncate('users');

6. Update Statement


6.1 Update an existing record in a table

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)
);

7.1 Replace data in a table

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',
	)
);

8.1 Using Prepared Statements

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.

Advanced usage

Restore Database From SQL File

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";	
}

Backup Database To SQL File

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

Displaying Last SQL Statement

You can retrieve the actual SQL statement used in previous query after execution:

echo $db->fetch_sql();

Forcing SQL Preview Mode

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));

Using The Internal Pagination

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();}

Notes

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.

Credits

Inspired by the general open-source community with special thanks to Github for making the life of programmers more fun.

Shoutouts

Check out an awesome developer portal created by yours truly.

Author

Anthony Ogundipe a.k.a. dhtml

Special thanks to Marcellinus Okeke and Oyebanji Jacob Mayowa for their contributions to this library.

License

DHTMLSQL is licensed under the MIT License.

Leave a comment

comments powered by Disqus