Jump to content

insert one row per day between two dates.


russed

Recommended Posts

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. 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
**************************************/
Link to comment
Share on other sites

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>";
			}
		}
	} 
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.