Hi all, I''m trying to implement a simple search box: @items = Item.find(:all, :conditions => ["description like %?% or text like %?%", @search_text, @search_text]) The problem with this is rails automatically encloses the search term in single quotes, resulting in this MySQL error: Mysql::Error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%''stuff''% or text like %''stuff''%)'' at line 1: SELECT * FROM items WHERE (description like %''stuff''% or text like %''stuff''%) Is there a simple (not necessarily elegant) way to fix this? Thanks a lot!!! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Brad Ediger
2006-Oct-25 03:05 UTC
Re: Newbie question - using sql "Like ''%...." in Find problem
Try putting the wildcards in the parameters instead of the query: @items = Item.find(:all, :conditions => ["description like ? or text like ?", "%#@search_text%", "%#@search_text%"]) On Oct 24, 2006, at 9:57 PM, Alex wrote:> > Hi all, > > I''m trying to implement a simple search box: > > @items = Item.find(:all, :conditions => ["description like %?% or > text > like %?%", @search_text, @search_text]) > > The problem with this is rails automatically encloses the search > term in > single quotes, resulting in this MySQL error: > > Mysql::Error: #42000You have an error in your SQL syntax; check the > manual that corresponds to your MySQL server version for the right > syntax to use near ''%''stuff''% or text like %''stuff''%)'' at line 1: > SELECT > * FROM items WHERE (description like %''stuff''% or text like %''stuff''%) > > Is there a simple (not necessarily elegant) way to fix this? > > Thanks a lot!!! > > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Kian Wright
2006-Oct-25 03:05 UTC
Re: Newbie question - using sql "Like ''%...." in Find proble
Alex wrote:> Hi all, > > I''m trying to implement a simple search box: > > @items = Item.find(:all, :conditions => ["description like %?% or text > like %?%", @search_text, @search_text])Try: @items = Item.find(:all, :conditions => ["description like ? or text like ?", "%#{@search_text}%", "%#{@search_text}%"]) Note that putting the % operator in the beginning essentially forces a full table scan (unless you have some sort of full text indexing perhaps) and could result in very slow searches. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Danny Burkes
2006-Oct-25 03:05 UTC
Re: Newbie question - using sql "Like ''%...." in Find proble
> @items = Item.find(:all, :conditions => ["description like %?% or text > like %?%", @search_text, @search_text]) >Try Item.find(:all, :conditions => ["description like ? or text like ?", "%#{@search_text}%", "%#{@search_text}%"]) -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---
Thanks all, putting wildcards in the parameters worked like a charm!!! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk -~----------~----~----~----~------~----~------~--~---