PHP Classes
elePHPant
Icontem

Where Builder: Create SQL WHERE clauses from request parameters

Recommend this page to a friend!
  Info   View files Example   View files View files (3)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2015-12-20 (3 years ago) RSS 2.0 feedNot enough user ratingsTotal: 187 All time: 8,366 This week: 363Up
Version License PHP version Categories
where-builder 1.0Free for non-comm...5.4HTTP, PHP 5, Databases
Description Author

This class can create SQL WHERE clauses from request parameters.

It can take the current HTTP request parameters and build SQL query clauses from its values. The class can exclude certain request values.

Parameters may be followed by operands that determine how they will form the SQL query clause. Currently it supports operands eq, ne, lt, le, gt, ge and lk.

Creating dynamic filters for data is a nightmare for developers. You can easily create your customized where clauses using this class.

Innovation Award
PHP Programming Innovation award nominee
January 2016
Number 3


Prize: One copy of the Zend Studio
In many applications database queries are often executed using parameters passed to PHP via HTTP GET or POST methods.

This class implements a solution to build SQL queries from HTTP request parameters and combine them using given operators which are translated to SQL.

Manuel Lemos
  Performance   Level  
Name: Rahman Haghparast <contact>
Classes: 10 packages by
Country: Iran Iran
Age: 39
All time rank: 781 in Iran Iran
Week rank: 201 Down5 in Iran Iran Down
Innovation award
Innovation award
Nominee: 2x

 

Details
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
  Files folder image Files  
File Role Description
Accessible without login Plain text file readme.txt Doc. Read me first!
Plain text file request.class.php Class main class file
Accessible without login Plain text file sample.php Example sample on how to use the class

 Version Control Unique User Downloads Download Rankings  
 0%
Total:187
This week:0
All time:8,366
This week:363Up