PHP Classes
elePHPant
Icontem

File: readme.txt

Recommend this page to a friend!
  Classes of Rahman Haghparast  >  Where Builder  >  readme.txt  >  Download  
File: readme.txt
Role: Documentation
Content type: text/plain
Description: Read me first!
Class: Where Builder
Create SQL WHERE clauses from request parameters
Author: By
Last change:
Date: 3 years ago
Size: 4,943 bytes
 

Contents

Class file image Download
This class is written to for developers to generate the where section of queries from url using some simple rules.

Installation:
You just need to include request.class.php in your codes, create a new instance of the class and call the getOutput() method of the class with 'where' parameter. You can check the sample.php file to see it in action.
The class would not consider 'option', 'controller', 'view', 'action' and 'verb' in url when creating the where section of the queries. You can pass other keywords as the unset key of the input array as seen in the sample.php file.

How to use:
In order to create where section of your queries, you should follow the rules below:
You should specify the name of the field followed by = and its value. What follows this is optional but is used almost all the times. In order to set the optional parameters you should use commas.
After the first comma, the name of the operand is used and after that a series of options which are explained below.
Operands are in form of standard two character mathematical forms which are eq, ne, lt, le, gt, ge and lk. The lk is not standard but was added to the list to support LIKE in queries.
Let's say you want to add `city` = 'tehran' to your where section of the query. All you need to do is adding &city=tehran to the url. 
Since no comma is used after tehran, the default operand which is eq(=) is used and the result would be `city` = 'tehran'
What if you'd like to check for lastnames ending in 'er' or in other words, WHERE `lastname` LIKE '%er'. Let's see how we can generate that with this class. 
You just need to add &lastname=er,lk,a Now if you add this to the previous query string to have city=tehran&lastname=er,lk,a, the resulting where clause would be:
`city` = 'tehran' AND `lastname` LIKE 'er%' 
Pretty cool, now let's add another condition to list those older than 25 and younger or equal to 45 or in other words, WHERE `age` > 25 AND `age` <= 45
Here is how you can do it. &age[]=25,gt,n&age[]=45,le,n
Adding this to the previous url will make it city=tehran&lastname=er,lk,a&age[]=25,gt,n&age[]=45,le,n and the result will be:
`city` = 'tehran' AND `lastname` LIKE 'er%' AND `age` > 25 AND `age` <= 45
The 'n' parameters tells the class not to surround the value with quotes and to handle it as a number.
Now we want to list those who work in hr or technical section of the company or in other words, WHERE `section` = 'hr' OR `section` = 'technical'
We just need to add this to the url. &section=[hr,technical]
The code will automatically surround the phrase with parenthesis. The url would be city=tehran&lastname=er,lk,a&age[]=25,gt,n&age[]=45,le,n&section=[hr,technical] and the result would be:
`city` = 'tehran' AND `lastname` LIKE 'er%' AND (`section` = 'hr' OR `section` = 'technical') AND `age` > 25 AND `age` <= 45
That's not all. You can add parenthesis to the where clause to create more complex queries. The class also supports alias tables. Let' add (user.salary > 2000 AND (user.leave < 5 OR user.isManager = 1))
&user[salary]=2000,gt,po&user[leave]=5,lt,o,n,po&user[isManager]=1,n,pc,pc
Please note that 'o' in options section will add OR after the phrase. 'po' opens a parenthesis and 'pc' closes it. You can use nested parenthesis as shown here. Here is the final url and the resulting where clause:
http://localhost/request/sample.php?city=tehran&lastname=er,lk,a&age[]=25,gt,n&age[]=45,le,n&section=[hr,technical]&user[salary]=2000,gt,po&user[leave]=5,lt,o,n,po&user[isManager]=1,n,pc,pc
`city` = 'tehran' AND `lastname` LIKE 'er%' AND (`section` = 'hr' OR `section` = 'technical') AND `age` > 25 AND `age` <= 45 AND (`user`.salary > '2000' AND (`user`.leave < 5 OR `user`.isManager = 1))
It happens that you would like to have other parameters in your query string which should not be included in the where clause. All you need to do is adding them after nowhere=1 in the query string.
http://localhost/request/sample.php?city=tehran&lastname=er,lk,a&age[]=25,gt,n&age[]=45,le,n&section=[hr,technical]&user[salary]=2000,gt,po&user[leave]=5,lt,o,n,po&user[isManager]=1,n,pc,pc&nowhere=1&redirect=1
In the example above, redirect=1 will not be included in the where clause.

Examples:
a=jack,lk,ba => `a` LIKE '%jack%' //before and after
a=jack,lk,ab => `a` LIKE '%jack%' //before and after
a=jack,lk,b  => `a` LIKE '%jack'  //before
a=jack,lk,a  => `a` LIKE 'jack%'  //after
b=300,ge     => `b` >= '300'
b=300,ge,n   => `b` >= 300
b=300,lt,n,o => `b` < 300 OR
c=[paris,london,lisbon,tehran] => (c='paris' OR c='london' OR c='lisbon' OR c='tehran')
user[id]=123 => `user`.id = 123
c=12,eq,n,po => (`c` = 12
c=34,gt,n,pc => c > 34)
d[]=55,eq,n,po&d[]=66,eq,n,pc => (d=55 AND d=66)
d[]=55,eq,n,po,o&d[]=66,eq,n,pc => (d=55 OR d=66)

License:
This program is free for non-commercial use.

Please send any comments and questions to my email address:
haghparast@gmail.com