Converting PHP date range to MYSQL individual date

Question

I have an availability calendar in which I am currently adding in dates one by one, and using a mysql query to determine if there exists a row with a certain date and changing the class of the day to "booked" (Red).

I would like to enter in a range into my form, and process it through php (or mysql) into multiple, individual dates. My date format is M/D/YYYY, or MM/DD/YYYY, both are accepted. Unfortunately, when I built my calendar, I did not use the date format in sql for entries, but used varchar.

Is there a way to enter into my form for example 1/1/2014-1/3/2014 and have php convert that to 1/1/2014, 1/2/2014, 1/3/2014, and then have a mysql INSERT query to insert multiple values at once?

if (empty($_POST) === false && empty($errors) === true) {
$adcp_data = array(
'date'      => $_POST['date'],
'customer'  => $_POST['customer'],
'notes'     => $_POST['notes'],
            );
insert_adcp($adcp_data);
header('Location: adcp.php?success');
exit();

the insert_adcp function looks like this:

function insert_adcp ($adcp_data) {
    array_walk($adcp_data, 'array_sanitize');
    $fields = '`' . implode('`, `', array_keys($adcp_data)) . '`';
    $data = '\'' . implode('\', \'', $adcp_data) . '\'';

    mysql_query("INSERT INTO `adcp` ($fields) VALUES ($data)");

}

My workaround and last resort will be to add multiple text inputs and just add multiple dates manually so I only have to submit once. But a range is so much faster!

As a last note, if I could have those multiple entries keep the "customer" and "notes" values for each date in the range that would be amazing. I am prepared to lose those fields though to make this work. Thanks

Answer 1

Check if the value from the input match your range format, capture the parts and generate the from and to dates.

if (preg_match('%\A(?<fromMonth>\d{1,2})/(?<fromDay>\d{1,2})/(?<fromYear>\d{4})-(?<toMonth>\d{1,2})/(?<toDay>\d{1,2})/(?<toYear>\d{4})\Z%', $str, $res)) {
    $dates['from'] = mktime(0, 0, 0, $res['fromMonth'], $res['fromDay'], $res['fromYear']);
    $dates['to']   = mktime(0, 0, 0, $res['toMonth'], $res['toDay'], $res['toYear']);
}

Generate the range between from and to dates.

for ($date = $dates['from']; $date <= $dates['to']; $date = strtotime('+1 day', $date) ){
    $dates['range'][] = date('m-d-Y', $date);
}

Answer 2

I think, strtotime is more usable for your case. You can found definition at php.net site: http://php.net/manual/en/function.strtotime.php

Answer 3

Something like:

$day = new DateTime($_POST['range_start']);
$end = new DateTime($_POST['range_end']);

$all_dates = array();

while ($day <= $end){
  $all_dates[] = $day;
  $day->add(new DateInterval('P1D'));
}

That will give you an array of DateTime objects each of which represents a day in your range. You can get each object back into a string by calling DateTime::format() and passing 'm/d/Y' as the format string.

As for getting multiple entries into MySQL, the INSERT syntax allows INSERT INTO table (column) VALUES (row1), (row2), ... (rowN)

(this is clearly not not tested or the final code you would use -- just written into this web form from memory ... you'll have to write it out properly with input sanitation and range checking and whatnot.)