By drudev on

Hi every body
Could any one help me
actualley i have devloped my own module with own search hook
and put the following query
$result = pager_query("SELECT person_id,uid, firstname,lastname,country,city FROM {patiants} p INNER JOIN {person} c ON p.person_id = c.id WHERE LOWER(story) LIKE LOWER('%s%') OR
LOWER(firstname) LIKE LOWER('%s%')OR LOWER(lastname) LIKE LOWER('%s%')OR LOWER(country) LIKE LOWER('%s%')OR LOWER(city) LIKE LOWER('%s%') OR LOWER(diagnosis) LIKE LOWER('%s%')", 15, 0, NULL, $keys);
person and patiant tables in the database
but the problem is what ever the input
the result is all records in the patiants table
i don't know what is the problem
could any one help me
regards

⋅ Categories: Drupal 6.x

Comments

drudev’s picture

drudev commented

Maybe because there is more than or condition in the query

arcaneadam’s picture

arcaneadam commented 
$result = pager_query("SELECT person_id,uid, firstname,lastname,country,city FROM {patiants} p INNER JOIN {person} c ON p.person_id = c.id WHERE LOWER(story) LIKE LOWER('%s%') OR
LOWER(firstname) LIKE LOWER('%s%')OR LOWER(lastname) LIKE LOWER('%s%')OR LOWER(country) LIKE LOWER('%s%')OR LOWER(city) LIKE LOWER('%s%') OR LOWER(diagnosis) LIKE LOWER('%s%')", 15, 0, NULL, $keys);

Are you trying to wrap the entire like statements in wildcards i.e. LIKE LOWER(%test%) or just LOWER(test%) because right now now drupal isn't reading it as either. If you are using the percentage sign in a sql query you MUST use it twice to escape it from the _db_query_callback function. So if you wanted to write a like statement you would need to do it like this

LIKE LOWER(%%%s%%) // becomes LIKE LOWER(%test%)
Or
LIKE LOWER(%s%%) // becomes LIKE LOWER(test%)

Adam A. Gregory
_____________________________
Drupal Developer and Consultant
https://goo.gl/QSJjb2
Acquia Certified Developer-Back end Specialist

drudev’s picture

drudev commented

thank you arcaneadam the problem is solved
for un obvious reason the query don't accept more than one (or) in where cluase
so i digest my query and got the right results.
beside your suggstion
thank you again for this reply

tjkoblentz’s picture

tjkoblentz commented

Did you even attempt his suggestion of escaping the _db_query_callback function and maintaining your multi-condition query?

drudev’s picture

drudev commented

Hi
As i undrstand from your question
actually the main problem was in the condition (or condition in where cluase ) even when i tried arcaneadam solution
I hope i answer your question .