Filter Search PHP/mySQL

[noelpasia]

Can anyone help me with my search engine. I need filter search in the system using PHP. The problem with the code: All the provided fields must be filled up..unless it will not work

There were 6 fields such as
Remark Status:
Industry:
Position:
Location:
Age Range: Min and Max

I have to use these fields as the filters,but eventually all the fields must be filled up before it works..How if i provide just one field, let say i fill the position as HR Manager so i did not specify its age and location...Please Help me on this,,,

if(isset($_GET['advance_searching'])){


if ($search_remarks = "") {
$search_remarks = $_POST[''];}
elseif($search_industry = ""){
$search_industry = $_POST['']; }
elseif ($search_position = "") {
$search_position = $_POST['']; }
elseif ($search_city = "") {
$search_city = $_POST['']; }
elseif ($age_one = "") {
$age_one = $_POST['']; }
elseif ($age_two = "") {
$age_two = $_POST['']; }
else {
$search_remarks = $_POST['search_remarks'];
$search_industry = $_POST['search_industry'];
$search_position = $_POST['search_position'];
$search_city = $_POST['search_city'];
$age_one = $_POST['age_one'];
$age_two = $_POST['age_two'];
}
$query_str = "SELECT * FROM resumevault WHERE age BETWEEN '$age_one' AND '$age_two' AND position='$search_position' AND city='$search_city' AND process='$search_remarks' AND industry='$search_industry'";

[urtrivedi]

Visit following post and go through all comments, I think you will find proper solution

http://www.daniweb.com/web-development/php/threads/366629

[noelpasia]

I think the link in your replied post(thread) was very helpful but in my case, I need to understand it from the easiest way but its quite difficult (Im a beginner in PHP). but then I thank you @urtrivedi

[diafol]

Without wanting to give a complete solution, something like this:

if(isset($_GET['advance_searching']) && !empty($_GET)){
	$where = "";
	if($_POST['search_remarks']!="")	$s[] = "`process` = '"	. mysql_real_escape_string($_POST['search_remarks']) . "'";
	... (other fields)
	if($_POST['age_one']!="")		$s[] = "`age` >= "	. intval($_POST['age_one']);
	... (other age)
	if(isset($s))$where = "WHERE " . implode(" AND ",$s);
	... (concatentate query string)
}

[noelpasia]

if(isset($_GET['advance_searching']) && !empty($_GET)){
$where = "";
if($_POST['search_remarks']!="") {
$s[] = "`process` = '" . mysql_real_escape_string($_POST['search_remarks']) . "'";
}

if($_POST['search_industry']!="") {
$s[] = "`process` = '" . mysql_real_escape_string($_POST['search_industry']) . "'";
}

if($_POST['search_position']!="") {
$s[] = "`process` = '" . mysql_real_escape_string($_POST['search_position']) . "'";
}

if($_POST['search_location']!="") {
$s[] = "`process` = '" . mysql_real_escape_string($_POST['search_location']) . "'";
}

if($_POST['age_one']!="") { $s[] = "`age` >= " . intval($_POST['age_one']);
}

if($_POST['age_two']!="") { $s[] = "`age` >= " . intval($_POST['age_two']);
}

if(isset($s)){
$where = "WHERE " . implode(" AND ",$s);

}
$query_str = "SELECT * FROM resumevault WHERE age BETWEEN '$age_one' AND '$age_two' AND position='$search_position' AND city='$search_city' AND process='$search_remarks' AND industry='$search_industry'";
}

I do it this way...theres no error but the process doesn't work,

actually i wrote something wrong with my 1st statement, my program code is working in the first way i did.. my target is to filter either filling up all fields or even one field.

[urtrivedi]

When You are looking in four search fields in same column (PROCESS), then AND will not work. You need to implode with OR. Same is applicable to AGE field. Separate age1, age2 with OR

Another thing I would like to mention there is no need to keep 4 fields. you can keep one field for one column, ultimately you are using PROCESS and AGE column so you need only 2 search fields not 6.

[noelpasia]

Yap... i already do it 4 fields in a query like this :

$query_str = "SELECT * FROM database WHERE remarks LIKE '$search_text' OR industry LIKE '$search_text' OR position LIKE '$search_text' OR location LIKE '$search_text'";

so that i can search using 4 keywords in 1 text field...

but I have to do 4 fields in requirements and 2 fields for minimum and maximum to specify age limit .

example:

Im looking for the active applicants(remarks status) in IT industry(industry) which are JAVA Developer(position) from Los Angeles(location) age : 20 to 35 years old...

@urtrivedi

[vibhaJ]

if(isset($_GET['advance_searching']) && !empty($_GET)){ $where = ""; if($_POST['search_remarks']!="") { $s[] = "`process` = '" . mysql_real_escape_string($_POST['search_remarks']) . "'"; } if($_POST['search_industry']!="") { $s[] = "`process` = '" . mysql_real_escape_string($_POST['search_industry']) . "'"; } if($_POST['search_position']!="") { $s[] = "`process` = '" . mysql_real_escape_string($_POST['search_position']) . "'"; } if($_POST['search_location']!="") { $s[] = "`process` = '" . mysql_real_escape_string($_POST['search_location']) . "'"; } if($_POST['age_one']!="") { $s[] = "`age` >= " . intval($_POST['age_one']); } if($_POST['age_two']!="") { $s[] = "`age` >= " . intval($_POST['age_two']); } if(isset($s)){ $where = "WHERE " . implode(" AND ",$s); } $query_str = "SELECT * FROM resumevault WHERE age BETWEEN '$age_one' AND '$age_two' AND position='$search_position' AND city='$search_city' AND process='$search_remarks' AND industry='$search_industry'"; }

I do it this way...theres no error but the process doesn't work,

actually i wrote something wrong with my 1st statement, my program code is working in the first way i did.. my target is to filter either filling up all fields or even one field.

As per ardav's logic you don't have to again write where condition. All above if-else condition itself makes where string. so your $query_str will be :

<? 
if(isset($s)){
$where = "WHERE " . implode(" AND ",$s);

}
$query_str = "SELECT * FROM resumevault WHERE ".$where;
}
?>

[urtrivedi]

if(isset($_GET['advance_searching']) && !empty($_GET))
{

$where = " 1 = 1 ";
if($_POST['search_remarks']!="")	{
$s[] = "`remarks` like '%"	. mysql_real_escape_string($_POST['search_remarks']) . "%'";
}

if($_POST['search_industry']!="")	{
$s[] = "`industry` like '%"	. mysql_real_escape_string($_POST['search_industry']) . "%'";
}

if($_POST['search_position']!="")	{
$s[] = "`position` like '%"	. mysql_real_escape_string($_POST['search_position']) . "%'";
}

if($_POST['search_location']!="")	{
$s[] = "`location` like '%"	. mysql_real_escape_string($_POST['search_location']) . "%'";
}

if($_POST['age_one']!="" && $_POST['age_two']!="")	{
	$s[] = "(`age` between "	. intval($_POST['age_one']) ." AND ".intval($_POST['age_two']) .")";
}

if(isset($s)){
$where = "WHERE (" . implode(" AND ",$s).")";

}


}

$query_str = "SELECT * FROM resumevault {$where}";
echo $query_str;

[noelpasia]

alright .. i forgot haha its process not remarks, my own error ,,so its supposedly process im sorry...

its working,,,so what is to use of 1 =1 ?...Could u explain it ..Please so that my problem is nearly solve,,, its working but this statement appears

its working,,,

SELECT * FROM resumevault 1 = 1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = 1' at line 1"

[urtrivedi]

YOU can remove it and make it to

$where="";

[noelpasia]

thanks a lot you made it really great, so now i just have to learn this properly

@urtrivedi

you deserve respect ...Problem solve...

[vibhaJ]

1=1 is just to make string proper without any where error.

$where = " WHERE 1 = 1 ";

[noelpasia]

haha i did not notice the names hahaha
Thanks a lot

urtrivedi
vibhadevit
ardav