New_Coder New_Coder - 1 month ago 6
MySQL Question

Extract all dates from date range in Mysql

I am a new coder and I am trying to create a holiday booking website a for school project.

I am using the Jquery UI date picker and I am trying to extract unavailable dates from my mysql bookings table using a date range.

My Table is

bookings


My date fields are
datein
and
dateout


Example:

Row 1

2016-12-15 to 2016-12-18

Row 2

2017-01-15 to 2017-01-18

Next Row Etc...

Desired Output

2016-12-15

2016-12-16

2016-12-17

2016-12-18

2017-01-15

2017-01-16

2017-01-17

2017-01-18


And then using PHP echo them into a json array like this:

["2016-12-15", "2016-12-16", "2016-12-17", "2016-12-18", "2017-01-15", "2017-01-16", "2017-01-17", "2017-01-18"]


Thanks you anyone that may contribute I apologise if my question is not clear but I am just learning.

Answer

I figured it out (except the array part) here is the MYSQL query.

mysql_select_db($database, $connection);
$query_rsunavaildates = "select selected_date from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v,
bookings
where selected_date between datein and dateout
ORDER BY selected_date ASC";
$rsunavaildates = mysql_query($query_rsunavaildates, $connection) or die(mysql_error());
$row_rsunavaildates = mysql_fetch_assoc($rsunavaildates);
$totalRows_rsunavaildates = mysql_num_rows($rsunavaildates);

If any one can help with the json array I would appreciate it. I can make the array echo but the format is incorrect. I need it to output ["2015-01-02", "2015-01-03", "etc"]