querying mysql columns storing comma separeated values

[raymond_feliciano]

I created a simple search box which will query my table and match the input value to one of my columns. two of these columns store comma separated values. if i query a column other than a column which stores my csv i can see my search results. if i query a column which stores my csv i will not see results unless the search value matches the first value within the column. how would i be able to get say the second or third or forth value. here is the code i am using to query the table any help would be appreciated thanks.

 

$q = $this->db->query("SELECT * FROM table WHERE col1 LIKE 'searchvalue'". 
             " OR col2 LIKE 'searchvalue'".
             " OR col3 LIKE 'searchvalue'". 
             " OR col4 LIKE 'searchvalue'".
             " OR FIND_IN_SET('searchvalue', col5) > 0 ".
             " OR FIND_IN_SET('searchvalue', col6) > 0");

[smoseley]

FIND_IN_SET won't work on a comma-delimited string.  (e.g. 'A,B,C') It works on a set of values.  (e.g. 'A', 'B', 'C')

 

Instead, use something like this:

 

AND (col5 LIKE '%,searchvalue,%' OR LEFT(col5, LENGTH(searchvalue)+1) = CONCAT(searchvalue,',') OR OR RIGHT(col5, LENGTH(searchvalue)+1) = CONCAT(',',searchvalue))

[Psycho]

You should not store comma separated data in field precisely for this reason. It is not too difficult to perform a search on such a column, but it will be difficult to impossible to do anything more advanced such as joining tables. You need to go find a tutorial or two on how to properly set up a normalized database.

 

In this case you should have an associative table for the values where there is one record per value that points back to the parent record in the current table.

[smoseley]

You should not store comma separated data in field precisely for this reason. It is not too difficult to perform a search on such a column, but it will be difficult to impossible to do anything more advanced such as joining tables. You need to go find a tutorial or two on how to properly set up a normalized database.

 

In this case you should have an associative table for the values where there is one record per value that points back to the parent record in the current table.

+1

[raymond_feliciano]

@smoseley I tried using your suggestion and I was still i was only able view results if I searched for the first value within the column. thanks for your help will keep this in mind for next time.

 

So what I did is what Psycho proposed and created two new tables. In these tables I added the values which have a foreign key pointing back to my main table. When i use this query

SELECT ci_study_history.account_num, ci_study_history.study, ci_patient_info.icd9_code, ci_patient_info.dob, ci_patient_info.sex
FROM ci_patient_info, ci_study_history
WHERE ci_patient_info.account_num = ci_study_history.account_num
AND ci_study_history.study = 'study 2'

using this I know I should get two rows back but I only get one. I also tired inner join and right join but I got back the same result. I am a newbie with mysql so I'm not sure if I am writing the query out correctly

[PFMaBiSmAd]

Umm. Find_in_set should have worked, even though you should not have a comma separated list that you want to search.

 

Since your reworked design doesn't match the rows you think it should (and your original design didn't either), it's likely that there is something about the actual data values that is preventing a match, such as a white-space character that isn't what you expect or extra white-space characters before or after the data value.

 

The query you just posted is trying to find 'study 2' (presumably - study space 2). Are you sure your data has that exact value in it? How did this data originally get inserted?

 

You would need to post a data dump of the rows you expect your query to match, for any one here to directly be able to help.

[raymond_feliciano]

@PFMaBiSmAD wow I thought when I checked my fields that I made sure all the data was correctly inserted without any extra spaces boy was I dead wrong. That was the problem I was having. Feeling really dumb right know since I have been messing with this since this morning.

Just want to thank all you guys for helping me.