Jump to content

[SOLVED] get dates between two dates, then add to table

Dragen

By Dragen
in PHP Coding Help

 Share

Recommended Posts

Dragen Advanced Member

Dragen

Posted
Posted

Hi,

I've got a form that inserts data into a database. Basically what it sends is a sets of dates. What I'm wanting is another set of inputs that also sends some dates.

But before it adds them to the database I want to query the dates and find all of the dates between the two given ones and add them to the database as well.

Here's the form:

<form name="adddates" method="post" action="editbooking.php">
		<input type="hidden" name="addrem" value="add" />
		Start: <select name="day0" size="1">
			<option value="" selected="selected">day</option>
			<option value="1">1</option>
			<option value="2">2</option>
			<option value="3">3</option>
			<option value="4">4</option>
			<option value="5">5</option>
			<option value="6">6</option>
			<option value="7">7</option>
			<option value="8">8</option>
			<option value="9">9</option>
			<option value="10">10</option>
			<option value="11">11</option>
			<option value="12">12</option>
			<option value="13">13</option>
			<option value="14">14</option>
			<option value="15">15</option>
			<option value="16">16</option>
			<option value="17">17</option>
			<option value="18">18</option>
			<option value="19">19</option>
			<option value="20">20</option>
			<option value="21">21</option>
			<option value="22">22</option>
			<option value="23">23</option>
			<option value="24">24</option>
			<option value="25">25</option>
			<option value="26">26</option>
			<option value="27">27</option>
			<option value="28">28</option>
			<option value="29">29</option>
			<option value="30">30</option>
			<option value="31">31</option>
		</select>
		<select name="month0" size="1">
			<option value="" selected="selected">month</option>
			<option value="1">Jan</option>
			<option value="2">Feb</option>
			<option value="3">Mar</option>
			<option value="4">Apr</option>
			<option value="5">May</option>
			<option value="6">Jun</option>
			<option value="7">Jul</option>
			<option value="8">Aug</option>
			<option value="9">Sep</option>
			<option value="10">Oct</option>
			<option value="11">Nov</option>
			<option value="12">Dec</option>
		</select>
		<select name="year0" size="1">
			<option value="" selected="selected">year</option>
			<option value="<?php echo date(Y); ?>"><?php echo date(Y); ?></option>
			<option value="<?php echo date(Y)+1; ?>"><?php echo date(Y)+1; ?></option>
		</select><br />
		End: <select name="day1" size="1">
			<option value="" selected="selected">day</option>
			<option value="1">1</option>
			<option value="2">2</option>
			<option value="3">3</option>
			<option value="4">4</option>
			<option value="5">5</option>
			<option value="6">6</option>
			<option value="7">7</option>
			<option value="8">8</option>
			<option value="9">9</option>
			<option value="10">10</option>
			<option value="11">11</option>
			<option value="12">12</option>
			<option value="13">13</option>
			<option value="14">14</option>
			<option value="15">15</option>
			<option value="16">16</option>
			<option value="17">17</option>
			<option value="18">18</option>
			<option value="19">19</option>
			<option value="20">20</option>
			<option value="21">21</option>
			<option value="22">22</option>
			<option value="23">23</option>
			<option value="24">24</option>
			<option value="25">25</option>
			<option value="26">26</option>
			<option value="27">27</option>
			<option value="28">28</option>
			<option value="29">29</option>
			<option value="30">30</option>
			<option value="31">31</option>
		</select>
		<select name="month1" size="1">
			<option value="" selected="selected">month</option>
			<option value="1">Jan</option>
			<option value="2">Feb</option>
			<option value="3">Mar</option>
			<option value="4">Apr</option>
			<option value="5">May</option>
			<option value="6">Jun</option>
			<option value="7">Jul</option>
			<option value="8">Aug</option>
			<option value="9">Sep</option>
			<option value="10">Oct</option>
			<option value="11">Nov</option>
			<option value="12">Dec</option>
		</select>
		<select name="year1" size="1">
			<option value="" selected="selected">year</option>
			<option value="<?php echo date(Y); ?>"><?php echo date(Y); ?></option>
			<option value="<?php echo date(Y)+1; ?>"><?php echo date(Y)+1; ?></option>
		</select>
		<input type="submit" name="submit" value="submit" />
	</form>

At the moment it submits two dates into the table. One for each input.

This is what's in databaseedit.php to send the data to the database:

$day0 = $_POST['day0'];
$month0 = $_POST['month0'];
$year0 = $_POST['year0'];
$day1 = $_POST['day1'];
$month1 = $_POST['month1'];
$year1 = $_POST['year1'];

if($_POST['addrem'] == 'add'){
mysql_query("INSERT INTO booked (day, month, year) VALUES ('$day0', '$month0', '$year0')");
mysql_query("INSERT INTO booked (day, month, year) VALUES ('$day1', '$month1', '$year1')");
echo "<p align=\"left\">Your information has been successfully added to the database.<br />";
echo "<a href=\"javascript:history.back()\">go back</a>";
}

 

How can I get the dates between the two ones entered, then add them to the database as well?

 

Thanks

Link to comment
Share on other sites
Dragen Advanced Member

Dragen

Posted
  • Author
Posted

Okay I've just added this to the top of editbooking.php, which saves the start and end entries as mktime().

$day0 = $_POST['day0'];
$month0 = $_POST['month0'];
$year0 = $_POST['year0'];
$day1 = $_POST['day1'];
$month1 = $_POST['month1'];
$year1 = $_POST['year1'];

$start = mktime(0, 0, 0, $month0, $day0, $year0);
$end = mktime(0, 0, 0, $month1, $day1, $year1);

I'm hoping that I can use a method of getting the times and finding all the dates between them..

can anyone help?

Link to comment
Share on other sites
craygo Member

craygo

Posted
Posted

I think you are going to have some problems when you get into month overlaps. You are better off keeping the dates in the database AS dates and breaking them up when needed. but here is a function you can use to get the number of days in between 2 dates

 

<?php
function days($month1, $day1, $year1, $month2, $day2, $year2){
$date1 = gregoriantojd($month1, $day1, $year1);
$date2 = gregoriantojd($month2, $day2, $year2);
$diff = abs($date1-$date2);;
return $diff;
}
?>

 

now you can loop through the dates

<?php
$days = days($month0, $day0, $year0, $month1, $day1, $year1);
for($i=0; $i<$days; $i++){
$day = $day0+$i;
mysql_query("INSERT INTO booked (day, month, year) VALUES ('$day', '$month0', '$year0')");
}
?>

 

Like I said you will run into a problem with this when you get towards the end of the month. You can add a check to advance the month when it gets to the end but that will be alot of code since months end on different days. I can write something for you that would work better but you would be better of storing your dates in the table correctly.

 

Ray

Link to comment
Share on other sites
obsidian Member

obsidian

Posted
Posted

Yes

 

Lets say you have 2 date

 

04/28/2007 - 05/05/2007

 

That would be 8 days apart. if you keep adding one to the day you would end up with 04/36/2007 which will not work.

 

Ray

 

If you use mktime() or strtotime() to process your dates and then assign a format, you don't have to worry about that. Here's something I would use:

<?php
$start = "04/28/2007";
$end   = "05/05/2007";
$day   = 60 * 60 * 24;

$stTs = strtotime($start);
$edTs = strtotime($end);

while ($stTs <= $edTs) {
  list($y, $m, $d) = explode('-', date('Y-m-d', $stTs));
  mysql_query("INSERT INTO booked (day, month, year) VALUES ('$d', '$m', '$y')");
  $stTs += $day; // increment by one day
}
?>

 

I would recommend that you take advantage of the MySql date types, though. This would really save you some headache as you work through some of these issues.

 

Good luck.

Link to comment
Share on other sites
craygo Member

craygo

Posted
Posted

Now if you had your dates set up in the table as an actual date, and you had your form let the person put a start and end date in like

 

startdate: 04/28/2007  enddate: 05/05/2007  other formats will be acceptable ie: 04-28-2007, 2007-04-28

 

or you can keep the form the way it is and put the dates back together

 

you could use this

<?php
function days($date1, $date2){
$month1 = date("m", strtotime($date1));
$day1 = date("d", strtotime($date1));
$year1 = date("Y", strtotime($date1));
$month2 = date("m", strtotime($date2));
$day2 = date("d", strtotime($date2));
$year2 = date("Y", strtotime($date2));
$first = gregoriantojd($month1, $day1, $year1);
$second = gregoriantojd($month2, $day2, $year2);
$diff = abs($first-$second);
return $diff;
}
/*  Uncomment below to use your current form */
// $date1 = date("Y-m-d", mktime(0, 0, 0, $_POST['month0'], $_POST['day0'], $_POST['year0']));
// $date2 = date("Y-m-d", mktime(0, 0, 0, $_POST['month1'], $_POST['day1'], $_POST['year1']));

/* comment below to use code above */
$date1 = "2007-04-28";
$date2 = "2007-05-05";

$days = days($date1, $date2);
for($i=0; $i<$days; $i++){
$newdate = date("Y-m-d", strtotime("$date1 +$i days"));
echo "INSERT INTO booked (day, month, year) VALUES ($newdate)<br />";
}
?>

 

I just echo'd out the sql statement so you could see what it is doing.

 

Ray

Link to comment
Share on other sites
Dragen Advanced Member

Dragen

Posted
  • Author
Posted

hi,

Thanks for all the help! This is the code I've got:

function days($date1, $date2){
$month0 = date("m", strtotime($date1));
$day0 = date("d", strtotime($date1));
$year0 = date("Y", strtotime($date1));
$month1 = date("m", strtotime($date2));
$day1 = date("d", strtotime($date2));
$year1 = date("Y", strtotime($date2));
$first = gregoriantojd($month0, $day0, $year0);
$second = gregoriantojd($month1, $day1, $year1);
$diff = abs($first-$second);
return $diff;
}

$date1 = date("Y-m-d", mktime(0, 0, 0, $_POST['month0'], $_POST['day0'], $_POST['year0']));
$date2 = date("Y-m-d", mktime(0, 0, 0, $_POST['month1'], $_POST['day1'], $_POST['year1']));

if($_POST['addrem'] == 'add'){
$days = days($date1, $date2);
for($i=0; $i<$days; $i++){
$newdate = date("Y-m-d", strtotime("$date1 +$i days"));
mysql_query("INSERT INTO booked (day, month, year) VALUES ($newdate)");
echo "INSERT INTO booked (day, month, year) VALUES ($newdate)";
}
echo "<p align=\"left\">Your information has been successfully added to the database.<br />";
echo "<a href=\"javascript:history.back()\">go back</a>";
}

Notice that after I've done the mysql_query() I've also echoed the same query. This is because I'm getting strange results.

When I pass the form through this it doesn't seem to be adding anything to the database... although when I echo the mysql_query() it reads perfectly as a working statement, so I don't see why it's not working.. here's an example of the echo:

INSERT INTO booked (day, month, year) VALUES (2007-01-01)INSERT INTO booked (day, month, year) VALUES (2007-01-02)INSERT INTO booked (day, month, year) VALUES (2007-01-03)INSERT INTO booked (day, month, year) VALUES (2007-01-04)

another thing is, it's not adding the end date. on the above echo, it should have a last one of

INSERT INTO booked (day, month, year) VALUES (2007-01-05)

 

EDIT: I realised why it wasn't submitting the last date.. I needed to change this line:

	for($i=0; $i<$days; $i++){

to

	for($i=0; $i<=$days; $i++){

Link to comment
Share on other sites
Dragen Advanced Member

Dragen

Posted
  • Author
Posted

I think the problem is the $newdate variable.. surely I'd need it to be three variables so I can send it to the right colums in the database..

The problem I've got with changing the dates to an actual date instead of seperate day, months and year, is I'd then have to change script on all my other files which took quite a while to figure out. :-\

Link to comment
Share on other sites
obsidian Member

obsidian

Posted
Posted

I think the problem is the $newdate variable.. surely I'd need it to be three variables so I can send it to the right colums in the database..

The problem I've got with changing the dates to an actual date instead of seperate day, months and year, is I'd then have to change script on all my other files which took quite a while to figure out. :-\

 

Did you try my suggestion? I think that it might give you something more understandable to work with.

Link to comment
Share on other sites
Dragen Advanced Member

Dragen

Posted
  • Author
Posted

Did you try my suggestion? I think that it might give you something more understandable to work with.

hmm.. I should've tried your suggestion first.. It worked straight away, no problem!

Thank you so much!

 

Thanks to everyone else who helped out as well.

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.

×
 Share
Go to topic listing
×
×
  • 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.