1

I have a mysql table with:

id --- name --- start_year --- end_year

1 --- george --- 2005 --- 2009

How do I get with a mysql query the year between this to years like:

2005
2006
2007
2008
2009

?

csabinho
  • 1,579
  • 1
  • 18
  • 28
Marcel Hanselmann
  • 21
  • 2
  • are those row or fields (the years you used for the example)? – Alberto Sinigaglia Nov 18 '19 at 23:36
  • Google for a MySQL row generator and generate a list of numbers from eg 1000 to 4000 then join the list to your table `ON list.num BETWEEN start_year AND end_year` – Caius Jard Nov 18 '19 at 23:36
  • You can checkout this link https://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql – Janardhan G Nov 18 '19 at 23:49
  • Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). – Strawberry Nov 19 '19 at 00:38

1 Answers1

1

Using any one of the answers in here that you like the look of, generate a list of numbers from eg 1000 to 4000

When you do

SELECT * FROM rowgen

You should see a list of numbers:

num
1000
1001
1002
...
3999
4000

Or whatever range will be acceptable for your dates. Maybe 2000 to 2050 will do. Anyway... then you just join the list to your table ON rowgen.num BETWEEN start_year AND end_year

SELECT *
FROM
  yourtable t
  INNER JOIN
  rowgen r
  ON r.num BETWEEN t.start_year AND t.end_year

You'll get:

1, George, 2005, 2009, 2005
1, George, 2005, 2009, 2006
1, George, 2005, 2009, 2007
1, George, 2005, 2009, 2008
1, George, 2005, 2009, 2009

The last column being the generated row numbers

Caius Jard
  • 72,509
  • 5
  • 49
  • 80