Jump to content

Insert multiple records based on date range


ztimer

Recommended Posts

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.

Link to comment
Share on other sites

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 />";

}

Link to comment
Share on other sites

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')

Link to comment
Share on other sites

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

:confused: 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?

 

Link to comment
Share on other sites

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

:confused: 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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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');

?>

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.