PHP Universal Database Library - PUDL

• Source Code (GitHub)
• Releases
• Documentation
• About
• Supported Database Engines
• License
• Usage
  • Getting Started
  • INSERT
  • UPDATE
  • SELECT

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.

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'		=> 'mariadb',
	'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