## for simple tokenisation, the terms are separated by space here.
## ###############################################################
$string = "( C AND kernel ) OR ( C++ AND UML )";
function emit_term( $tag ) {
$res = " EXISTS (
SELECT *
FROM skill_usage su
JOIN skill_names sn ON sn.skill_id = su.skill_id
WHERE su.Job_id = j.job_id
AND sn.skillname = :" . $tag . ")\n";
return $res;
}
$fixed_part ="
SELECT job_id, job_name
FROM jobs j
WHERE 1=1
AND \n" ;
# $tokens = explode( ' ' , $string ); #splits on any single space
$tokens = preg_split( '/[\s]+/' , $string ); # accepts multiple whitespace
# print_r ( $tokens );
$query = $fixed_part;
$args = array();
$num = 1;
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:
$tag = '_q' . $num ;
$query .= emit_term ( $tag );
$args[$tag] = $tok;
$num += 1;
break;
}
}
$query .= ";\n\n";
echo "Query + parameters (for PDO):\n" ;
echo $query;
print_r ( $args) ;
输出：
SELECT job_id, job_name
FROM jobs j
WHERE 1=1
AND
( EXISTS (
SELECT *
FROM skill_usage su
JOIN skill_names sn ON sn.skill_id = su.skill_id
WHERE su.Job_id = j.job_id
AND sn.skillname = :_q1)
AND EXISTS (
SELECT *
FROM skill_usage su
JOIN skill_names sn ON sn.skill_id = su.skill_id
WHERE su.Job_id = j.job_id
AND sn.skillname = :_q2)
) OR ( EXISTS (
SELECT *
FROM skill_usage su
JOIN skill_names sn ON sn.skill_id = su.skill_id
WHERE su.Job_id = j.job_id
AND sn.skillname = :_q3)
AND EXISTS (
SELECT *
FROM skill_usage su
JOIN skill_names sn ON sn.skill_id = su.skill_id
WHERE su.Job_id = j.job_id
AND sn.skillname = :_q4)
);
Array
(
[_q1] => C
[_q2] => kernel
[_q3] => C++
[_q4] => UML
)
602392714
清零编程群