Explode data from MySQL field and compare with string

Question

I have a problem with data in MySQL. I have a column "vacation_period" where I keep data with dates separated by commas eg. "02/10/2015,02/11/2015" and I want to explode commas, month and year. I want to make condition where I can compare month and year sent by POST with exploded data from MySQL field.

Example query:

SELECT * 
FROM ag_vacations INNER JOIN
     ag_employees
     ON ag_vacations.user_id = ag_employees.e_id 
where ag_vacations.user_id = 1 AND 
      ag_vacations.vacation_period = 2015 AND 
      ag_vacations.vacation_period = 02
order by date_added desc

its never a good idea to store imploded values in database and try to condition it, btw i would recomment to store "dates" as date or datetime fields in mysql — donald123
– donald123, Commented Jan 30, 2015 at 14:54
You shouldn't keep a column called vacation_perdiod with a comma-delimited list of dates. You should instead have a separate table, called a junction table, with one row per whatever (employee ?) and vacation date. The date should be stored in the database as a proper date and not as a string. — Gordon Linoff
– Gordon Linoff, Commented Jan 30, 2015 at 14:54

Alex · Accepted Answer · 2015-01-30 16:01:16Z

1

So try this way:

SELECT * 
FROM ag_vacations INNER JOIN
     ag_employees
     ON ag_vacations.user_id = ag_employees.e_id 
where ag_vacations.user_id = 1 AND 
      ((ag_vacations.vacation_period REGEXP '[0-9]{2}\/[0-9]{2}\/2015\,[0-9]{2}\/[0-9]{2}\/[0-9]{4}' AND 
      ag_vacations.vacation_period REGEXP '02\/[0-9]{2}\/[0-9]{4}\,[0-9]{2}\/[0-9]{2}\/[0-9]{4}')
     OR (ag_vacations.vacation_period REGEXP '[0-9]{2}\/[0-9]{2}\/[0-9]{4}\,[0-9]{2}\/[0-9]{2}\/2015' AND 
      ag_vacations.vacation_period REGEXP '[0-9]{2}\/[0-9]{2}\/[0-9]{4}\,02\/[0-9]{2}\/[0-9]{4}'))
order by date_added desc

so if you need to check just first date in that string 02/10/2015,02/11/2015 - you could delete OR part:

 OR (ag_vacations.vacation_period REGEXP '[0-9]{2}\/[0-9]{2}\/[0-9]{4}\,[0-9]{2}\/[0-9]{2}\/2015' AND 
          ag_vacations.vacation_period REGEXP '[0-9]{2}\/[0-9]{2}\/[0-9]{4}\,02\/[0-9]{2}\/[0-9]{4}')

edited Jan 30, 2015 at 16:01

answered Jan 30, 2015 at 15:50

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ron Smith · Accepted Answer · 2015-01-30 15:37:37Z

While a string of dates is not ideal, we sometimes receive data this way. If you need to query this before you have the opportunity to clean up your schema, and if your date formats are consistent, you can query the string with like. In this query, we will concat commas to the beginning and end of the ag_vacations.vacation_period column to make sure we are getting the beginning of the date for month and the end of the date for year:

SELECT * 
FROM ag_vacations INNER JOIN
     ag_employees
     ON ag_vacations.user_id = ag_employees.e_id 
where ag_vacations.user_id = 1 AND 
      concat(ag_vacations.vacation_period,',') like '%/2015,%' AND 
      concat(',',ag_vacations.vacation_period like '%,02/%'
order by date_added desc

dcampoy · Accepted Answer · 2015-01-30 15:09:06Z

0

If you just want to find the row, you don't need to explode in php.You can use FIND_IN_SET

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

answered Jan 30, 2015 at 15:09

dcampoy

338 bronze badges

Collectives™ on Stack Overflow

Explode data from MySQL field and compare with string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related