Formatting a DBI/MySQL query from HTML form via Perl

Polyglot has asked for the wisdom of the Perl Monks concerning the following question:

I have been wrestling with this for some time now. I have a form which accepts user input for search terms and for a specific resource (MySQL table) in which to search. Is there a good resource for converting "standard" search terms and operators into a MySQL select query? Barring that, I've come up with a rather complicated regexp solution, but it still fails of working intuitively as it should, especially with the following exceptional searches:

a search with nested parentheses
a search which has been copy/pasted from a paragraph of text including all of the original punctuation
a search with no parentheses but with a combination of AND and OR operators

Currently, each of these exceptions will return either no results (MySQL error), or an improper result set due to an incorrect ordering of terms.

I'm sure someone must have dealt with this before...

Thank you for sharing your tips with us.

Blessings,

~Polyglot~

Comment on Formatting a DBI/MySQL query from HTML form via Perl

Replies are listed 'Best First'.
Re: Formatting a DBI/MySQL query from HTML form via Perl by Your Mother (Archbishop) on May 21, 2009 at 18:08 UTC
There is a bit on natural language parsing --> SQL in Object Oriented Perl. I know I've seen someone else's code that worked on the problem too but it was 5 years ago and I don't remember who/where. A casual couple of searches on the CPAN didn't turn up anything complete. If I were starting on this from scratch I'd revisit Conway's stuff (it's been years since I read it but his clarity of thought in problem solving is invaluable even if you go a different way in the end) and join the idea with SQL::Abstract. When you're done, write some tests and release it! It would great to have available. If I missed something on the CPAN that already touches this problem space, please someone mention it.	[reply]

Replies are listed 'Best First'.

Re: Formatting a DBI/MySQL query from HTML form via Perl
by Your Mother (Archbishop) on May 21, 2009 at 18:08 UTC

There is a bit on natural language parsing --> SQL in Object Oriented Perl. I know I've seen someone else's code that worked on the problem too but it was 5 years ago and I don't remember who/where. A casual couple of searches on the CPAN didn't turn up anything complete.

If I were starting on this from scratch I'd revisit Conway's stuff (it's been years since I read it but his clarity of thought in problem solving is invaluable even if you go a different way in the end) and join the idea with SQL::Abstract.

When you're done, write some tests and release it! It would great to have available. If I missed something on the CPAN that already touches this problem space, please someone mention it.

[reply]