CODEWITHSUNDEEP
phpmysql
Gerald Gray
Gerald Gray

Reputation: 27

PHP search form with multiple inputs

I have a PHP script which I'm trying to use to generate search results from a db, with multiple search text inputs and a submit. The three inputs are Term, Keyword and Location. I have achieved the search but the validation logic i am finding a tad difficult. I broke the query into three parts so that when one of the inputs is empty it exempts that part of the query being added to the full query. But because of the OR operator when the first input is empty the rest fails.

A second eye will be helpful here. Please don't mark this question as too generic just have a look at the query and you will see the problem. 

$term = mysqli_real_escape_string ($con , $_GET['term'] );
$location = mysqli_real_escape_string ($con , $_GET['location'] );
$keyword =  mysqli_real_escape_string ($con , $_GET['keyword'] );

if(empty($term)) {
$term1 = "";
}else{$term1 = "job_title LIKE '%".$term."%'";}

if(empty($location)) {
$loc1 = "";
}else{$loc1 = "location LIKE '%".$location."%'";}

if(empty($keyword)) {
$key1 = "";
}else{$key1 = "OR description LIKE '%".$keyword."%'";}


$sql = "SELECT * FROM jobs WHERE ".$term1." ".$loc1." ".$key1." ";
$r_query = mysqli_query($con,$sql);


while ($joblist = mysqli_fetch_array($r_query)){
$now = date('Y-m-d',time());

Upvotes: 2

Views: 2047

Answers (3)

chris85
chris85

Reputation: 23892

Here's the modified approach. Give this a try, if it works or doesn't please post back.

if (!empty($_GET['term'])) {
    $where[] = " job_title like ? ";
    $params[] = '%' . $_GET['term'] . '%';
}
if (!empty($_GET['location'])) {
    $where[] = " location like ? ";
    $params[] = '%' . $_GET['location'] . '%';
}
if (!empty($_GET['keyword'])) {
    $where[] = " description like ? ";
    $params[] = '%' . $_GET['keyword'] . '%';
}
$sql_where = !empty($where) ? ' where ' . implode(' or ', $where) : '';
$query = "SELECT * FROM jobs $sql_where";
if (!($tot = mysqli_prepare($con, $query))) {
   echo "Prepare failed: (" . mysqli_errno($con) . ") " . mysqli_error($con);
} else {
    if(!empty($params)) {
    $params = array_merge(array($tot),
        array(str_repeat('s', count($params))), 
        $params);
    call_user_func_array('mysqli_stmt_bind_param', $params);
    // adapated from http://stackoverflow.com/questions/793471/use-one-bind-param-with-variable-number-of-input-vars and http://www.pontikis.net/blog/dynamically-bind_param-array-mysqli may need to be altered
    }
    mysqli_execute($tot);
}

Upvotes: 1

lincolndidon1
lincolndidon1

Reputation: 21

You are missing an OR after the first like statement, here -

if(empty($location)) {
$loc1 = "";
}else{$loc1 = "location LIKE '%".$location."%'";}

You probably want to place an OR right before location like, so -

"OR location LIKE '%".$location."%'"

Upvotes: 2

Gerald Gray
Gerald Gray

Reputation: 27

This seems to work based on @chris85's suggestion.I am not quite sure how the $params[] plays a part. Any explanations will be good.

if (!empty($_GET['term'])) {
$where[] = " job_title LIKE '%".$_GET['term']."%' ";
$params[] = '%' . $_GET['term'] . '%';
}
if (!empty($_GET['location'])) {
$where[] = " location LIKE '%".$_GET['location']."%' ";
$params[] = '%' . $_GET['location'] . '%';
}
if (!empty($_GET['keyword'])) {
$where[] = " description LIKE '%".$_GET['keyword']."%' ";
$params[] = '%' . $_GET['keyword'] . '%';
}
$sql_where = !empty($where) ? ' where ' . implode(' or ', $where) : '';
$query = mysqli_query ($con, "SELECT * FROM jobs $sql_where");

Upvotes: 0

Related Questions