Insert multiple records based on date range

[ztimer]

Hi. Im searching for a way to enter multiple records at once to mysql databased on a date range. Lets say i want to insert from date 2010-11-23 to date 2010-11-25 a textbox with some info and the outcome could look in database like below.

 

----------------------------------------------------------

| ID  |  date                |  name  |    amount  |

-----------------------------------------------------------

| 1    |  2010-11-23    |  Jhon    |        1        |

| 2    |  2010-11-24    |  Jhon    |        1        |

| 2    |  2010-11-25    |  Jhon    |        1        |

-----------------------------------------------------------

 

The reason i need the insertion to be like this is because if quering by month and by user to see vacation days then vacations that start in one month and end in another month can be summed by one month. If its in one record then it will pop up in both months.

 

Help is really welcome.

[dgoosens]

this database structure does not look very efficient to me...

 

why not like this:

ID | date_start | date_end | name | amount

??

 

[ztimer]

Actually it is currently like

ID | workername | user_id | vacationtype | comment | datestart | dateend | dateinserted | status | amountofdays

 

But the problem im having is with this table i cant get in another query the amount of days there is.

Lets say im going on a vacation and i insert date 2010-11-30 to 2010-12-04 so the table would have

ID | workername | user_id | vacationtype | comment | datestart    | dateend      | dateinserted | status | amountofdays |

1  | Jhon              |      2    |  regular        |                |2010-11-30 |2010-12-04  |  2010-11-29  |    1    |          6            |

 

Now when in anothe page i would like to get all the vacation days taken in November the amount then it would be simpler to sum the amount of

data between the dates. If i take it from current solution then it will show me the same amount in both November and december so its bad.

 

I hope i described it unerstandably

Thank you for your interest dgoosens.

 

Currently the query for the info looks like:

I did change the table names cause they are in Estonian language so its simpler to see what its for.

 

$from = "2010-10-01 00:00:00";

$to = "2010-10-31 23:59:59";

 

$query3 = "SELECT *, SUM(amountofdays) FROM vacations WHERE user_id = '".$selectedworker."' AND datestart BETWEEN '$from' AND '$to' AND vacationtype ='Põhipuhkus / regular vacation'";

$result3 = mysql_query($query3) or die(mysql_error());

 

// showing the results.

while($row3 = mysql_fetch_array($result3)){

echo "<b>P -> </b>The amont of days of vacation in this month: ". $row3['SUM(amountofdays)'];

echo "<br />";

}

[ztimer]

Is there really nobody that could help with this issue.?

[ztimer]

Hi. Just found a code that looks promising. Just thinking that it could help somebody i will shre my find. When i test it out and feel its the best there is will close this case.

 

The code is from phpbuilder.com/board/showthread.php?t=10356443

 

<?php

// sample data from user input:

$start_date = ('2008-06-01');

$end_date = ('2008-06-30');

$rate = 99;

$company_id = 11;

$car_id = 22;

 

// create values for each date:

$startTime = strtotime($start_date);

$endTime = strtotime($end_date);

$values = array();

for($time = $startTime; $time <= $endTime; $time = strtotime('+1 day', $time))

{

  $thisDate = date('Y-m-d', $time);

  $values[] = "($company_id, $car_id, $rate, '$thisDate')";

}

 

// build the actual query:

$query = sprintf(

  "INSERT INTO rates (companyID, carID, rate, date) VALUES\n%s",

  implode(",\n", $values)

);

 

// show what query would look like:

echo "<pre>$query</pre>";

 

 

Output should look like:

INSERT INTO rates (companyID, carID, rate, date) VALUES

(11, 22, 99, '2008-06-01'),

(11, 22, 99, '2008-06-02'),

(11, 22, 99, '2008-06-03'),

(11, 22, 99, '2008-06-04'),

(11, 22, 99, '2008-06-05'),

(11, 22, 99, '2008-06-06'),

(11, 22, 99, '2008-06-07'),

(11, 22, 99, '2008-06-08'),

(11, 22, 99, '2008-06-09'),

(11, 22, 99, '2008-06-10'),

(11, 22, 99, '2008-06-11'),

(11, 22, 99, '2008-06-12'),

(11, 22, 99, '2008-06-13'),

(11, 22, 99, '2008-06-14'),

(11, 22, 99, '2008-06-15'),

(11, 22, 99, '2008-06-16'),

(11, 22, 99, '2008-06-17'),

(11, 22, 99, '2008-06-18'),

(11, 22, 99, '2008-06-19'),

(11, 22, 99, '2008-06-20'),

(11, 22, 99, '2008-06-21'),

(11, 22, 99, '2008-06-22'),

(11, 22, 99, '2008-06-23'),

(11, 22, 99, '2008-06-24'),

(11, 22, 99, '2008-06-25'),

(11, 22, 99, '2008-06-26'),

(11, 22, 99, '2008-06-27'),

(11, 22, 99, '2008-06-28'),

(11, 22, 99, '2008-06-29'),

(11, 22, 99, '2008-06-30')

[mikosiko]

If i take it from current solution then it will show me the same amount in both November and december so its bad.

how that could be possible if in your select you are filtering using the datestart field?.. meaning if you run your select in November the example record will show because the datestart is in November... if you run the same query in December that record is not going to be shown because datestart is NOT in december... I don't follow... could you explain?

 

[ztimer]

If i take it from current solution then it will show me the same amount in both November and december so its bad.

how that could be possible if in your select you are filtering using the datestart field?.. meaning if you run your select in November the example record will show because the datestart is in November... if you run the same query in December that record is not going to be shown because datestart is NOT in december... I don't follow... could you explain?

 

No problem. Ill explain. Like you can see i query the data like so:

$query3 = "SELECT *, SUM(amountofdays) FROM vacations WHERE user_id = '".$selectedworker."' AND datestart BETWEEN '$from' AND '$to' AND vacationtype ='Põhipuhkus / regular vacation'";

the table allredy has the amount of days written in it. this is cause when user selects vacation there might be some days in that period that are bank holidays and then they will be automatically calculated in. Lets have a example. im going on vacation 2010-12-31 and end date will be 2011-01-02 so the system if use the option to from date to date sum then i get vacation days 3 but 1 january is a bank holiday so i should get 2 this i accomplice with some more code i have in mind but in the first i had to get the data by day in the database and then i can use my calculations to get the real number im looking for.

 

This project is for my boss. She wanted a HR software to calculate workers working percentage and some more bookkeeping info that is needed.

[mikosiko]

ok... not sure if this is going to solve your issue 100% but you could give it a try... using the table that you currently have it is supposed to count in a giving month only the vacations days relative to that month or the overlaps

 

SELECT user_id,workername datestart, dateend,
       CASE
          WHEN dateend > $from THEN SUM(amountofdays) - datediff(dateend,$from)
          WHEN datestart < $to AND dateend BETWEEN $to AND $from THEN SUM(amountofdays) + datediff($from,dateend)
          ELSE SUM(amountofdays)
       END AS monthvacdays
FROM test1
WHERE datestart BETWEEN $from AND $to OR
      dateend BETWEEN $from AND $to
GROUP BY user_id; 

 

give it a try... and if it doesn't solve what you want... maybe it could give you ideas

[ztimer]

ok... not sure if this is going to solve your issue 100% but you could give it a try... using the table that you currently have it is supposed to count in a giving month only the vacations days relative to that month or the overlaps

 

SELECT user_id,workername datestart, dateend,
       CASE
          WHEN dateend > $from THEN SUM(amountofdays) - datediff(dateend,$from)
          WHEN datestart < $to AND dateend BETWEEN $to AND $from THEN SUM(amountofdays) + datediff($from,dateend)
          ELSE SUM(amountofdays)
       END AS monthvacdays
FROM test1
WHERE datestart BETWEEN $from AND $to OR
      dateend BETWEEN $from AND $to
GROUP BY user_id; 

 

give it a try... and if it doesn't solve what you want... maybe it could give you ideas

 

Thanks a million. This did not resolve my issue but i did use this code on my other query so it was also helpful.

 

 

[ztimer]

I found a solution to my problem in the third post. So i tested the script and all worked like i liked it to.

 

For those who wanted the same thing i cleaned the code a bit.

<?php

 

$TNimi = "Jhon Smith"; // Workers Name

$PLiik = "Regular Vacation"; // Vacation Type

$PLiikk = "Some Comment"; // Comment

$AKuu = ('2010-11-25'); // Start date

$LKuu = ('2010-11-30'); // End date

$MViis = "Before vacation"; //Payment type

$SKuu = ('2010-11-24'); // Date entered

$PArv = "1"; // Number of days

 

mysql_connect ("localhost", "username", "password") or die ('Error:' . mysql_error());

mysql_select_db ("databasename");

 

$alatesaega = strtotime($AKuu);

$kuniaega = strtotime($LKuu);

$vaartused = array();

for($aeg = $alatesaega; $aeg <= $kuniaega; $aeg = strtotime('+1 day', $aeg))

{

$sisestatav_alateskuupaev = date('Y-m-d', $aeg);

$vaartused[] = "('NULL', '".$TNimi."', '".$PLiik."', '".$PLiikk."', '".$MViis."', '".$sisestatav_alateskuupaev."', '".$LKuu."', '".$PArv."')";

}

 

$paring = sprintf(

  "INSERT INTO test1 (ID, TNimi, PLiik, PLiikk, MViis, AKuu, LKuu, PArv) VALUES\n%s",

  implode(",\n", $vaartused)

);

 

//Test to see if the info is correct. Can be removed when in live action.

echo "<pre>$paring</pre>";

 

mysql_query($paring) or die ('Viga amdmete sisestamisel');

?>