|
|
|
|
|
|
| |
index.php
| <?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;
}
}
?> | |
index.html
| <html>
<head>
<title></title>
</head>
<body>
<FORM method="post" action="index.php">
<TABLE>
<tr>
<td>Price:</td>
<td><select name=s_price>
<OPTION value="">--Any--</OPTION>
<OPTION value="500000">500000</OPTION>
<OPTION value="100000">100000</OPTION>
<OPTION value="150000">150000</OPTION>
</select></td>
</tr>
<td>City:</td>
<td><select name=s_city>
<OPTION value="">--Any--</OPTION>
<OPTION value="Bremen">Bremen</OPTION>
<OPTION value="Bremen">Amsterdam</OPTION>
<OPTION value="Bremen">Belgrade</OPTION>
</select></td>
</tr>
<td>Badrooms:</td>
<td><select name=s_badrooms>
<OPTION value="">--Any--</OPTION>
<OPTION value="1">1</OPTION>
<OPTION value="2">2</OPTION>
<OPTION value="3">3</OPTION>
<OPTION value="4">4</OPTION>
</select></td>
</tr>
<tr>
<td>Badrooms:</td>
<td><select name=s_bathrooms>
<OPTION value="">--Any--</OPTION>
<OPTION value="1">1</OPTION>
<OPTION value="2">2</OPTION>
<OPTION value="3">3</OPTION>
<OPTION value="4">4</OPTION>
</select></td>
</tr>
<tr>
<td>Sort by:</td>
<td><select name=sort>
<OPTION value="">--Any--</OPTION>
<OPTION value="s_price">Price</OPTION>
<OPTION value="s_city">City</OPTION>
<OPTION value="s_badrooms">Badrooms</OPTION>
<OPTION value="s_bathrooms">Badrooms</OPTION>
</select></td>
</tr>
<tr>
<td><INPUT type="submit" value=search name=search ></td>
<td></td>
</tr>
</TABLE>
</FORM>
</body>
</html> | |
|
|
| This function will populate the options in a drop down HTML select list
in a form from a database query.
Categories : MySQL, General SQL, PHP, HTML and PHP, Databases | | | Point and Click Interface ala MS Access for creating SQL statements.
Categories : MySQL, Complete Programs, General SQL, PHP, Databases | | | Message of the Day - Random Message (Needs MySQL!)
Categories : Databases, HTML and PHP, PHP, MySQL | | | Alternating background color for HTML table rows
Categories : PHP, Databases, MySQL, HTML and PHP | | | A very simple way to build and do a hierarchical html categories browser without javascript , just using html php and mySql
Categories : HTML and PHP, Databases, Algorithms, PHP, MySQL | | | Automatically printing the contents of an sql table in MySQL.
Categories : MySQL, PHP, HTML and PHP, Databases | | | XDT Topsite (Gold v1.0)
Categories : Databases, CSS, PHP, HTML and PHP, Sessions | | | Pull Down Surfing - Surf on Change
Categories : Java Script, MySQL, HTML and PHP, PHP, Databases | | | Dynamically generated pop-ups (Select items)
Categories : PHP, HTML and PHP, MySQL, Databases | | | Creating thumbnails from MySQL Blobs online
Categories : PHP, MySQL, Graphics, HTML and PHP, Databases | | | Record Set Paging with PHP (RSP)
Categories : PHP, MySQL, Navigation, Databases, HTML and PHP | | | Is there some possibility to link a database to an htaccess file, so that instead of having a passwd file you would have a database with DES-crypted password and username fields?
Categories : Authentication, PHP, General SQL, Databases | | | Functions for loading images into a MySQL database and displaying them.
Categories : Graphics, HTML and PHP, MySQL, PHP, Databases | | | dynamic table columns
Categories : PHP, HTML and PHP, Arrays, Databases, MySQL | | | phpEasySQL - Easily connect to your MySQL database with just 1 php file and 3 easy steps!
Categories : Databases, PHP, MySQL, General SQL | |
|
|
|
