How to search double quotes in fulltext search in sql?

Need PHP code to convert a boolean search string into SQL

• Well, after banging my head against the wall for a while, and thinking this will require a regular expression or a few (which are mindbending to me) I thought I'd see if someone here might be able to whip something up for this. We have a search form and we would like to allow people to enter boolean terms to help them narrow their search. So, for example the code I'm looking for would take something like this: $_POST[thefieldtosearch] would have "Running OR Walking AND Tall AND Happy NOT short" The code needs to watch for " OR ", " AND ", " NOT " and my example here should end up as SQL such as this: $sql = "SELECT * from mydb where thefieldtosearch LIKE '%Running%' OR thefieldtosearch LIKE '%Walking%' AND thefieldtosearch LIKE '%Tall%' AND thefieldtosearch LIKE '%Happy%' NOT thefieldtosearch LIKE '%short%'"; I'm sure that's not exactly correct syntax, and the query would likely need to be built piece by piece as the original string is being broken down..but that's an example of the final query as seen by the db.

• Answer:

  trancecan-ga, I have written an include file so you can easily add this functionality to your code: www.maxlin.ca/boolean-query.zip To use, simply copy the file into the directory of the script you want to use it, and add at the beginning of your file: <? include ("boolean-query.php"); ?> To use the function, call: boolstring2sql (<return fields>, <search tables>, <search fields>, <search string>) For example: echo boolstring2sql ("*","mydb","thefieldtosearch", "Running OR Walking AND Tall AND Happy NOT short"); Returns: SELECT * FROM mydb WHERE (thefieldtosearch LIKE '%running%') OR (thefieldtosearch LIKE '%walking%') AND (thefieldtosearch LIKE '%tall%') AND (thefieldtosearch LIKE '%happy%') AND (NOT thefieldtosearch LIKE '%short%'); If you only want the WHERE portion, use: boolstring2sql_query (<search fields>, <search string>) For example: boolstring2sql_query ("thefieldtosearch", "Running OR Walking AND Tall AND Happy NOT short") Returns: (thefieldtosearch LIKE '%running%') OR (thefieldtosearch LIKE '%walking%') AND (thefieldtosearch LIKE '%tall%') AND (thefieldtosearch LIKE '%happy%') AND (NOT thefieldtosearch LIKE '%short%') To specify multiple fields, use space delimiting. For example: boolstring2sql ("*","mydb","firstfield secondfield thirdfield", "Running OR Walking AND Tall AND Happy NOT short"); Returns: SELECT * FROM mydb WHERE (firstfield LIKE '%running%' OR secondfield LIKE '%running%' OR thirdfield LIKE '%running%') OR (firstfield LIKE '%walking%' OR secondfield LIKE '%walking%' OR thirdfield LIKE '%walking%') AND (firstfield LIKE '%tall%' OR secondfield LIKE '%tall%' OR thirdfield LIKE '%tall%') AND (firstfield LIKE '%happy%' OR secondfield LIKE '%happy%' OR thirdfield LIKE '%happy%') AND (NOT firstfield LIKE '%short%' OR secondfield LIKE '%short%' OR thirdfield LIKE '%short%'); It should be noted that the search string is guarded against SQL injection. Enjoy, tox-ga