Search form AND condition - Not working

Hi Folks,

My search fields,

Firstname*

Lastname*

MI - Middle initial

State*

ID

Birthday

when people will search John Bolton in any state for example AL (Alabama), all John Bolton names in only AL will show up.

So if there are 3 John Bolton with different middle MIs, they will show, for example

John A. Bolton

John W. Bolton

John O. Bolton and so on…

I used this code in my model, But it show an error,

def self.search_my_new_contact(first_name,last_name,initial,inimate_id,state,dob)

find(:all, :include=>[:address_book], :conditions => ["’( LOWER (address_books.first_name) LIKE ? AND LOWER (address_books.last_name) LIKE ? AND LOWER (address_books.state_name) LIKE ?)’ AND ‘(LOWER (address_books.initial) LIKE ? OR LOWER (address_books.inmate_id_number) LIKE ? OR LOWER (address_books.birthday) LIKE ?)’", “#{first_name.downcase}”,"#{last_name.downcase}","#{initial.downcase}", “#{inimate_id.downcase}”, “#{state.downcase}”,"#{dob.downcase}"])

end

ActiveRecord::StatementInvalid in InimateLocatorsController#index

Mysql2::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 '.state_name) LIKE 'ar'))' at line 1: SELECT `contact_books`.`id` AS t0_r0, `contact_books`.`user_id` AS t0_r1, `contact_books`.`created_at` AS t0_r2, `contact_books`.`updated_at` AS t0_r3, `contact_books`.`prison_id` AS t0_r4, `contact_books`.`active` AS t0_r5, `address_books`.`id` AS t1_r0, `address_books`.`prison_name` AS t1_r1, `address_books`.`first_name` AS t1_r2, `address_books`.`last_name` AS t1_r3, `address_books`.`inmate_id_number` AS t1_r4, `address_books`.`birthday` AS t1_r5, `address_books`.`city` AS t1_r6, `address_books`.`state_id` AS t1_r7, `address_books`.`zipcode` AS t1_r8, `address_books`.`street` AS t1_r9, `address_books`.`user_id` AS t1_r10, `address_books`.`created_at` AS t1_r11, `address_books`.`updated_at` AS t1_r12, `address_books`.`addressable_id` AS t1_r13, `address_books`.`addressable_type` AS t1_r14, `address_books`.`mobile_no` AS t1_r15, `address_books`.`avatar_file_name` AS t1_r16, `address_books`.`avatar_content_type` AS t1_r17, `address_books`.`avatar_file_size` AS t1_r18, `address_books`.`avatar_updated_at` AS t1_r19, `address_books`.`address` AS t1_r20, `address_books`.`gender` AS t1_r21, `address_books`.`fullname` AS t1_r22, `address_books`.`prison_id` AS t1_r23, `address_books`.`state_name` AS t1_r24, `address_books`.`image` AS t1_r25, `address_books`.`registration_no` AS t1_r26, `address_books`.`initial` AS t1_r27 FROM `contact_books` LEFT OUTER JOIN `address_books` ON `address_books`.`addressable_id` = `contact_books`.`id` AND `address_books`.`addressable_type` = 'ContactBook' WHERE ( ( LOWER (address_books.first_name) LIKE 'remo' AND LOWER (address_books.last_name) LIKE 'nandi' AND LOWER (address_books..state_name) LIKE 'ar'))

Rails.root: /home/ashok/projects/textbehind

Application Trace | Framework Trace | Full Trace

app/models/contact_book.rb:23:in `search_my_new_contact'
app/controllers/inimate_locators_controller.rb:15:in `index'

Request

Parameters:

 {"utf8"=>"✓",
"search_contact"=>{"first_name"=>"remo",
"initial"=>"",
"last_name"=>"nandi",
"inimate_id"=>"",
"state"=>"AR",
"dob"=>""}}

Show session dump

Show env dump

Response

Headers:

None

Please Advise.

MySQL is complaining about the double dot in this section:

WHERE ( ( LOWER (address_books.first_name) LIKE 'remo' AND LOWER
(address_books.last_name)
LIKE 'nandi' AND LOWER (address_books..state_name) LIKE 'ar'))

But I don't see how it's getting that from your code:

'( LOWER (address_books.first_name) LIKE ? AND LOWER
(address_books.last_name) LIKE ? AND LOWER (address_books.state_name)
LIKE ?)'

I only see a single dot in address_books.state_name so I'm really not
sure where the problem stems from...

Hi Folks,

My search fields,

Firstname*
Lastname*
MI - Middle initial
State*
ID
Birthday

when people will search John Bolton in any state for example AL (Alabama),
all John Bolton names in only AL will show up.
So if there are 3 John Bolton with different middle MIs, they will show, for
example
John A. Bolton
John W. Bolton
John O. Bolton and so on...

I used this code in my model, But it show an error,

def
self.search_my_new_contact(first_name,last_name,initial,inimate_id,state,dob)
  find(:all, :include=>[:address_book], :conditions => ["'( LOWER
(address_books.first_name) LIKE ? AND LOWER (address_books.last_name) LIKE ?
AND LOWER (address_books.state_name) LIKE ?)' AND '(LOWER
(address_books.initial) LIKE ? OR LOWER (address_books.inmate_id_number)
LIKE ? OR LOWER (address_books.birthday) LIKE ?)'",
"#{first_name.downcase}","#{last_name.downcase}","#{initial.downcase}",
"#{inimate_id.downcase}", "#{state.downcase}","#{dob.downcase}"])
end

ActiveRecord::StatementInvalid in InimateLocatorsController#index
...

MySQL is complaining about the double dot in this section:

WHERE ( ( LOWER (address_books.first_name) LIKE 'remo' AND LOWER
(address_books.last_name)
LIKE 'nandi' AND LOWER (address_books..state_name) LIKE 'ar'))

But I don't see how it's getting that from your code:

'( LOWER (address_books.first_name) LIKE ? AND LOWER
(address_books.last_name) LIKE ? AND LOWER (address_books.state_name)
LIKE ?)'

I only see a single dot in address_books.state_name so I'm really not
sure where the problem stems from...

I wonder whether there is a non-printing character in the source code
"LOWER (address_books.state_name)" which is appearing as the extra dot
in the sql. Try deleting and re-typing that bit to see if it makes a
difference. That is of course if you have not already realised that
the code you posted is not actually the code in the source file.

Colin

in the source given, i see something like

:conditions => ["’( LOWER (

**(address_books.birthday) LIKE ?)’", **

is theres a extra quote?

>> Hi Folks,
>>
>> My search fields,
>>
>> Firstname*
>> Lastname*
>> MI - Middle initial
>> State*
>> ID
>> Birthday
>>
>> when people will search John Bolton in any state for example AL
>> (Alabama),
>> all John Bolton names in only AL will show up.
>> So if there are 3 John Bolton with different middle MIs, they will
>> show, for
>> example
>> John A. Bolton
>> John W. Bolton
>> John O. Bolton and so on...
>>
>> I used this code in my model, But it show an error,
>>
>> def
>>
>> self.search_my_new_contact(first_name,last_name,initial,inimate_id,state,dob)
>> find(:all, :include=>[:address_book], :conditions => ["'( LOWER
>> (address_books.first_name) LIKE ? AND LOWER (address_books.last_name)
>> LIKE ?
>> AND LOWER (address_books.state_name) LIKE ?)' AND '(LOWER
>> (address_books.initial) LIKE ? OR LOWER
>> (address_books.inmate_id_number)
>> LIKE ? OR LOWER (address_books.birthday) LIKE ?)'",
>> "#{first_name.downcase}","#{last_name.downcase}","#{initial.downcase}",
>> "#{inimate_id.downcase}", "#{state.downcase}","#{dob.downcase}"])
>> end
>>
>> ActiveRecord::StatementInvalid in InimateLocatorsController#index
>> ...
> MySQL is complaining about the double dot in this section:
>
> WHERE ( ( LOWER (address_books.first_name) LIKE 'remo' AND LOWER
> (address_books.last_name)
> LIKE 'nandi' AND LOWER (address_books..state_name) LIKE 'ar'))
>
>
> But I don't see how it's getting that from your code:
>
> '( LOWER (address_books.first_name) LIKE ? AND LOWER
> (address_books.last_name) LIKE ? AND LOWER (address_books.state_name)
> LIKE ?)'
>
> I only see a single dot in address_books.state_name so I'm really not
> sure where the problem stems from...

I wonder whether there is a non-printing character in the source code
"LOWER (address_books.state_name)" which is appearing as the extra dot
in the sql. Try deleting and re-typing that bit to see if it makes a
difference. That is of course if you have not already realised that
the code you posted is not actually the code in the source file.

Colin

in the source given, i see something like

:conditions => ["'( LOWER (

   "
   '
   ( LOWER...

(address_books.birthday) LIKE ?)'",

   LIKE
   ?
   )
   '
   "

is theres a extra quote?

not that i can see