0

I use an SQL database with car information in combination with a great WordPress Plugin that outputs dynamic tables with the data.

The plugin also offers nice filter functions via Dropdowns that filter the content according to column values - however I struggle with one problem.

One column of the database includes the date range in which the specific car was built (e.g. 2012 - 2016). So I have many cars with different date ranges that often overlap. Therefore I have many duplicates when I open this Dropdown.

For instance:

2012 - 2016
2012 - 2018
2013 - 2014
2013 - 2017
and so on...

I want the user to be able to select from a variety of single years (e.g. 2014,2015,2016...). If a year is chosen via Dropdown (e.g. 2014), all the rows that match this range need to be shown (so for instance also the example from above where 2012 - 2016 is also a match because 2014 is within this range.

I wrote to the developer and he told me to use strings but I have no clue how to do that.

The plugin offers a possibility to fetch the data from the SQL table via $get.

I would really be happy if someone helped me with this problem.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Matthias Gengenbach
  • 1
  • 3
  • 1
    (1) Tag your question with the database you are really using. (2) One column should not contain multiple values. Please better explain your data model. – Gordon Linoff May 25 '20 at 21:07

1 Answers1

0

First normalize the schema and use two columns with integers instead of a string in one column. For example made_from for the lower year and made_to for the upper year.

Then you can simply use:

SELECT ...
       FROM ...
       WHERE made_from <= ?
             AND made_to >= ?;

(Where ? is the parameter place holder for the year the user inputs.)

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • I talked to the developer and he told me that I need an extra column in my SQL database that contains comma separated years. Right now I have the column with values like "2011 - 2015". Could somebody provide some code to add an extra column that turns 2011 - 2015 into 2011, 2012, 2013, 2014, 2015? – Matthias Gengenbach May 29 '20 at 18:22
  • @MatthiasGengenbach: I'd recommend to hire another developer then. They're advertising [really bad practices](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|193.1687). – sticky bit May 29 '20 at 20:03