find conditions issue

rubyonrails-talk
1

I am making a search to look through my database at a string that I enter into a text field. I got it to work properly if I enter in the exact title but I wanted to see if I could find database entries with similar titles.

I currently have this code that works: @find = Product.find(:first, :conditions => ["title = '#{name}'"]) So when I enter in "Rolling Rock" it finds that name in the database

What I want to do now is if I enter in "Rolling" it will still find Rolling Rock. I tried the following but it doesn't work: @find = Product.find(:first, :conditions => ["title.include? '#{name}'"])

I get this error: ActiveRecord::StatementInvalid in SearchController#results Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'include'#{name}') LIMIT 1' at line 1: SELECT * FROM `products` WHERE (title include'#{name}') LIMIT 1

Any clue how to find the entry with only the partial name?

Thanks for any help!

2

Quoting Leah Antkiewicz <lists@ruby-forum.com>:

I am making a search to look through my database at a string that I enter into a text field. I got it to work properly if I enter in the exact title but I wanted to see if I could find database entries with similar titles.

I currently have this code that works: @find = Product.find(:first, :conditions => ["title = '#{name}'"]) So when I enter in "Rolling Rock" it finds that name in the database

What I want to do now is if I enter in "Rolling" it will still find Rolling Rock. I tried the following but it doesn't work: @find = Product.find(:first, :conditions => ["title.include? '#{name}'"])

I get this error: ActiveRecord::StatementInvalid in SearchController#results Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'include'#{name}') LIMIT 1' at line 1: SELECT * FROM `products` WHERE (title include'#{name}') LIMIT 1

@find = Product.find(:first, :conditions => ["title like '?'", "%#{name}%"])

This code is untested, but I would expect to work.

Jeffrey

3

Jeffrey L. Taylor wrote:

Quoting Leah Antkiewicz <lists@ruby-forum.com>:

Rolling Rock. I tried the following but it doesn't work: @find = Product.find(:first, :conditions => ["title.include? '#{name}'"])

I get this error: ActiveRecord::StatementInvalid in SearchController#results Mysql::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'include'#{name}') LIMIT 1' at line 1: SELECT * FROM `products` WHERE (title include'#{name}') LIMIT 1

@find = Product.find(:first, :conditions => ["title like '?'", "%#{name}%"])

This code is untested, but I would expect to work.

Jeffrey

Thanks for the help Jeffrey but I still get the same error that I posted above. I altered your code to this: @find = Product.find(:all, :conditions => ["title LIKE '#{name}'"]) and got it to work when I enter the full name again but when I add in the % signs it tells me: @find = Product.find(:all, :conditions => ["title LIKE '%#{name}%'"]) malformed format string - %R

Any suggestions on why the % doesn't work?

4

Try this:

@product = Product.find(:first, :conditions => [“title like ?”, “%#{name}%”])

Hope this helps…

5

Why did you altered Jeffrey's code. He gave you a good advice

In the "Agile Web Development with Rails" you can find this: Rails doesn't parse the SQL inside a condition and so doesn't substitute the #{name}

According to the same book, you should always use the ["title = ?", name] notation as opposed to ["title = #{name}"]. It's gives you a security belt. By doing this, Active Record will try to avoid some SQl injection attack.

Christophe

6

Quoting Jeffrey L. Taylor <ror@abluz.dyndns.org>:

Quoting Leah Antkiewicz <lists@ruby-forum.com>: > I am making a search to look through my database at a string that I > enter into a text field. I got it to work properly if I enter in the > exact title but I wanted to see if I could find database entries with > similar titles. > > I currently have this code that works: > @find = Product.find(:first, :conditions => ["title = '#{name}'"]) > So when I enter in "Rolling Rock" it finds that name in the database > > What I want to do now is if I enter in "Rolling" it will still find > Rolling Rock. I tried the following but it doesn't work: > @find = Product.find(:first, :conditions => ["title.include? > '#{name}'"]) > > I get this error: > ActiveRecord::StatementInvalid in SearchController#results > Mysql::Error: You have an error in your SQL syntax; check the manual > that corresponds to your MySQL server version for the right syntax to > use near 'include'#{name}') LIMIT 1' at line 1: SELECT * FROM > `products` WHERE (title include'#{name}') LIMIT 1 >

@find = Product.find(:first, :conditions => ["title like '?'", "%#{name}%"])

This code is untested, but I would expect to work.

The inner single quote are not needed, i.e.,

@find = Product.find(:first, :conditions => ['title like ?', "%#{name}%"])

HTH,   Jeffrey

7

Gianluca Tessarolo wrote:

Try this:

@product = Product.find(:first, :conditions => ["title like ?", "%#{name}%"])

Hope this helps...

Hey thanks this totally worked!!! I really appreciate the help