How do i save a date range as individual records in database?

Question

let's say i have the following form and php that inserts data into mysql database.

<form id="" class="" action="" method="post">
<input type="text" class="" name="name" id="" value="" />
<input type="text" class="" name="date" id="" value="" />
<input type="submit" class="" name="submit" value="Submit">
</form>

<?php
if (isset($_POST['submit'])) {
    $name = mysql_real_escape_string(htmlspecialchars($_POST['name']));
    $date = mysql_real_escape_string(htmlspecialchars($_POST['date']));
    mysql_query("INSERT table SET name='$name', date='$date' ");
}
?>

each time it's filled in and submitted, one record gets inserted into database with 2 pieces of data.

here is what i would like to do next.

<form id="" class="" action="" method="post">
<input type="text" class="" name="name" id="" value="" />
<input type="text" class="" name="date" id="" value="" />
<input type="text" class="" name="date2" id="" value="" />
<input type="submit" class="" name="submit" value="Submit">
</form>

i want to add a date range. if a person fills in both date fields and submits, i want to enter as many records into the table as there are dates in the date range they picked. in each record, the same name will appear in the name field, but each record will have a different date.

so for example, if someone enters joe in the name field and enters 09-08-2014 in the first date field and enters 09-12-2014 in the second date field and clicks submit...there should be 5 new records in the table.

record 1 should have a name of joe and date of 09-08-2014, record 2 should have a name of joe and date of 09-09-2014, record 3 should have a name of joe and date of 09-10-2014, record 4 should have a name of joe and date of 09-11-2014, and record 5 should have a name of joe and date of 09-12-2014.

if the person only fills in first date field and leaves second one empty, then a single record with the name and date will be inserted into table.

how would something like that be done?

It might help to array the names like `name="date[]"` instead of `name="date2"` then loop through the `$_POST['date']` array and add depending if date is filled or not. — Rasclatt, Sep 06 '14 at 05:01

score 1 · Answer 1 · answered Sep 06 '14 at 05:12

First note: Use a better extension, in this example is mysqli_*. Yes it's possible, this can be easily done with DatePeriod Class. Example:

After form submission:

$db = new mysqli('localhost', 'username', 'password', 'database');
$stmt = $db->prepare('INSERT INTO `table_name` (`name`, `date`) VALUES (?, ?)');

if(isset($_POST['submit'])) {
    $name = $_POST['name'];
    // provided the format is mm-dd-yyyy
    $date = $_POST['date'];
    $date2 = $_POST['date2'];

    $begin = DateTime::createFromFormat('m-d-Y', $date);
    $end = DateTime::createFromFormat('m-d-Y', $date2);

    $interval = new DateInterval('P1D');
    $daterange = new DatePeriod($begin, $interval ,$end);
    foreach($daterange as $dr) {
        // ASSUMING your DATE column is DATETIME datatype
        $stmt->bind_param('ss', $name, $dr->format('Y-m-d H:i:s'));
        $stmt->execute();
    }
}

If you date1 == 09-08-2014 and date2 == 09-12-2014, then it will loop until that date, and thus creating that entries.

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

pseudocode:

$this_date = $date;

while ($this_date < $date2){ # save $name and $this_date to a new db record # increment $this_date by 1. See: adding one day to a date }

edited May 23 '17 at 11:57

Community

1
1

answered Sep 06 '14 at 05:18

acronym

71
4

How do i save a date range as individual records in database?

2 Answers2