Database

The PAX Database Class is a PHP PDO Method implementation of database querying. By default all methods will separate values from the raw MYSQL string submitted for standard security protocol.

The foundational strategy of the PAX Database Class is to promote minimalism and JSON friendly conversion. As such array structures are implemented for language speed and immediate ease of interaction across all languages.

Basic Methods

get( $table :string , $id :array , $select :array );

            
$user = $app->get("db")->get('users',['id'=>1]);
print_r($user);

/*
OUTPUTS
Array
(
	[firstname]=>John
	[lastname]=>Smith
	[age]=>28
	[id]=>1
)
*/

save( $table :string , $data :array );

            
$user = $app->get("db")->save('users',[
	'firstname'=>'Daniel',
	'lastname'=>'Johnson',
	'age'=>21
]);
print_r($user);

//OUTPUTS ASSIGNED ID
2

update( $table :string , $id :integer , $data :array );

            
$user = $app->get("db")->update('users',2,[
	'firstname'=>'daniel',
	'lastname'=>'johnson',
	'age'=>17
]);
print_r($user);

//OUTPUTS SUCCESS BOOLEAN 1 OR 0
1

delete( $table :string , $id :integer );

            
$user = $app->get("db")->delete('users',2);
print_r($user);

//OUTPUTS SUCCESS BOOLEAN 1 OR 0
1

Advanced Methods

Parameters

Name	Type	Default	Description
$table	String		Name of Table Querying
$query	Array		Associative array used to define the query's where, join, select, and group clauses
$max	Integer	10	Total amount of results to return
$start	Integer	0	Pagination position to start.
$sort	String	'asc'	Sorting of Results
$order	String	'id'	Field to sort by

Query Details

Name	Description	Examples
select	List of field values to select	`'select'=>['firstname','lastname'] //SELECT firstname, lastname`
and	Array of Dependent Where Clause elements	`'and'=>[ ['age','>',29], ['firstname','!=','Daniel'] ] //WHERE age > 29 AND firstname != Daniel`
or	Array of Independent Where Clause elements	`'or'=>[ ['age','=',28], ['age','<',10], ] //WHERE age = 28 OR age < 10`
join	Array of joins in query	`'join'=>[ ['left join','city','user.city_id','city.id'] ] //LEFT JOIN city ON user.city_id = city.id`
group	Array of fields to group by	`'group'=>['firstname','age'] //GROUP BY firstname,age`

Search Return Object

Name	Type	Description
count	Integer	The total count of results independent of pagination
hits	Array	Results dependent on pagination

            
Array
(
	[count] => 2
	[hits] => Array
		(
			[0] => Array
				(
					[firstname] => John
					[lastname] => Smith
					[age]=>28
				)

			[1] => Array
				(
					[firstname] => Daniel
					[lastname] => Johnson
					[age]=>17
				)
		)
)

count( $table :string , $query :array );

            
$user = $app->get("db")->count('users');
print_r($user);

//OUTPUTS INTEGER COUNT
1

search( $table :string , $query :array , $max :integer ,
$start :integer , $sort :string , $order :string );

            
$user = $app->get("db")->search('users');
print_r($user);

/*OUTPUT
Array
(
	[count] => 2
	[hits] => Array
		(
			[0] => Array
				(
					[firstname] => John
					[lastname] => Smith
					[age]=>28
				)

			[1] => Array
				(
					[firstname] => Daniel
					[lastname] => Johnson
					[age]=>17
				)
		)
)
*/

searchGet( $table :string , $query :array );

            
$user = $app->get("db")->searchGet('users',[
	['lastname','=','Smith']
]);
print_r($user);

/*OUTPUT
(
	[firstname] => John
	[lastname] => Smith
	[age]=>28
)
*/

searchUpdate( $table :string , $query :array , $data :array );

            
$user = $app->get("db")->searchUpdate('users',[
		['lastname','=','Smith']
	],
	['age'=>37]
);
print_r($user);

//OUTPUTS SUCCESS BOOLEAN 1 OR 0
1

Query Examples

Search Where

            
$user = $app->get("db")->search('users',[
	'and'=>[
		['age','>',20],
		['firstname','!=','Daniel']
	]
]);
print_r($user);

//SELECT * FROM users WHERE age > 20 AND firstname != Daniel
/*OUTPUT
Array
(
	[count] => 1
	[hits] => Array
		(
			[0] => Array
				(
					[firstname] => John
					[lastname] => Smith
					[age]=>28
				)
		)
)
*/

Search Select

            
$user = $app->get("db")->search('users',['select'=>['lastname']]);
print_r($user);

//SELECT lastname FROM users
/*OUTPUT
Array
(
	[count] => 2
	[hits] => Array
		(
			[0] => Array
				(
					[lastname] => Smith
				)
			[1] => Array
				(
					[lastname] => Johnson
				)
		)
)
*/

Search Pagination & Sorting

            
$user = $app->get("db")->search('users',[],2,1,'asc','firstname');
print_r($user);

//SELECT * FROM users LIMIT 2 OFFSET 1
/*OUTPUT
Array
(
	[count] => 4
	[hits] => Array
		(
			[0] => Array
				(
					[firstname] => Paul
					[lastname] => Rudd
					[age]=>24
				)
			[1] => Array
				(
					[firstname] => Zach
					[lastname] => Galifianakis
					[age]=>32
				)
		)
)
*/

Search Advanced

*Note joins convert select fields to $table_$field nomenclature*

            
$user = $app->get("db")->search('users',[
	'select'=>['users.firstname','users.lastname','city.name'],
	'join'=>[
		['left join','city','users.city_id','city.id']
	],
	'or'=>[
		['and'=>[
			['users.age','>',5],
			['users.age','<',15]
		]],
		['and'=>[
			['users.age','>',25],
			['users.age','<',35]
		]]
	]
],2,0,'asc','users.firstname');
print_r($user);

/*MYSQL
SELECT users.firstname AS users_firstname,
users.lastname AS users_lastname, city.name AS city_name
FROM users LEFT JOIN city ON users.city_id = city.id
WHERE (users.age > 5 AND users.age < 15)
OR (users.age > 25 AND users.age < 35) LIMIT 2
*/

/*OUTPUT
Array
(
	[count] => 4
	[hits] => Array
		(
			[0] => Array
				(
					[users_firstname] => Paul
					[users_lastname] => Rudd
					[city_name] => Hollywood
				)
			[1] => Array
				(
					[users_firstname] => Zach
					[users_lastname] => Galifianakis
					[city_name] => Charlotte
				)
		)
)
*/

Seeding

*Warning: This will erase the database and reset the scheme and inject new data.

            
$data = [
	"DB_NAME"=>[
		"users"=>[
			[
				"id"=>1,
				"name"=>"John Doe"
			]
		]
	]
];

$map = [
	"DB_NAME"=>[
		"users"=>[
			"id"=>["type"=>"key"],
			"email"=>["type"=>"string"]
		]
	]
];

$app->get("db")->seed("DB_NAME", $data, $map);