Conditional Search PHP and MySQL

[cary1234]

Hello! Right now I'm working on advance search for my website and I'm stuck on this part of my website. I'm glad I can shout for help here in you forums.

 

What I want is whenever a user use the advance search the user can search for first name, last name, middle name and lastly nick name.
 

The Condition:

Sometimes the user may want to define their search specifically like for example I want to search all the people who have a last name "Bondoc" that lives in "Philippines" so they will check the checkbox for first name and country then click the advance search. What is the best code for this logic?

Right now this is what I did.. But I guest I will code every type of combination which really needs some effort, any other idea please?

 

INPUT

 

 

 

 

THE RESULT

And here's my code

$db_Connection = mysqli_connect($db_Host, $db_User, $db_Pass, $db_Name);

$cb_Fname = $_POST['cb_Fname'];
$tb_Fname = $_POST['tb_Fname'];
$cb_Lname = $_POST['cb_Lname'];
$tb_Lname = $_POST['tb_Lname'];

if ($cb_Fname == "1"){
	//checkbox is CHECKED
	$fname = "fname LIKE '%$tb_Fname%'";
}

else{
	//checkbox not CHECKED
	$fname = "";
	
}

if ($cb_Lname == "1"){
	//checkbox is CHECKED
	$lname = "AND lname LIKE '%$tb_Lname%'";
}

else{
	//checkbox not CHECKED
	$lname = "";
	
}

$qry_Ftt = mysqli_query($db_Connection, "SELECT *  FROM users WHERE $fname $lname");
?>

 

[Barand]

My usual method is this (not valid code - just to give the idea)

$where = array();
$whereclause = '';

if (isset(condition1) && !empty(condition1)) {
    $where[] = "(col1 = 'condition1')";
}
if (isset(condition2) && !empty(condition2)) {
    $where[] = "(col2 = 'condition2')";
}

// etc

if (count($where) > 0) $whereclause = "WHERE " . join(' AND ', $where);

$sql = "SELECT foo, bar $whereclause";

Remember to sanitize conditions

Edited October 10, 2013 by Barand

[wittenberg]

Do you really need the checkboxes? Why don't you search ONLY for fields that the user filled?

[cary1234]

 

My usual method is this (not valid code - just to give the idea)

$where = array();
$whereclause = '';

if (isset(condition1) && !empty(condition1)) {
    $where[] = "(col1 = 'condition1')";
}
if (isset(condition2) && !empty(condition2)) {
    $where[] = "(col2 = 'condition2')";
}

// etc

if (count($where) > 0) $whereclause = "WHERE " . join(' AND ', $where);

$sql = "SELECT foo, bar $whereclause";

Remember to sanitize conditions

Thanks Barand for your reply, I tried to apply your code but honestly I dont know how to change.
I have so many questions, like what should I put in col1, condition1, $whereclause, etc.. etc... also why is it that there are 2 columns now in $sql (foo, bar)

Will you please explain it more?



 

Do you really need the checkboxes? Why don't you search ONLY for fields that the user filled?

 

Thanks wittenberg for your reply, I dont know if there are any other way for this but my purpose is to give the user some choices on how to specifically search ​what they want, what if there are 500 users that has a firstname 'Jack' but they lived in different place and what the user wants to search is 'Jack' that lives in 'netherland' only I'm sure if I give the user some options they will be happy.

And also the purpose of checkbox is to limit the user not to type if the user didn't check the checkbox, this is to prevent them from accidentally typing.



SORRY FOR MULTIPLE POST, THATS BECAUSE OF INTERNET CONNECTION ISSUES OF MINE

Edited October 10, 2013 by cary1234

[cary1234]

So far this is what I did to apply your code Barand


 

<input type="checkbox" id="cb_Fname" name="cb_Fname" value="1">First Name</label>
<input type="text" id="tb_Fname" name="tb_Fname">
<br />
<input type="checkbox" id="cb_Lname" name="cb_Lname" value="1">Last Name</label>
<input type="text" id="tb_Lname" name="tb_Lname">
<br />

 

 

<?php
require('/Functions/db_Connect.php');
$db_Connection = mysqli_connect($db_Host, $db_User, $db_Pass, $db_Name);

$cb_Fname = $_POST['cb_Fname'];
$tb_Fname = $_POST['tb_Fname'];
$cb_Lname = $_POST['cb_Lname'];
$tb_Lname = $_POST['tb_Lname'];


$where = array();
$whereclause = '';

if (isset($tb_Fname) && !empty($tb_Fname)) {
    $where[] = "fname like '%$tb_Fname'";
}
else
{
echo "mali ito A";
}


if (isset($tb_Lname) && !empty($tb_Lname)) {
    $where[] = "lname like '%$tb_Lname'";
}
else{
echo "mali ito B";
}

// etc

if (count($where) > 0) $whereclause = "WHERE " . join(' AND ', $where);

$sql = "SELECT * FROM trainees $whereclause";

echo $sql;
?>

 

But I dont know, I think what I'm doing is wrong or Im missing something

Edited October 10, 2013 by cary1234

[cary1234]

I got the code and explanation in this forum http://www.dreamincode.net/forums/topic/252413-how-to-remove-notice-undefined-index/