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->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->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->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->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->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->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->db->searchGet('users',[
	['lastname','=','Smith']
]);
print_r($user);

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

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

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

//OUTPUTS SUCCESS BOOLEAN 1 OR 0
1

Query Examples

Search Where

            
$user = $app->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->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->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->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
				)
		)
)
*/