PHP Universal Database Library - PUDL
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