0

I'm trying to allow users to view only certain records, where query variables are given by the user using a form. The form contains a datepicker and 3 select-elements where users can select multiple options, the allowed options are populated by SQL using SELECT DISTINCT.

The date selection works, but the SELECT-list only works when one value is selected. I feel that the problem is being caused by incorrect SQL on the 'results' page. I don't know how to make SQL work with a comma delimited string (example: user1, user2, user3 etc. This is my SQL query:

SELECT *
FROM exp_core
WHERE `time` >= startdate 
AND `time` <= enddate 
AND user LIKE uservar 
AND type LIKE typevar 
AND trnsact LIKE trnsactvar

uservar, typevar and trnsactvar are comma delimited form variables. Who knows how to make SQL treat these values as separate values?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Geoffrey Bergman
  • 13
  • 2
  • this can be done in mysql, but is is easier to do in php. are you using php? If not, check this out : http://stackoverflow.com/questions/11691942/mysql-iterate-through-elements-of-a-split-string-and-do-things-with-them?lq=1 – mlishn Aug 15 '12 at 13:13

1 Answers1

3

For multiple variables, you have to use the IN operator.

SELECT *
FROM exp_core
WHERE `time` >= startdate 
AND `time` <= enddate 
AND user IN (uservar) 
AND type IN (typevar) 
AND trnsact IN (trnsactvar)
Karan Punamiya
  • 8,603
  • 1
  • 26
  • 26