代码之家 › 专栏 › 技术社区 › Mawg says reinstate Monica

在PHP中将布尔表达式解析为MySql查询-第2部分

parsing mysql php

Mawg says reinstate Monica · 技术社区 · 4 年前

我正在为一个朋友编写一个应用程序,SQL不是我的强项。我以为这件事已由我父亲决定了 previous question ,得到了极好的回答。

然而,我的朋友又一次移动了球门柱(并且发誓这次是最后一次)。

mysql> describe skill_names;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| skill_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| skill_name | char(32) | NO   | MUL | NULL    |                |
+------------+----------+------+-----+---------+----------------+

mysql> describe skill_usage;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| skill_id | int(11) | NO   | MUL | NULL    |       |
| job_id   | int(11) | NO   | MUL | NULL    |       |
+----------+---------+------+-----+---------+-------+

mysql> describe jobs;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| job_id       | int(11) | NO   | PRI | NULL    | auto_increment |
| candidate_id | int(11) | NO   | MUL | NULL    |                |
| company_id   | int(11) | NO   | MUL | NULL    |                |
| start_date   | date    | NO   | MUL | NULL    |                |
| end_date     | date    | NO   | MUL | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

一些例子可能是

C级
C或C++
C++与UML

我想用PHP解析它并生成一个适当的SQL查询。与我的答案大致相同的东西 ,但需要调整。

以前,我认为如果用户输入两个技能,并将它们和例如 C++ and UML ,然后我应该返回使用这两种技能的任何工作的详细信息。

我想调整我上一个问题答案中的代码来实现这一点。我想知道我是否会最终用PHP完成整个工作,使用一堆嵌套FOR循环,而不是将其卸载到SQL引擎上。

更新

我找不到一个同时支持PHP和MySQL的好的fiddle站点。

如果有人想在localhost上测试自己的代码,测试数据库的主要部分如下所示:

测试搜索 3.1.5 (Python AND UML) OR (C++ OR UML)

Candidate name  Company Job year    Skills     Overall match
One             Thales      2015    C           No
One             BAe         2016    Python      No
One             Google      2017    C++         No
Two             BAe         2015    C++         Yes
Two             Google      2020    Python      Yes
Two             Google      2011    C++, UML    Yes
Three           Thales      2019    Python, UML Yes

CREATE TABLE `candidates` (
  `candidate_id` INT(11) NOT NULL AUTO_INCREMENT,
  `candidate_name` CHAR(50),
  `candidate_city` CHAR(50),
  `latitude` DECIMAL(11,8),
  `longitude` DECIMAL(11,8),
     PRIMARY KEY (candidate_id));

CREATE TABLE `companies` (
  `company_id` INT(11) NOT NULL AUTO_INCREMENT,
  `company_name` CHAR(50) NOT NULL,
  `company_city` CHAR(50) NOT NULL,
  `company_post_code` CHAR(50) NOT NULL,
  `latitude` DECIMAL(11,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
     PRIMARY KEY (company_id));

CREATE TABLE `jobs` (
  `job_id` INT(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` INT(11) NOT NULL,
  `company_id` INT(11) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
     PRIMARY KEY (job_id));


CREATE TABLE `skill_names` (
  `skill_id` INT(11) NOT NULL AUTO_INCREMENT,
  `skill_name` CHAR(32) NOT NULL,
     PRIMARY KEY (skill_id));


CREATE TABLE `skill_usage` (
  `skill_id` INT(11) NOT NULL,
  `job_id` INT(11) NOT NULL);

INSERT INTO `skill_names` (skill_name) VALUES("C");
INSERT INTO `skill_names` (skill_name) VALUES("Python");
INSERT INTO `skill_names` (skill_name) VALUES("C++");
INSERT INTO `skill_names` (skill_name) VALUES("UML");

INSERT INTO `candidates` (candidate_name, candidate_city, latitude, longitude ) VALUES("One",   "Hastings",   50.8543, 0.5735);
INSERT INTO `candidates` (candidate_name, candidate_city, latitude, longitude ) VALUES("Two",   "Slough",  51.5105, 0.5950);
INSERT INTO `candidates` (candidate_name, candidate_city, latitude, longitude ) VALUES("Three", "Stonehenge", 51.1789, -1.8262);

INSERT INTO `companies` (company_name, company_city, company_post_code, latitude, longitude ) VALUES("Thales", "Crawley",   "AB1 1CD", 51.1091, -0.1872);
INSERT INTO `companies` (company_name, company_city, company_post_code, latitude, longitude ) VALUES("BAe",    "Rochester", "EF1 2GH", 51.3880, 0.5067);
INSERT INTO `companies` (company_name, company_city, company_post_code, latitude, longitude ) VALUES("Google", "East Ham",  "E6  0XX", 51.5334, 0.0499);

INSERT INTO `jobs` (candidate_id, company_id, start_date, end_date ) VALUES(1, 1, "2015-01-010", "2015-12-31");
INSERT INTO `jobs` (candidate_id, company_id, start_date, end_date ) VALUES(1, 2, "2016-01-010", "2016-12-31");
INSERT INTO `jobs` (candidate_id, company_id, start_date, end_date ) VALUES(1, 3, "2017-01-010", "2017-12-31");
INSERT INTO `jobs` (candidate_id, company_id, start_date, end_date ) VALUES(2, 2, "2015-01-010", "2015-12-31");
INSERT INTO `jobs` (candidate_id, company_id, start_date, end_date ) VALUES(2, 3, "2020-01-010", "2020-12-31");
INSERT INTO `jobs` (candidate_id, company_id, start_date, end_date ) VALUES(2, 3, "2011-01-010", "2011-12-31");
INSERT INTO `jobs` (candidate_id, company_id, start_date, end_date ) VALUES(3, 1, "2019-01-010", "2019-12-31");

INSERT INTO `skill_usage` (job_id, skill_id) VALUES(1, 1);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES(2, 2);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES(3, 3);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES(4, 3);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES(5, 2);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES(6, 3);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES(6, 4);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES(7, 2);
INSERT INTO `skill_usage` (job_id, skill_id) VALUES (7, 4);

0 回复 | 直到 4 年前

JSowa 4 年前

DB fiddle中的工作查询示例: https://www.db-fiddle.com/f/rQKazPgbtGS766WEiuiXyR/0

$string = '(C AND kernel) OR (C++ AND UML)';

//Adding spaces between parentheses to accept inputs without that space as well
$string = str_replace('(', ' ( ', $string);
$string = str_replace(')', ' ) ', $string);

//Splitting input into separate strings, taken from previous question
$tokens = preg_split('/[\s]+/', $string);

$query = '';

$args = [];
$n = 0;
//Transcripting all strings separately and each skill name into FIND_IN_SET function
foreach ($tokens as $tok) {
    switch ($tok) {
        case '':  # skip empty tokens
        case ';':  # No, you should not!
        case '"':
        case "'":
        case ';':
            break;
        case '(':
            $query .= '(';
            break;
        case ')':
            $query .= ')';
            break;
        case '&':
        case 'AND':
            $query .= ' AND ';
            break;
        case '|':
        case 'OR':
            $query .= ' OR ';
            break;
        case '!':
        case 'NOT':
            $query .= ' NOT ';
            break;
        default:
            $arg = 'arg_' . $n;
            $args[$arg] = $tok;
            $query .= "FIND_IN_SET(':{$arg}', skills)";
            $n++;
            break;
    }
}

//Query grouping all of the skills used by candidates returning candidates
//with grouped column skills with HAVING clause containing all of the
//conditions they must match. So query returns only candidates who have ever
//used desired skills.
$sql = "SELECT candidate_id, GROUP_CONCAT(DISTINCT skill_names.skill_name) as skills
FROM jobs
LEFT JOIN skill_usage ON skill_usage.job_id = jobs.job_id
LEFT JOIN skill_names ON skill_names.skill_id = skill_usage.skill_id
GROUP BY candidate_id
HAVING {$query}
ORDER BY candidate_id DESC";

输出示例 (C AND kernel) OR (C++ AND UML)

SELECT candidate_id, GROUP_CONCAT(DISTINCT skill_names.skill_name) as skills
FROM jobs
    LEFT JOIN skill_usage ON skill_usage.job_id = jobs.job_id
    LEFT JOIN skill_names ON skill_names.skill_id = skill_usage.skill_id
GROUP BY candidate_id
HAVING (FIND_IN_SET('C', skills) AND FIND_IN_SET('kernel', skills)) OR (FIND_IN_SET('C++', skills) AND FIND_IN_SET('UML', skills))
ORDER BY candidate_id DESC

参数数组:

Array
(
    [arg_0] => C
    [arg_1] => kernel
    [arg_2] => C++
    [arg_3] => UML
)