MySQL IN clause for an array of strings

rubyonrails-talk
1

I'm trying (for the first time) to build an IN clause from an array of strings and getting an error using MySQL as the DB...

Here's the code I'm using to build the clause:

    zips_clause = ''     user.get_zipcodes.each {|z|       zips_clause += ',' if !zips_clause.blank?       zips_clause += '\'' + z + '\'' # wrap string in single-quotes     }     zips_clause = '(' + zips_clause + ')'

    conditions = sanitize_sql(["updated_at > ? AND local_code IN ?", since_date, zips_clause])

This is the resulting SQL:

SELECT * FROM tips WHERE (updated_at > '2008-03-14 11:55:29' AND local_code IN '(\'94952\',\'08054\',\'11111\')')

And this is the error:

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 ''(\'94952\',\'08054\',\'11111\')')' at line 1: SELECT * FROM tips WHERE (updated_at > '2008-03-14 11:55:29' AND local_code IN '(\'94952\',\'08054\',\'11111\')')

I'm guessing that it's something about that parenthesized clause that follows the IN.

Could it be the backslash that gets included for the single-quotes around each string? And why *is* that getting inserted?

Or can anybody spot anything else wrong with that SQL statement?

If you have code that shows a different way to build a SQL IN clause from an array of strings that might help, too.

Thanks in advance.

2

I'm trying (for the first time) to build an IN clause from an array of strings and getting an error using MySQL as the DB...

Here's the code I'm using to build the clause:

    zips_clause = ''     user.get_zipcodes.each {|z|       zips_clause += ',' if !zips_clause.blank?       zips_clause += '\'' + z + '\'' # wrap string in single-quotes     }     zips_clause = '(' + zips_clause + ')'

    conditions = sanitize_sql(["updated_at > ? AND local_code IN ?", since_date, zips_clause])

a = [1,2,3,4] User.find(:all, :conditions => ["id IN (?)", a])

Results in this:

SELECT * FROM users WHERE (id IN (1,2,3,4))

3

Thanks, Phillip!

I had tried something very close to what you show me, but it didnt work... I was missing the parentheses around the ? in '(?)'.

Sometimes any docs for this are so hard to find (I'd tried googling, the Agile book, Rails Way, etc.)

Thanks again.