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?). http://stackoverflow.com/questions/32685881/filter-mysql-query-with-form-options/32686418#32686418 — chris85, Sep 25 '15 at 02: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, Sep 25 '15 at 06:20

score 2 · Answer 1 · edited Sep 29 '15 at 17:03

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 '15 at 17:03

Gustavo Straube

3,744
6
39
62

answered Sep 25 '15 at 01:49

lincolndidon1

21
4

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 – Gerald Gray Sep 25 '15 at 06:22

chris85 · Answer 2 · 2015-09-29T20:55:01.863

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 '15 at 20:55

answered Sep 25 '15 at 14:47

chris85

23,846
7
34
51

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. – Gerald Gray Sep 29 '15 at 15:38
@GeraldGray What error did you get; or how did it not work? – chris85 Sep 29 '15 at 15:48
Can you output `$query`? – chris85 Sep 29 '15 at 15:58
The Output of $query gives me `SELECT * FROM jobs where job_title like ?` – Gerald Gray Sep 29 '15 at 16:08
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 Sep 29 '15 at 20:20
Hmm strange so nothing about parameter one anymore? – chris85 Sep 29 '15 at 20:50

Gerald Gray · Answer 3 · 2015-09-29T15:45:28.103

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 '15 at 15:45

answered Sep 29 '15 at 15:43

Gerald Gray

27
8

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. https://en.wikipedia.org/wiki/SQL_injection 2. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – chris85 Sep 29 '15 at 15:45
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.. – chris85 Sep 29 '15 at 15:47
@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` – Gerald Gray Sep 29 '15 at 15:53

PHP search form with multiple inputs

3 Answers3