Constructing PHP/MySQL search query with variable options

Question

What I'm Trying to Do

I have a SQL table that has a list of job opportunities for a client's website.

I am trying to create a search form but not all the fields will necessarily be filled in.

The possible values are:

title = the title of the job

location = the location of the job

sal1 = the bottom bracket of the desired salary

sal2 = the top bracket of the desired salary

The Code

$query = 'SELECT * FROM `jobs`';

if($_GET['title']!='') $query.= ' AND `title` LIKE %'.$_GET['title'];
if($_GET['location']!='') $query.= ' AND `location`='.$_GET['location'];
if($_GET['sal1']!='') $query.= ' AND `sal1`>='.$_GET['sal1'];
if($_GET['sal2']!='') $query.= ' AND `sal2`<='.$_GET['sal2'];

$stmt=$dbh->prepare($query.' ORDER BY `date` DESC');
$stmt->execute();

The Issue

On paper this method should work, but I would prefer to use prepared statements. The only method of doing this seems to be using mysql_real_escape_string() on each $_GET variable, as I can't figure out how to use execute(array($val1,$val2...)) when I don't know how many variables are in use.

If possible, how can I utilised prepared statements to sanitize the $_GET variables?

The `%` goes inside the quotes. Plus, you should change all `$query.=` to `$query .=` since that will break your query. — Funk Forty Niner, Jan 21 '15 at 16:31
@Fred-ii- *"Plus, you should change all $query.= to $query .= since that will break your query."* whatchoo talkin bout? — Madbreaks, Jan 21 '15 at 16:32
@Madbreaks Try it out for yourself. I don't need to, I know. — Funk Forty Niner, Jan 21 '15 at 16:33
`$query.=` attempts to perform a concatenation, `$query .=` (with a space) correctly employs the operand @Madbreaks — Jay Blanchard, Jan 21 '15 at 16:33
However, the code you have Ben seems to be missing something. Your query right now, translates to `SELECT * FROM table AND column LIKE xxx ORDER BY` unless that's only part of your actual code(?). — Funk Forty Niner, Jan 21 '15 at 16:36
Basically two sets of ifs: one set to include or not the placeholder in the SQL before prepare. One set to bind or not the param. — developerwjk, Jan 21 '15 at 16:39
@georg For one thing, `LIKE %'.$_GET` will fail. More like `LIKE '%word'` and the concatenate will fail. — Funk Forty Niner, Jan 21 '15 at 16:41
@Fred-ii- *"Try it out for yourself. I don't need to, I know.*". You're not really here to help, are you? — Madbreaks, Jan 21 '15 at 16:50
@Madbreaks I admit I was wrong, but go over the comments under Rocket's answer which will explain it in its own right instead of me repeating it here. Please be polite, I was. ;-) and yes, I am here to help as I was right about the `%` being outside the quote. — Funk Forty Niner, Jan 21 '15 at 16:50

gen_Eric · Answer 1 · 2015-01-21T16:48:39.330

2

You can pass execute() an associative array. You just add placeholders for each value in $_GET you want, then pass the whole array to execute()

So, first you create your query string, prepare it, then call execute().

P.S. You forgot the WHERE. You can't use AND without WHERE. I'd suggest building the query like this:

// Base query
$query = 'SELECT * FROM `jobs`';

// WHERE clauses
$where = array();

// Don't concat GET values, use placeholders
if($_GET['title'] != '') $where[] = '`title` LIKE CONCAT("%", :title)';
if($_GET['location'] != '') $where[] = '`location` = :location';
if($_GET['sal1'] != '') $where[] = '`sal1`>= :sal1';
if($_GET['sal2'] != '') $where[] = '`sal2`<= :sal2';

// Combine the WHERE clauses
if(count($where) > 0) $query .= " WHERE ".implode(' AND ', $where);

// Prepare the query
$stmt = $dbh->prepare($query.' ORDER BY `date` DESC');

// Run it with the entered parameters
$stmt->execute($_GET);

NOTE: I'm doing CONCAT("%", :title) in the first clause. This is because LIKE expects a string. It wants something like '%test' (note how the % is part of the string). I used CONCAT in MySQL instead of doing $_GET['title'] = '%'.$_GET['title']; in PHP.

edited Jan 21 '15 at 16:48

answered Jan 21 '15 at 16:40

gen_Eric

223,194
41
299
337

Some don't seem to agree with my comment about `$query.=` while some have. – Funk Forty Niner Jan 21 '15 at 16:42
`$query.=` won't concatenate properly, would "you" agree? I've seen it before where OP's code failed because of not concatenating properly `$query .=` – Funk Forty Niner Jan 21 '15 at 16:45
@Fred-ii-: Works fine here: https://eval.in/245783 – gen_Eric Jan 21 '15 at 16:46
Well I'll be a monkeys uncle. That is too bizarre. As I said, there was a question about a few weeks ago where the concatenate failed because of not having a space between. Too too weird. But why did you choose to do `$query .=` in your answer? – Funk Forty Niner Jan 21 '15 at 16:47
@Fred-ii-: Personal preference. `$query.=` looks ugly :-) – gen_Eric Jan 21 '15 at 16:49
Ok. Well, I was wrong about the concatenate, but pretty sure I was right about the `LIKE %'` having the `%` outside the quote. – Funk Forty Niner Jan 21 '15 at 16:49
2

@Fred-ii-: Yeah. The `%` should be part of the string, not outside it. – gen_Eric Jan 21 '15 at 16:51

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

You can handle variable number of query parameters by using bindParam:

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.

Docs here.

EDIT To use LIKE: How do I create a PDO parameterized query with a LIKE statement?

edited May 23 '17 at 12:30

Community

1
1

answered Jan 21 '15 at 16:34

Madbreaks

19,094
7
58
72

1

I'm familiar with the method. Can I use `bindParam` on a variable that doesn't exist (i.e. if the user hasn't searched for a title, by binding a value to the `:title` parameter will it still search correctly)? – Ben Jan 21 '15 at 16:35
Why would you do that? The idea is, if the user did not search by title (e.g. `$_GET['title'];` is empty) then don't bind that param, nor include it in the query at all. – Madbreaks Jan 21 '15 at 16:38
1

I see what you mean - having the query expanded (`if($_GET['location']!='') $query .= ' AND `location`=:location';`), then preparing (`$stmt=$dbh->prepare($query);`) and finally binding (`if($_GET['location']!='') $stmt->bindParam(':location',$_GET['location']);`) before executing works. However, the wildcard `:title` won't work correctly with this. What is the correct method of using `LIKE` and `%` with the PDO statement? – Ben Jan 21 '15 at 16:49

score 1 · Answer 3 · answered Jan 21 '15 at 16:53

Instead of concatenating, which is verbose and error-prone, populate two arrays - placeholders and params - as you go, and insert all placeholders at once at the end:

$where  = array();
$params = array();

if(!empty($_GET['title'])) {
    $where  []= '`title` LIKE ?';
    $params []= '%' . $_GET['title'];
}

if(!empty($_GET['location'])) {
    $where  []= '`location` = ?';
    $params []= $_GET['location'];
}

// etc

if(!count($where))
    // error

$where = implode(' AND ', $where);
$query = "SELECT * FROM `jobs` WHERE $where ORDER BY `date` DESC";

$stmt=$dbh->prepare($query);
$stmt->execute($params);

Constructing PHP/MySQL search query with variable options

3 Answers3