CODEWITHSUNDEEP
phpmysql
Camrin Parnell
Camrin Parnell

Reputation: 449

Best way to search a comma separated mysql field

I have been given the task to combine 2 mysql fields into one and then make it so that the new field can be searched. The 2 fields I had to combine in my database where previous year registered and current years registered. The format both these fields are in are dd/mm/yyyy. I have combined these fields into a field called Years Registered whih is still in the same format dd/mm/yyyy but has the years registered seperated by a comma(,). I am wondering how I would go about performing a couple different kinds of querys on this column. The mysql queries I have to perform are: Show All() , Show All between dates: mm/yyyy and mm/yyyy , Before: mm/yyyy , After: mm/yyyy

Any help would be greatly appreciated.

Thanks for your time.

Upvotes: 0

Views: 600

Answers (3)

Peter Parker
Peter Parker

Reputation: 29715

Do not do this! I do not know how it is exactly possible (some SQL Stringoperations and Datefunctions in a storedprocedurem i presume), but it will surely kill performance of your database. use a relation for this.

This is:

  • way faster
  • more expandable (eg. for three dates..)
  • easier to code
  • much better understandable
  • more portable to other databases

If you have problems with existing platforms you have to support, use a code base where both alternatives are supported. This is still easier and better to maintain than to use a comma-separated list

Upvotes: 2

Tyler
Tyler

Reputation: 19848

Your database would be breaking 'First Normalized Form (1NF)' and would be highly ineffecient.

In order to search for a selected date, you would either have to query all rows in the table, or use LIKE which is also very sluggish.

Whoever is asking you to do this should read this article on database normalization.

What is wrong with using two DATE, or DATETIME fields and the formatting them outside of MySQL?

Upvotes: 0

Andrej
Andrej

Reputation: 7504

I don't like it but if you need you can use the next solution:

extract date using start_date = STR_TO_DATE(SUBSTRING(your_new_field, 1, 10)) and end_date=STR_TO_DATE(SUBSTRING(your_new_field, 12, 10))

Upvotes: 2

Related Questions