PhARoS Ain't Rocket Science

PhARoS Ain’t a Rocket Science and after reading this simple document you will say the same.

Really simple and minimal PhARoS provides a minimal set of user routines to let you generate your standard SQL queries using modified version of active record pattern. Also taking away all the boiler level strings from the programmer making code simpler.

Since PhARoS is not dependent upon any of the databases classes at all, you can yourself choose how to query database either by directly using the functions (like mysql_query) or choose any of the databases abstraction libraries (adodblite or adodb itself).

Also since it is based on principle of minimalism, unlike any of the active record classes (Propel or Doctrine) it is plug n play. So no tight integration or complex XML configurations. All you will be doing is feeling the complete power to generate the queries with decent readable code. Unlike some libraries it only requires PHP 4+ version and that’s it. Since there is not much bloated code still you will be able to use CRUD like methods to generate queries for you. They key routines are selectQuery, insertQuery, updateQuery, deleteQuery (see below and you will understand).

The document provides everything you need to get started and making your life easier with PhARoS. You can create a pharos object and start using it straight away .e.g.

require_once(‘PhARoS.php’);

$ar = new PhARoS();

You can use following set of routines in combination to generate queries. Each section will describe you example usage and a partial or full output query (partial ones indicate by …)

Selecting columns

$ar->select(‘col1’,’col2’);

$ar->select(array(‘col1’,’col2’));

Generated output:

SELECT col1,col2 …

As clear from above example usually a select can take any number of string arguments specifying table column names or an array containing those names. The first statement shows how I can specify col1 and col2 from a table as arguments however the second statement demonstrates the array usage.
You can pass any number of arguments (either string or array) to this function

From Table

$ar->from(‘entries’);

Generated output:

… FROM entries …

Sets the target table (entries in above example), if it is called again from table name will be overwritten or changed.

Where conditions

There are as many flavors available as you can think of for the where clause, since it is always the most used. Let’s have a glimpse at them. You can either pass a single string specifying complete condition. Like this:

$ar->where(“name = ‘zohaib’ AND age > 20”);

Generated output:

… WHERE name=’zohaib’ AND age>20 …

In above method user has complete control over string and will be used as it is (.i.e. no quotes are added to query; hence user is responsible for handling SQL injection)

The second way is to specify the column and value pair, this method will automatically add slashes to the value objects as well hence recommended:

$ar->where(‘name’, $_POST[‘name’]);

$ar->where(‘age >’,20);

Generated output:

… WHERE name=’zohaib’ AND age>’20’ …

When you call it where twice it automatically concatenated the next condition with AND operator. You may change this operator by specifying it to the PhARoS like this:

$ar->where(‘name’, $_POST[‘name’]);

$ar->where(‘age >’,20,’OR’);

$ar->where(‘pay’,40,’AND’);

Generated output:

… WHERE name=’zohaib’ OR age>’20’ AND pay=’40’…

Simple yet powerful! PhARoS can detect the passed value type and generate the specific operator accordingly, for example if you pass an array as value object it will automatically generate the IN operator .i.e.

$ar->where(‘id’, array(50,32,71));

$ar->where(‘age >’,20,’OR’);

Generated output:

… WHERE id IN (’50’,’32’,’71’) OR age>’20’ …

You can also specify the array operator your self and PhARoS will detect it .i.e.

$ar->where(‘id NOT IN’, array(50,32,71));

$ar->where(‘age >’,20,’OR’);

Generated output:

… WHERE id NOT IN (’50’,’32’,’71’) OR age>’20’ …

Good enough! PhARoS is also intelligent for NULLs

$ar->where(‘fid’, NULL);

$ar->where(‘age >’,20);

Generated output:

… WHERE fid IS NULL AND age>’20’ …

And same operator detection holds here (for NULL) as well like before

$ar->where(‘fid IS NOT’, NULL);

$ar->where(‘age >’,20);

Generated output:

… WHERE fid IS NULL AND age>’20’ …

Who can forget the good old LIKE operator:

$ar->where(‘name LIKE’, ‘%ohaib’);

$ar->where(‘age >’,20);

Generated output:

… WHERE name LIKE ‘%ohaib’ AND age>’20’ …

Now time for most elegant feature of the PhARoS, you can do the same as above using associative arrays, this can be very useful when using it on GET or POST variables,

$ar->where(
array(‘id’=> array(1,2,3,4,5),‘age >’ => 20,‘fid’=>NULL,‘name LIKE’=>’%ohaib’)
);

Generated output:

… WHERE id IN(‘1’,’2’,’3’,’4’,’5’) AND age > ‘20’ AND fid IS NULL AND age>’20’ AND name LIKE ‘%ohaib’…

Want to change the concatenation operator? Simply ask PhARoS!!!

$ar->where(array(

‘id’=> array(1,2,3,4,5),

‘age >’ => 20,

‘fid’=>NULL,

‘name LIKE’=>’%ohaib’),

‘OR’);

Generated output:

… WHERE id IN(‘1’,’2’,’3’,’4’,’5’) OR age > ‘20’ OR fid IS NULL OR age>’20’ OR name LIKE ‘%ohaib’…

Well you have some “complex” kind of conditional structure but still want PhARoS to do the work for you? Well I got inspired by the PDO style query preparation system and also provided that .e.g.

$ar->where(‘(id IN :ids AND fid =:fid) OR age>=:age’,

array(

‘id’=>array(1,2,3),

‘fid’=>32,

‘age’=>20

));

Generated output:

… WHERE (id IN (‘1’,’2’,’3’) AND fid=’32’) OR age>=’20’…

I think its enough to generate almost above average queries.

Joining Tables

Joining table is also simple, here is the basic syntax:

$ar->join(tableName ,joinCondition, type);

Simply replace the table name, join condition, and type with your requirements .e.g.

$ar->from(‘posts’);

$ar->join(‘comments’ ,’posts.id=comments.pid’, ‘LEFT’);

Generated output:

… FROM posts LEFT JOIN comments ON (posts.id = comments.id)…

$ar->from(‘posts’);

$ar->join(‘comments’ ,’posts.id=comments.pid’, ‘RIGHT’);

Generated output:

… FROM posts RIGHT JOIN comments ON (posts.id = comments.id)…

Order By

Ordering can be done in two ways, one is like the where function call where() passing column name and order type(DESC by default) passing pair .i.e.

$ar->orderBy(‘name’);

$ar->orderBy(‘age’,’ASC’);

$ar->orderBy(‘id’,’DESC’);

Generated output:

… ORDER BY name DESC, age ASC, id DESC…

Other method can be to pass associative array with each key as name and value as order type .i.e. the above output can be achieved by single call as well

$ar->orderBy(

array(

‘name’=>’DESC’,

‘age’=>’ASC’,

‘id’=>’DESC’

));

Group By

Parameters are same as that of calling methodology of select()

$ar->groupBy(‘col1’,’col2’);

$ar-> groupBy (array(‘col1’,’col2’));

Generated output:

… GROUP BY col1,col2 …

Group conditions (Having)

You have the same powers as that of the where() system (see the where conditions section) except the last PDO parsing part, but don’t worry there is an cut down version as “?” operator .i.e.

$ar->having(array(‘cnt_totl’=>1,’p <’=>30),’OR’);

$ar->having(‘avg_age >’,20);

$ar->having(‘cpl IN (?)’,array(10,3,20));

$ar->having(‘pp < ?’,20,’OR’);

Generated output:

… HAVING cnt_totl=’1’ OR p<’30’ AND avg_age > ‘20’ AND cpl IN (‘10’,’3’,’20’) AND pp < ‘20’…

Generating Select Query

After you are done with applying everything we are ready to generate query, do it by few key strokes like this:

$ar->where(‘id’,arrary(1,2,3))

$ar->from(‘comments’);

$qry = $ar->selectQuery();

Generated output:

SELECT * FROM comments WHERE id IN(‘1’,’2’,’3’)

Same query can be also generated like this

$ar->where(‘id’,arrary(1,2,3))

$qry = $ar->selectQuery(‘comments’);

Generating Insert, Delete, or Update Queries

For generating an insert or an update query you must set the data first, you can set the data by the set() call .e.g.

$ar->set(‘title’,’My Comments’);

$ar->set(array(

‘commentText’ => $comment,

‘date’=> date()

)); //Works both ways

$ar->insertQuery(‘comments’);

Generated output:

INSERT INTO comments(title,commentText,date) Values(‘My Comments’, ’here is my comment’,’2131232133’);

Simple same goes for the update query generation

$ar->set(‘title’,’My Comments’);

$ar->set(array(‘commentText’ => $comment));

$ar->where(‘id’,1)

$ar->updateQuery(‘comments’);

Generated output:

UPDATE comments SET title = ‘My Comments’, commentText=’blah blah blah’ WHERE id=’1’;

Deletion, I guess you would have guessed it up-till now:

$ar->where(‘id’,1)

$ar->deleteQuery(‘comments’);

Generated output:

DELETE FROM comments WHERE id = ‘1’;

PHP 5 Users

PHP 5 users can chain methods for example

$qry = $ar->where(‘id’,1)->deleteQuery(‘comments’);

$ar->where(‘id’,arrary(1,2,3))->from(‘comments’)->selectQuery();

Making code more sleek ;)