从PHP数组编写PDO搜索查询 [英] Writing a PDO search query from a PHP array

问题描述

我正在使用PHP 7和与MySQL数据库的PDO连接来构建应用程序.

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

应用程序的一部分包含一个搜索表单，该表单允许用户通过3个不同的字段来搜索培训课程:课程类别，课程名称和日期.

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.

表单上元素的类型为:

1. 课程类别-带有数字(int)ID的下拉列表.
2. 课程名称-文本输入
3. Date-日期选择器(使用HTML 5 type="date"参数在浏览器中获取日历).

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

这些字段可以结合使用，也可以单独使用.这意味着用户可以仅按(1)或(2& 3)或全部(1& 2& 3)进行搜索.

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).

我已经编写了PHP以获取POST数据，并且它现在处于数组中-例如:

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' => ''
]

我想在PDO查询中使用它，但我不知道写它的最佳方法是什么，因为(1)和(3)将是=查询条件，而(2)是LIKE.我的解决方案是遍历搜索词，然后尝试构建一个查询，例如

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

麻烦之处在于，由于必须绑定PDO中的参数，因此无法正常工作.这也行不通，因为我找不到在每个查询之间获取AND的方法(如果有前面的语句).

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.

任何帮助将不胜感激.

我意识到对名称进行硬编码，例如($key == 'course_name')不是理想的选择，但这只是由于不同的查询条件(LIKE与=)而完成的.我认为可以使$search_data多维来说明它是哪种类型的查询，但这超出了我最初的问题，可能还超出了我的讨论范围.

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.

推荐答案

以下是您的问题的简单解决方案:

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

并且您有$pdoDate数组保存绑定的数据.

And you have $pdoDate array holding the binded data.

这篇关于从PHP数组编写PDO搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！