Bruce Gust
asked on
How can I correct this query?
Here's my dilemma: I've got a CSV file that's a couple of thousand rows. Everything about it is pristine except for one thing. Anytime I've got an event that spans a couple of days it needs to be recorded in the database like this:
event_id start_date new_start_date
CARTN_1622 01/06/2015 2015-01-07
CARTN_1622 01/06/2015 2015-01-08
CARTN_2436 01/06/2015 2015-01-07
CARTN_2436 01/06/2015 2015-01-08
CARTN_2436 01/06/2015 2015-01-09
CARTN_2436 01/06/2015 2015-01-10
CARTN_2436 01/06/2015 2015-01-11
CARTN_2436 01/06/2015 2015-01-12
See what's happening? The start date on repeating events has to be adjusted. In the first example, the event starts on the sixth, I've got to change that to the seventh. The same event is listed in the next row and that new start date needs to be the 8th.
For some reason, when I run my script to make these changes, I get this:
original date new date
CARTN_1622 01/06/2015 2015-01-07
CARTN_1622 01/06/2015 2015-01-08
CARTN_2436 01/06/2015 2015-01-09
CARTN_2436 01/06/2015 2015-01-10
CARTN_2436 01/06/2015 2015-01-11
CARTN_2436 01/06/2015 2015-01-12
CARTN_2436 01/06/2015 2015-01-13
CARTN_2436 01/06/2015 2015-01-14
Bear in mind that "CARTN_1622" is different that "CARTN_2436." They both start on the sixth, but when I get to event "CARTN_2436," the first "new_date" should be the seventh and instead it's the ninth. Rather than starting over based on the original start date, for some reason my new date is increasing in a way that I don't understand.
Here's the code I'm using to try and fix the data. There's something I'm overlooking and I'm hoping you can tell me what.
What do you think?
event_id start_date new_start_date
CARTN_1622 01/06/2015 2015-01-07
CARTN_1622 01/06/2015 2015-01-08
CARTN_2436 01/06/2015 2015-01-07
CARTN_2436 01/06/2015 2015-01-08
CARTN_2436 01/06/2015 2015-01-09
CARTN_2436 01/06/2015 2015-01-10
CARTN_2436 01/06/2015 2015-01-11
CARTN_2436 01/06/2015 2015-01-12
See what's happening? The start date on repeating events has to be adjusted. In the first example, the event starts on the sixth, I've got to change that to the seventh. The same event is listed in the next row and that new start date needs to be the 8th.
For some reason, when I run my script to make these changes, I get this:
original date new date
CARTN_1622 01/06/2015 2015-01-07
CARTN_1622 01/06/2015 2015-01-08
CARTN_2436 01/06/2015 2015-01-09
CARTN_2436 01/06/2015 2015-01-10
CARTN_2436 01/06/2015 2015-01-11
CARTN_2436 01/06/2015 2015-01-12
CARTN_2436 01/06/2015 2015-01-13
CARTN_2436 01/06/2015 2015-01-14
Bear in mind that "CARTN_1622" is different that "CARTN_2436." They both start on the sixth, but when I get to event "CARTN_2436," the first "new_date" should be the seventh and instead it's the ninth. Rather than starting over based on the original start date, for some reason my new date is increasing in a way that I don't understand.
Here's the code I'm using to try and fix the data. There's something I'm overlooking and I'm hoping you can tell me what.
<table border="1">
<tr>
<td>id</td>
<td>original date</td>
<td>new date</td>
</tr>
<?php
$tomorrow="";
$succession=1;
$matt="select * from calendar_long order by id, succession LIMIT 10";
$matt_query=mysqli_query($mysqli, $matt);
while($matt_row=mysqli_fetch_assoc($matt_query))
{
if($matt_row['succession']==0)
{
continue;
}
else
{
$today=$matt_row['date'];
$tomorrow =date("Y-m-d", strtotime("$today + {$succession} days"));
//echo $tomorrow;
$michelle="update calendar_long set date = '$tomorrow' where id='$matt_row[id]'";
echo $michelle;
//$michelle_query=mysqli_query($mysqli, $michelle);
?>
<tr>
<td>
<?php echo $matt_row['event_id']; ?>
</td>
<td><?php echo date("m/d/Y", strtotime($matt_row['date'])); ?></td>
<td><?php echo $tomorrow; ?></td>
</tr>
<?php
}
$succession=$succession+1;
$today="";
}
</table>
What do you think?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was it! Thank you!
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html