By alynner on

I think the functionality of using %s and %d in queries screwing up my custom query:

SELECT u.uid, u.name, u.access FROM users as u , profile_values as p WHERE  u.access >= 0 AND u.uid != 0  AND p.uid = u.uid AND p.fid = 5 AND ( p.value like '%skiing%' || p.value like '%biking%' ) ORDER BY u.access DESC

It seems to return people that like biking, but not skiing. I'm doing it this way because people could enter "biking, hiking, running" for example, so it is searching for matches using the % on either side.

Any suggestions?

alynner

Comments

andrewlevine’s picture

andrewlevine commented

Use %%skiing%% and %%biking%%. %% will be substituted with a literal % by db_query.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

You might want to look at how it's done in taxonomy_select_nodes.

Nancy W.
Drupal Cookbook (for New Drupallers)
Adding Hidden Design or How To notes in your database

NancyDru

caligari’s picture

caligari commented

db_query("SELECT u.uid, u.name, u.access FROM users as u , profile_values as p WHERE u.access >= 0 AND u.uid != 0 AND p.uid = u.uid AND p.fid = %d AND ( p.value like '%s' || p.value like '%s' ) ORDER BY u.access DESC", 5, '%skiing%', '%biking%');

alynner’s picture

alynner commented

Thanks, %% worked great, but for keeping within drupal guidelines I will try this.

cheers
alynner