<?php
/*
Very often in my practice I had to generate a query from the form in
order to make a search engine. This example shows the logic which I
use to resolve this practical problem.
Note: This example is trivial - I want to show just the logic of
resolving the problem.
Starting from this point through improving this solution you can
very easily resolve much more complicated problems.
This class also provide more features which I didn't use in this easy
case. In case, that you are interested in more details regarding this
example you can contact me via the contact author link at the top of this
example or you can add a comment to it.
I can also be reached here:
http://pad.pmf.kg.ac.yu/articles/index.php?action=contact
*/
include "sql_builder.php";
if(isset($search)){
$par_name = array();
$par_value = array();
if (trim($s_price)!='') {
$par_name[]="s_price";
$par_value[]=$s_price;
}
if (trim($s_city)!='') {
$par_name[]="s_city";
$par_value[]=$s_city;
}
if (trim($s_badrooms)!='') {
$par_name[]="s_badrooms";
$par_value[]=$s_badrooms;
}
if (trim($s_bathrooms)!='') {
$par_name[]="s_bathrooms";
$par_value[]=$s_bathrooms;
}
if (trim($sort)!='') {
$par_name[]="sort";
$par_value[]=$sort;
}
$a = new sql_builder($par_name,$par_value,0,10);
$a->set_add_arg();
$sql.=$a->sql_generator();
include "index.html";
print($sql);
}
?>
sql_builder.php
<?php
class sql_builder{
var $def_tf;//Default searching fields
var $def_tl;//Default table names
var $def_links;//Default links between tables
var $def_where = "where";//Where
var $def_and = "and";//And
var $limit="LIMIT ";//Limit
var $koma=",";//,
var $def_string;//Default Query String
var $add_table; //Additional table names
var $add_links;//Additional table links
var $add_desc;//Additional Query description
var $row_num;//Number of the rows which we want to see
var $start_row;//Start row
var $limit_string;
var $final_string;//Generated Query
var $blanko = " ";
var $par_name;//List of parameter Names
var $par_value;//List of Parameter Values
var $def_sort;//Default Sort expresion
function sql_builder($par1,$par2,$par3,$par4){
$this->par_name=$par1;
$this->par_value=$par2;
$this->set_limit_string($par3,$par4);
$this->set_default();
}
function set_default(){
//At first we are setting fields which we want to see
$this->def_tf=" select* from ";
//After that we are setting names of the tables which we want to search -
//In our easy example we have just one table
$this->def_tl=" house ";
//This is a place where we are setting Join expression in case that we
//have more tables which we want to search - in our easy example we have
// just table and because that field is empty
$this->def_links=" ";
//We are setting here default Order By expresion
$this->def_sort="Order by s_price";
//REMEMBER THAT WE ARE STILL ON DEFAULT EXPRESION
$this->def_string=$this->def_tf.$this->def_tl.$this->def_where.$this->def_links.$this->def_sort.$this->limit_string;
}
function get_def(){
return $this->def_string;
}
function set_add_desc($par){
$this->add_desc=$par;
}
//This function gives as possibility to dynamically change - build our query
function set_add_arg(){
$n=count($this->par_name);
if ($n==0) {
$this->def_where=null;
}
if ($i!=0) {
$this->def_where=null;
}
for ($i=0;$i<$n;$i++){
//We are asking which parameters where sent by user and in
//dependency of that we are creating our additional query
if ($this->par_name[$i]=="s_price") {
if ($i==0) {
$par.=" s_price='".$this->par_value[$i]."' ";
}else{
$par.=" and s_price='".$this->par_value[$i]."' ";
}
$this->set_add_desc($par);
}
if ($this->par_name[$i]=="s_city") {
if ($i==0) {
$par.=" s_city='".$this->par_value[$i]."' ";
}else{
$par.=" and s_city='".$this->par_value[$i]."' ";
}
$this->set_add_desc($par);
}
if ($this->par_name[$i]=="s_badrooms") {
if ($i==0) {
$par.=" s_badrooms='".$this->par_value[$i]."' ";
}else{
$par.=" and s_badrooms='".$this->par_value[$i]."' ";
}
$this->set_add_desc($par);
}
if ($this->par_name[$i]=="s_bathrooms") {
if ($i==0) {
$par.=" s_bathrooms='".$this->par_value[$i]."' ";
}else{
$par.=" and s_bathrooms='".$this->par_value[$i]."' ";
}
$this->set_add_desc($par);
}
if ($this->par_name[$i]=="sort") {
$this->def_sort="Order by ".$this->par_value[$i];
$this->set_add_desc($par);
}
}
}
function set_row_num($par){
$this->row_num=$par;
}
function set_start_row($par){
$this->start_row=$par;
}
//Finally with this function we are finished with building of our query
function sql_generator(){
return $this->final_string=$this->def_tf.$this->def_tl.$this->add_table.$this->def_where.$this->def_links.$this->add_links.$this->add_desc.$this->def_sort.$this->limit_string;
}
function set_limit_string($par1,$par2){
$this->set_row_num($par2);
$this->set_start_row($par1);
$this->limit_string=$this->blanko.$this->limit.$this->blanko.$this->start_row.$this->koma.$this->row_num;
return $this->limit_string;
}
}
?>
