PHP search form with multiple inputs

Question

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());

Maybe this thread would be useful to you, OP never posted back if it worked but I think it should (maybe you could post back if it works?). stackoverflow.com/questions/32685881/… — chris85
– chris85, Commented Sep 25, 2015 at 2:08
@chris85 Thank you Chris, sadly his logic is a little different from mine as he isnt using the OR and LIKE operators which are inhibiting my logic :( — Gerald Gray
– Gerald Gray, Commented Sep 25, 2015 at 6:20

Gustavo Straube · Accepted Answer · 2015-09-29 17:03:19Z

2

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."%'"

edited Sep 29, 2015 at 17:03

Gustavo Straube

3,8916 gold badges45 silver badges68 bronze badges

answered Sep 25, 2015 at 1:49

lincolndidon1

214 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gerald Gray Over a year ago

Thank you @lincolndidon1 I had the OR there previously and Took it out before my post . It wouldnt work because :

SELECT * FROM jobs WHERE job_title LIKE '%".$term."%' OR location LIKE '%".$location."%' OR description LIKE '%".$keyword."%'

This is because once the first condition job_title LIKE '%".$term."%' is empty then the query becomes SELECT * FROM jobs WHERE OR location LIKE '%".$location."%' OR description LIKE '%".$keyword."%' Which doesnt work due to the WHERE OR seqence

chris85 · Accepted Answer · 2015-09-29 20:55:01Z

1

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);
}

edited Sep 29, 2015 at 20:55

answered Sep 25, 2015 at 14:47

chris85

23.9k7 gold badges36 silver badges51 bronze badges

6 Comments

Gerald Gray Over a year ago

I tried the query but it seems the params and where werent working well for me so i discovered and made some alterations based on yours and here is the result which worked.

chris85 Over a year ago

@GeraldGray What error did you get; or how did it not work?

chris85 Over a year ago

Can you output $query?

Gerald Gray Over a year ago

The Output of $query gives me SELECT * FROM jobs where job_title like ?

Gerald Gray Over a year ago

It outputs the query along with the warning : Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given in

|

Gerald Gray · Accepted Answer · 2015-09-29 15:45:28Z

0

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");

edited Sep 29, 2015 at 15:45

answered Sep 29, 2015 at 15:43

Gerald Gray

278 bronze badges

3 Comments

chris85 Over a year ago

My approach implements parameterized queries which prevent SQL injections. This is putting user input directly into your query which opens you to SQL injections. What happened with my answer? 1. en.wikipedia.org/wiki/SQL_injection 2. php.net/manual/en/mysqli.quickstart.prepared-statements.php

chris85 Over a year ago

The params separated out the user data from the query. They are unneeded in your present code so you could just remove them. You shouldn't use this code though as noted in previous comment..

Gerald Gray Over a year ago

@chris85 I get the following when running the approach you gave me. Warning: Parameter 3 to mysqli_stmt_bind_param() expected to be a reference, value given Warning: mysqli_execute() expects parameter 1 to be mysqli_stmt, boolean given

Collectives™ on Stack Overflow

PHP search form with multiple inputs

3 Answers 3

1 Comment

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related