PHP Universal Database Library - connects to and generates queries for SQL Servers
Clone or download
Permalink
Failed to load latest commit information.
clone Can now directly include one of the SQL drivers without PUDL core first Oct 1, 2018
file Adding in data type check for pudlExportExcel Nov 8, 2018
mssql Added a bunch of method comments Nov 14, 2018
mysql Added a bunch of method comments Nov 14, 2018
null Updating path to pudlArrayResult and fixing issue with it. Nov 8, 2018
odbc Added a bunch of method comments Nov 14, 2018
pdo Added a bunch of method comments Nov 14, 2018
pgsql Added a bunch of method comments Nov 14, 2018
sql updating comment Nov 14, 2018
sqlite Added a bunch of method comments Nov 14, 2018
test Added a bunch of method comments Nov 14, 2018
traits PUDL Engine type is now entirely case insensitive Nov 14, 2018
.gitignore Ignoring text files Aug 19, 2014
.travis.yml Testing to see if HHVM 3.27 is enabled on TravisCI Aug 21, 2018
LICENSE This is 2018, isnt it..? Oct 17, 2018
README.md Organizing file locations Nov 7, 2018
_config.yml Set theme jekyll-theme-leap-day May 18, 2018
composer.json Update composer.json Sep 20, 2018
pudl.php PUDL Engine type is now entirely case insensitive Nov 14, 2018
pudlCollection.php Callable type hints added Oct 23, 2018
pudlConstants.php minor whitespace cleanup Oct 23, 2018
pudlData.php Adding jsonserializable interface to pudlData interface Oct 6, 2018
pudlException.php Adding class type hinting for PUDL on available method parameters Oct 19, 2018
pudlHelpers.php Moved pudlRaw to its own file Nov 12, 2018
pudlInclude.inc.php Moved pudlRaw to its own file Nov 12, 2018
pudlInterfaces.php Adding class type hinting for PUDL on available method parameters Oct 19, 2018
pudlList.php Switching ownership checker. It'll be provided by Altaform now. Sep 5, 2018
pudlObject.php Callable type hints added Oct 23, 2018
pudlOrm.php Correcting issue where selex sections were not properly being merged Nov 9, 2018
pudlResult.php Adding class type hinting for PUDL on available method parameters Oct 19, 2018
pudlSession.php Should actually fix the session caching of empty data issue now! Oct 23, 2018

README.md

PHP Universal Database Library - PUDL

Build Status

About

The primary function of this library is to provide a common interface for interacting with several different database engines without worrying about implementation specific syntax. PUDL takes basic PHP functions and data types, and then converts these over to engine specific SQL queries automatically. This is accomplished using a simplistic procedural style of programming rather than a more complex ORM style of programming. No custom classes outside of PUDL are required to take full advantage of this library.

The test folder contains a series of examples of PUDL function calls and their resulting SQL query strings. This is a good place to get an idea of what the PUDL library is designed for beyond what is documented on this page.

Supported Database Engines

Engine Class Support Info
MySQL pudlMySqli
pudlMySql
Officially Supported Both modern php-mysqli and legacy php-mysql available
MariaDB pudlMySqli
pudlMySql
Officially Supported Same as MySQL
Percona pudlMySqli
pudlMySql
Officially Supported Same as MySQL
Galera Clustering pudlGalera Officially Supported Uses pudlMySqli with additional cluster features
NULL pudlNull Officially Supported Essentially /dev/null the database
Microsoft SQL pudlSqlSrv
pudlMsSql
Experimental Support Both modern php-sqlsrv and legacy php-mssql available
SQLite pudlSqlite Experimental Support Uses the php-sqlite3 driver
ODBC pudlOdbc Experimental Support Uses the php-odbc driver
Actian PSQL pudlOdbc Experimental Support Supported through ODBC
PostgreSQL pudlPgSql Experimental Support Uses the php-pgsql driver
PDO pudlPdo Experimental Support Uses the php-pdo driver
Shell pudlShell Experimental Hack Custom JSON proxy interface over shell commands
Web pudlWeb Experimental Hack Custom JSON proxy interface over HTTP(s)
Clone pudlClone Experimental Hack Cloned interface forwarding calls to another PUDL instance

License

This software library is licensed under the BSD 2-clause license, and may be freely used in any project (commercial, freelance, hobby, or otherwise) which is compatible with this license. See LICENSE for more details.

Usage

Getting Started


First, create an instance of PUDL for your specific database type

require_once('pudl/pudl.php');

$db = pudl::instance([
	'type'     => 'MySqli',
	'server'   => 'localhost',
	'database' => 'DatabaseName',
	'username' => 'AwesomeGuy9001',
	'password' => 'SuperDuperSecretSauce',
]);

INSERT


Let's start by showing the most intuitive conversion from PHP to SQL.

$db->insert('movies', [
	'id'		=> 1,
	'title'		=> 'Star Wars',
	'subtitle'	=> 'The Force Awakens',
	'director'	=> 'J.J. Abrams',
	'runtime'	=> 136,
]);

This will result in the following query being generated and executed:

INSERT INTO `movies` (`id`, `title`, `subtitle`, `director`, `runtime`) VALUES (1, 'Star Wars', 'The Force Awakens', 'J.J. Abrams', 136)

Inserting data into the database uses a normal and intuitive PHP associative array as a key-value pair. PUDL separates out theses keys and values automatically to form the column and value pair to INSERT into the database.

UPDATE


UPDATE queries are a similar syntax. Let's say we need to update the title because we initially put it in wrong. You can use the following to do so.

$db->update('movies', [
	'title'	=> 'Star Wars: Episode VII',
], [
	'id'	=> 1,
]);

Resulting SQL:

UPDATE `movies` SET `title`='Star Wars: Episode VII' WHERE (`id`=1)

With this, we use the same key and value pair with UPDATE as we do with INSERT. Additionally, we also use the same key and value pair to generate our WHERE clause.

SELECT


As with the UPDATE query, anything in PUDL that takes a WHERE clause can take a key and value pair. Here are some examples of SELECT statements.

PHP:

$data = $db->rows('movies');
var_export($data);

Generated SQL:

SELECT * FROM `movies`

Output:

array (
	0 =>
	array (
		'id' => 1,
		'title' => 'Star Wars: Episode VII',
		'subtitle' => 'The Force Awakens',
		'director' => 'J.J. Abrams',
		'runtime' => 136,
	),
)

We only have 1 item in the movies table right now, so only one row is returned. The rows() function returns all rows that match a particular WHERE clause. In this example above, the optional WHERE clause is not specified. Here is an example with it:

PHP:

$data = $db->rows('movies', ['director'=>'J.J. Abrams']);
var_export($data);

Generated SQL:

SELECT * FROM `movies` WHERE (`director`='J.J. Abrams')

Output:

array (
	0 =>
	array (
		'id' => 1,
		'title' => 'Star Wars: Episode VII',
		'subtitle' => 'The Force Awakens',
		'director' => 'J.J. Abrams',
		'runtime' => 136,
	),
)

If we only want to get a single row from the database, we can use row() instead of rows(). This will return a single dimensional array instead of a two-dimensional array. This function also forces a LIMIT of 1.

PHP:

$data = $db->row('movies', ['director'=>'J.J. Abrams']);
var_export($data);

Generated SQL:

SELECT * FROM `movies` WHERE (`director`='J.J. Abrams') LIMIT 1

Output:

array (
	'id' => 1,
	'title' => 'Star Wars: Episode VII',
	'subtitle' => 'The Force Awakens',
	'director' => 'J.J. Abrams',
	'runtime' => 136,
)

Often times you'll need more than one item in your WHERE clause. This is easily done with the automatic AND clauses.

PHP:

$data = $db->row('movies', [
	'director'	=> 'J.J. Abrams',
	'subtitle'	=> 'The Force Awakens',
]);

Generated SQL:

SELECT * FROM `movies` WHERE (`director`='J.J. Abrams' AND `subtitle`='The Force Awakens') LIMIT 1

Nesting an array inside of another array creates an OR clause

PHP:

$data = $db->row('movies', [
	'director'		=> 'J.J. Abrams',
	[
		['title'	=> 'Star Wars'],
		['title'	=> 'Star Wars: Episode VII'],
		['title'	=> 'Episode VII'],
	]
]);

Generated SQL:

SELECT * FROM `movies` WHERE (`director`='J.J. Abrams' AND ((`title`='Star Wars') OR (`title`='Star Wars: Episode VII') OR (`title`='Episode VII'))) LIMIT 1

General API Guide

Method Parameters


There are a few method parameter variable $names that reoccur frequently within PUDL. Except for a few cases specifically noted, wherever you see these $names, their values are of MIXED data type, each doing something different depending on the data type passed into the method. This can be thought of similarly to overloaded methods in C++, but with a significantly more dynamic nature. Listed here are the most common parameter names and what each data type represents.

$value

This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/parameters/value.md

$columns

This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/parameters/columns.md

$tables

This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/parameters/tables.md

$clause

This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/parameters/clause.md

$having

This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/parameters/having.md

$on

This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/parameters/on.md

SELECT


This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/pudl/select.md

pudlResult


This section has been migrated to: https://github.com/darkain/pudl-docs/blob/master/pudl/pudlResult.md