There was an article on forums.mysql.com (save the content, not the link) that takes, as
the in parameter, a comma delimited list of values and then breaks them down and inserts
them into a temp table. The article uses and innodb table, which I find sort of odd as a
memory table would probably be faster.
It uses this to join it to the table/columns that the search is being conducted on. This
seems to be the most common approach I've found. The problem with this approach is that
I don't know how to join a table against a column when you are looking for like data
instead of equality.
This gets me closer, but not exactly there yet.
From: Martin Gainty [mgainty@stripped]
Sent: Friday, March 27, 2009 1:58 PM
To: ben@stripped; mysql@stripped
Subject: RE: Search based where claused and stored proc
did'nt see your solution?
Disclaimer and confidentiality note
This message is confidential and may be privileged. If you are not the intended recipient,
we kindly ask you to please inform the sender. Any unauthorised dissemination or copying
hereof is prohibited. This message serves for information purposes only and shall not
have any legally binding effect. Given that e-mails can easily be subject to
manipulation, we can not accept any liability for the content provided.
> From: ben@stripped
> To: mysql@stripped
> Subject: RE: Search based where claused and stored proc
> Date: Fri, 27 Mar 2009 13:43:51 -0500
> Ben Wiechman
> Network Administrator
> Wisper High Speed Internet
> Office: 866.394.7737
> Direct: 320.256.0184
> Cell: 320.247.3224
> > -----Original Message-----
> > From: Gary Smith [mailto:Gary@stripped]
> > Sent: Friday, March 27, 2009 12:59 PM
> > To: mysql@stripped
> > Subject: [MySQL] Search based where claused and stored proc
> > I'm working on a small project of re-implementing all of the sql for a
> > web site. The task is pretty trivial but overall there are some minor
> > things that I'm trying to code through.
> > We've moved much of the logic over to stored procs and call them with
> > parameterized queries. This works well since there isn't much inject
> > attack possibility on these. Now I have one query left, which allows
> > for an arbitrary number of search parameters, all using AND.
> > Has anyone accomplished coverting something like this to a stored proc
> > in mysql?
> > Logically I could pass in the parameters in as an array of words, or a
> > wordlist to be broken up inside the proc, but I don't want to spend a
> > bunch of time either reinventing the wheel or working to a goal that
> > can't be accomplished.
> > We could build the base query dynamically in the code using standard sql
> > and bind the parameters to it that way but since we've moved everything
> > else to procs I figured I'd look into this as well.
> > BTW, this is a project I brought onto after they found they had a sql
> > injection bug in there code that was exploited...
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
Express your personality in color! Preview and select themes for Hotmail®.