insert one row per day between two dates.

[russed]

I am looking into a ROTA for my work and I have a function that inserts data about annual leave request. This was one line per request and included a start and end date recorded as UNIX time.

However i have now worked out that i need to record each day as a seprate entry in the mysql table, so there will not be a start or end date just a date effected, still unix.

This will require a loop to add a entry per day between the start and end date. i have never used a loop so any help would be appriciated. 

this is the function i currently have. 

 

private function Leave() {
if(isset($_POST["Leave"]) && !empty($_POST['timestampS']) && !empty($_POST['timestampE'])) {
$this->user = $this->db->real_escape_string($_POST['user']);
$this->leave_start = strtotime($_POST['timestampS']);
$this->leave_end = strtotime($_POST['timestampE']);
$this->leave_type = $this->db->real_escape_string($_POST['Leave']);
$this->leave_now = strtotime($_POST['now']); 
$this->leave_tm = $this->db->real_escape_string($_POST['TM']);
$query_leave_insert = $this->db->query("
INSERT INTO LEAVE (Unique_Id, User_id, start_date, end_date, status, date_added, added_by, verifying_tm)  VALUES
('NULL', '".$this->user."', '".$this->leave_start."', '".$this->leave_end."', '".$this->leave_type."', '".$this->leave_now."', '1', '".$this->leave_tm."')
");
           if($query_leave_insert) {
$this->messages[] = "<p>Leave successfully updated</p>";
} else{
$this->errors[] = "<p>Sorry there was a problem, Please try again</p>";
}

}
}

The leave start and end timestampS and timestampE are populated by the user using a java calendar in a html textbox. the leave type is a dropdown box and is a number between 10-19. The rest are hidden boxes populated by stored session details, all with a submit button. 

 

[rwhite35]

So this looks like a method in a larger class, correct?  Depending on how you are passing the start the end timestamps will determine what kind of looping function you can use.  If you were to pass a parameter like this:

<?php 
//set by the user
$params = array($_POST['timestampS'],$_POST['timestampE']);

$yourObj = new yourClass($params);
$yourObj->interfaceMethod();

Then inside your class:

public function interfaceMethod() {
 foreach ($this->params as $k=>$v) {  // set $this->params with a constructor so it has global scope
    $result = $this->Leave($k);   //where $k is either timestampS or timestampE
  }
  return $result;
}

You'll then change the Leave method to something like

private function Leave($key){
//private function Leave(){

if (in_array($key, $_POST) { //returns true on a match
//if(isset($_POST["Leave"]) && !empty($_POST['timestampS']) && !empty($_POST['timestampE'])) {

The rest can remain largely the same, except where youve changed the table and query ti reflect the change in start and end columns. 

 

This is all pseudo code, but what important is you need a method that loops through your parameters and calls the private method Leave with each timestamp.  Hope that helps.

Edited June 13, 2013 by rwhite35

[rwhite35]

Oops sorry, use array_key_exists, not in_array.  Was going off the top of my head. So:

if (array_key_exists($key, $_POST)) {    //returns true on a match

[Barand]

Easiest way to generate a range of date is to use DateTime and DatePeriod objects

$timestampS = strtotime('2013-06-01'); // create unix timestamps
$timestampE = strtotime('2013-06-14');

$dt1 = new DateTime();
$dt1->setTimestamp($timestampS);

$dt2 = new DateTime();
$dt2->setTimestamp($timestampE);
$dt2->modify('+1 days'); // increment to include last day in the date period

//create datePeriod object
$dp = new DatePeriod($dt1, new DateInterval('P1D'), $dt2);

// loop through the dates in the datePeriod
foreach ($dp as $date) {
    // these are the dates to enter in the database
    echo $date->format('Y-m-d') . '<br>';
}

/* results ***************************
2013-06-01
2013-06-02
2013-06-03
2013-06-04
2013-06-05
2013-06-06
2013-06-07
2013-06-08
2013-06-09
2013-06-10
2013-06-11
2013-06-12
2013-06-13
2013-06-14
**************************************/

[russed]

Thanks for the assistance have got it working. I have attached the finished function.

	private function Leave() {
		if(isset($_POST["Leave"]) && !empty($_POST['timestampS']) && !empty($_POST['timestampE'])) {
			$this->user = $this->db->real_escape_string($_POST['user']);
			$this->leave_start = strtotime($_POST['timestampS']);
			$this->leave_end = strtotime($_POST['timestampE']);
			$this->leave_type = $this->db->real_escape_string($_POST['Leave']);
			$this->leave_now = $this->db->real_escape_string($_POST['now']);					
			$this->leave_tm = $this->db->real_escape_string($_POST['TM']);
			$start_range = $this->leave_start;
			$end_range = $this->leave_end + 86400; // add one day so end of year is correct
			$this->error_msg = 0;
			for ($leave_date=$start_range; $leave_date < $end_range; $leave_date = $leave_date + 86400) {
			$sql = "INSERT INTO `LEAVE` (`User_id`, `date_effected`, `status`, `date_added`, `added_by`, `verifying_tm`) VALUES 
			(".$this->user.", ".$leave_date.", ".$this->leave_type.", ".$this->leave_now.", 1, ".$this->leave_tm.");";
			$result = $this->db->query($sql);
			if ($this->db->affected_rows != 1) $this->error_msg = $this->error_msg + 1;
			}
			if ($this->error_msg == 0 ) {
			$this->messages[] = "<p>Leave successfully updated</p>";
			} else{
			$this->errors[] = "<p>Sorry there was a problem, " . $this->error_msg . " records failed.<br>Please try again</p>";
			}
		}
	}