Writing a PDO search query from a PHP array

Question

I'm building an application using PHP 7 and a PDO connection to a MySQL database.

One part of the application contains a search form which allows a user to search for a training course by 3 different fields: the course category, the course name, and a date.

The types of elements on the form are:

Course category - dropdown, with numerical (int) ID's.
Course name - text input
Date - date picker (using HTML 5 type="date" parameter to get a calendar in the browser).

These fields can be used in conjunction, or on their own. This means a user could search, for example, just by (1), or (2 & 3), or all (1 & 2 & 3).

I've written the PHP to get the POST data and it's now in an array - for example:

$search_data = [
    'category' => 3,
    'name' => 'Hazard training',
    'date' => ''
]

I want to use this within a PDO query but I don't know what the best way to write it is because (1) and (3) would be an = query condition, whereas (2) is a LIKE. My solution was going to be looping through the search terms and then trying to construct a query, e.g.

$sql = ' WHERE ';
foreach ($search_data as $key => $value) {
    if ($key == 'category') {
        $sql .= ' category = ' . $value;
    }
    if ($key == 'course_name') {
        $sql .= ' course_name LIKE % ' . $value ' % ';
    }
    if ($key == 'date') {
        $sql .= ' date = ' . $value;
    }
}

The trouble with this is it doesn't work because of having to bind the parameters in PDO. It also doesn't work because I can't find a way to get the AND between each query (if there is a preceding statement).

I'm lost with this now and unsure what the best way to write this is.

Any help would be appreciated.

Edit: I realise that hardcoding the names, e.g. ($key == 'course_name') isn't ideal, but this is only being done because of the different query conditions (LIKE vs =). I assume that one could make $search_data multi-dimensional to say which type of query it was, but this is beyond my initial problem and probably another post.

krasipenkov · Accepted Answer · 2016-11-10 12:07:21Z

3

Here`s a simple solution to your problem:

$sql = 'SELECT ..... FROM ... WHERE 1 ';
$where = '';
$pdoData = [];

foreach ($search_data as $key => $value) {
    if(!$value) continue; // skip empty values

    if ($key === 'category') {
        $pdoData[':category'] = $value;
        $where .= ' AND category = :category ';
    }
    if ($key === 'course_name') {
        $pdoData[':course_name'] = '%'.$value.'%';
        $where .= ' AND course_name LIKE (:course_name) ';
    }
    if ($key === 'date') {
        $pdoData[':date'] = $value;
        $where .= ' AND date = :date ';
    }
}

$sql = $sql.$where;

$stmt = $this->ci->db->prepare($sql);
$stmt->execute($pdoData);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

And you have $pdoDate array holding the binded data.

edited Nov 10, 2016 at 12:07

answered Nov 10, 2016 at 11:09

krasipenkov

2,0141 gold badge13 silver badges13 bronze badges

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

11 Comments

Veve Over a year ago

I like the WHERE 1, making the construction easier.

M. Eriksson Over a year ago

I would change the course_name part to: $pdoData[':course_name'] = '%' . $value . '%'; I don't think LIKE % :course_name % will work as intended...

JYoThI Over a year ago

use not empty if ($key === 'category' && !empty($value))

krasipenkov Over a year ago

I little improvement was made

M. Eriksson Over a year ago

You also need to escape any % in the input, otherwise, that will change the search (you won't be able to search for that character, but rather get a weird search result, since any extra % will be counted as part of the search pattern).

|

Collectives™ on Stack Overflow

Writing a PDO search query from a PHP array

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related