dlofrodloh dlofrodloh -5 years ago 113
MySQL Question

Converting a user inputted boolean search string into mySQL

I've got a database search field where I want the user to be able to input simple boolean logic and have that translated to a mySQL search string.

So for instance if the user inputs:

(php AND mysql) OR ajax

I'd like to convert that to:

((c.Skillset LIKE '%php%' AND c.Skillset LIKE %mysql%) OR c.Skillset LIKE '%ajax%')

Is there a fairly simple way of doing this? I'm having particular trouble coming up with a solution for the brackets, if it wasn't for that it would be quite straightforward.

Answer Source

The following assumes you have some constant called SQL_PREFIX_STRING defined.

$string = '(php AND mysql) OR java';
preg_match_all('/[a-z]+/', $string, $skills); // puts the skills in an array
$skills = $skills[0]; // shift array to get the matching values
array_walk($skills, function (&$skill) {$skill = '%'.$skill.'%';}); // add wildcards to parameters

$query_string = preg_replace('/[a-z]+/', 'c.Skillset LIKE ?', $string); // create the ? parameters for the SQL string

$pdoStatement = $pdo->prepare(SQL_PREFIX_STRING.$sql); // prepare
$pdoStatement->execute($skills); // execute
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download