pajamaSQL is a PHP SQL database layer. It supports MySQL, PostgreSQL and SQLite.
Documentation
Requirements
Source Code
Unit Tests
Examples
Installation
Overview
MySQL
PostgreSQL
SQLite
Models
Error Handling
License
This README is currently the only documentation.
PHP 5.6.0+ or 7+; Composer; and at least one of the follow PHP vendor specific database extensions: Mysqli, PostgreSQL and/or SQLite3. The unit tests need PHP 7.2.0+.
This project and its source code are available on GitHub.
There are unit tests in the
tests directory.
You will need to edit the database credentials at the top of
MysqlTest.php and PgsqlTest.php.
All the example code in this README is in the
examples
directory. The examples directory also contains additional examples that are not
in this README.
You need to run composer install in the examples directory before running
any of the examples.
Install using composer:
{
"require": {
"pajamasql/pajamasql": "1.0.0",
}
}pajamaSQL wraps Mysqli,
PostgreSQL and
SQLite3
prepared statements. These are the available methods: exec(), query(),
rquery(), prepare(), bexec(), bquery(), brquery(), conn(),
error() and esc(). Basic usage looks like this:
$db->exec(
'insert into foo values(?), (?)',
'bar',
'baz');
$data = $db->query(
'select * from foo where rowid > ?',
1);There is also a model layer. A basic model looks like this:
<?php
namespace purple;
class FooModel extends \pjsql\DatabaseAdapter {
public function createFoo($name) {
$this->exec(
'insert into foo values(?)',
$name);
}
}Model instantiation and usage:
<?php
require 'vendor/autoload.php';
$foo_model = purple\ModelFactory::get('purple\FooModel');
$foo_model->createFoo('fred');Use Mysql() to connect to a database:
<?php
require 'vendor/autoload.php';
$db = new pjsql\Mysql(
'host',
'username',
'password',
'database');
echo $db->conn()->stat();conn() above is a mysqli object.
Execute queries with exec() and query():
$db->exec('create table tanimal(
animal_id int auto_increment primary key,
name varchar(32))');
$db->exec('insert into tanimal(name) values("tiger")');
$data = $db->query('select * from tanimal');
print_r($data);exec() and query() can take query parameters:
$db->exec(
'insert into tanimal(name) values(?), (?)',
'tiger',
'eagle');
$data = $db->query(
'select * from tanimal where animal_id = ?',
2);
print_r($data);query() returns an array with all the data.
If you want a
mysqli_result
object instead then use rquery():
$result = $db->rquery('select * from tanimal');
while($row = $result->fetch_object()) {
print_r($row);
}Use prepare(), bexec() and bquery() to run a query more than once:
$stmt = $db->prepare('insert into tanimal values(null, ?)');
$db->bexec($stmt, 'bird');
$db->bexec($stmt, 'frog');
$db->bexec($stmt, 'cat');
$ids = [1, 2, 3, 4];
$stmt = $db->prepare('select name from tanimal where animal_id = ?');
foreach($ids as $id) {
$data = $db->bquery($stmt, $id);
print_r($data);
}bquery() returns an array with all the data.
If you want a
mysqli_result
object instead then use brquery():
$floors = [1, 5];
$stmt = $db->prepare('select name from tanimal where animal_id >= ?');
foreach($floors as $f) {
$result = $db->brquery($stmt, $f);
while($row = $result->fetch_assoc()) {
print_r($row);
}
}With all the exec and query methods you can put the parameter values into an array and use a types string as the third argument in order to specify the query parameter types:
$db->exec(
'insert into tanimal(name) values(?), (?), (?)',
['lizard', 'cow', 'monkey'],
'sss');
$data = $db->query(
'select * from tanimal where animal_id < ?',
[3],
'i');
print_r($data);The parameter types string above works the same way as the
mysqli_stmt::bind_param
$types argument.
Use Pgsql() to connect to a database:
<?php
require 'vendor/autoload.php';
$db = new pjsql\Pgsql('dbname=foo user=bar password=baz');
echo pg_host($db->conn());conn() above is a PostgreSQL connection resource that is returned
by pg_connect().
Execute queries with exec() and query():
$db->exec('create table tcolor(
color_id serial primary key,
name varchar(40))');
$db->exec("insert into tcolor(name) values('green')");
$data = $db->query('select * from tcolor');
print_r($data);exec() and query() can take query parameters:
$db->exec(
'insert into tcolor(name) values($1), ($2)',
'gold',
'silver');
$data = $db->query(
'select * from tcolor where color_id <> $1',
1);
print_r($data);query() returns an array with all the data.
If you want a query result resource instead then use rquery():
$result = $db->rquery('select * from tcolor');
while($row = pg_fetch_assoc($result)) {
print_r($row);
}Use prepare(), bexec() and bquery() to run a query more than once:
$stmt_name = 'insert1';
$db->prepare('insert into tcolor values(default, $1)', $stmt_name);
$db->bexec($stmt_name, 'pink');
$db->bexec($stmt_name, 'purple');
$db->bexec($stmt_name, 'black');
$ids = [1, 2, 3, 4];
$stmt_name = 'select1';
$db->prepare('select name from tcolor where color_id = $1', $stmt_name);
foreach($ids as $id) {
$data = $db->bquery($stmt_name, $id);
var_dump($data);
}bquery() returns an array with all the data.
If you want a query result resource instead then use brquery():
$floors = [4, 3];
$stmt_name = 'select1';
$db->prepare('select * from tcolor where color_id >= $1', $stmt_name);
foreach($floors as $f) {
$result = $db->brquery($stmt_name, $f);
while($row = pg_fetch_object($result)) {
print_r($row);
}
}Use Sqlite() to connect to a database:
<?php
require 'vendor/autoload.php';
$db = new pjsql\Sqlite('mydb.db');
echo get_class($db->conn());conn() above is an SQLite3 object.
Execute queries with exec() and query():
$db->exec('create table tshape(name text)');
$db->exec('insert into tshape values("circle")');
$data = $db->query('select * from tshape');
print_r($data);exec() and query() can take query parameters:
$db->exec(
'insert into tshape values(?), (?)',
'triangle',
'square');
$data = $db->query(
'select * from tshape where rowid > ?',
1);
print_r($data);query() returns an array with all the data.
If you want an SQLite3Result
object instead then use rquery():
$result = $db->rquery('select * from tshape');
while($row = $result->fetchArray(SQLITE3_ASSOC)) {
print_r($row);
}Use prepare(), bexec() and bquery() to run a query more than once:
$stmt = $db->prepare('insert into tshape values(?)');
$db->bexec($stmt, 'octagon');
$db->bexec($stmt, 'oval');
$db->bexec($stmt, 'circle');
$ids = [1, 2, 3, 4];
$stmt = $db->prepare('select name from tshape where rowid = ?');
foreach($ids as $id) {
$data = $db->bquery($stmt, $id);
print_r($data);
}bquery() returns an array with all the data.
If you want an SQLite3Result
object instead then use brquery():
$stmt = $db->prepare('select * from tshape where rowid >= ?');
$floors = [4, 3, 4];
foreach($floors as $f) {
$result = $db->brquery($stmt, $f);
while($row = $result->fetchArray()) {
print_r($row);
}
}With all the exec and query methods you can put the parameter values into an array and use a types string as the third argument in order to specify the query parameter types:
$db->exec(
'insert into tshape values(?), (?)',
['square', 'circle'],
'tt');
$data = $db->query(
'select * from tshape where rowid < ?',
[500],
'i');
print_r($data);The characters in the above types string use the following mapping:
i = SQLITE3_INTEGER
f = SQLITE3_FLOAT
t = SQLITE3_TEXT
b = SQLITE3_BLOB
n = SQLITE3_NULL
Set up a model factory:
<?php
namespace purple;
class ModelFactory extends \pjsql\AdapterFactory {
protected static function databaseHandle() {
return new \pjsql\Mysql(
'host',
'username',
'password',
'database');
}
}If you want to use PostgreSQL or SQLite,
then use Pgsql() or Sqlite() instead of the above Mysql().
Create a model:
<?php
namespace purple;
class DogModel extends \pjsql\DatabaseAdapter {
public function install() {
$this->exec('create table tdog(
dog_id int auto_increment primary key,
name varchar(50))');
}
public function createDog($name) {
$this->exec(
'insert into tdog(name) values(?)',
$name);
}
public function getDogs() {
return $this->query('select * from tdog');
}
}Get a model and call its methods:
<?php
require 'vendor/autoload.php';
$dog_model = purple\ModelFactory::get('purple\DogModel');
$dog_model->install();
$dog_model->createDog('spike');
$dog_model->createDog('buster');
$data = $dog_model->getDogs();
print_r($data);With the exec, query and prepare methods you can handle errors with an exception handler:
set_exception_handler(function($e) {
if($e instanceof pjsql\DatabaseException) {
die($e->getMessage());
}
else {
throw $e;
}
});If you use conn(), then call error() when
appropriate(MySQL example):
if($status = $db->conn()->stat()) {
echo $status;
}
else {
$db->error();
}error() above will thrown an exception that will be
caught by the above exception handler.