Writing the ending time to each ToDoSlice
item is more complex because we have to match to an existing record without benefit of having a unique ID to reference. Instead, we use a combination of the ToDoID
index and the starting time for the slice, which we known from the sending form.
We start with the UpdateEndTime.php file. We get the information for all four columns of the ToDoSlices and then try to update the end time and note fields, which is where the trouble lies. Writing the ending time should be straightforward, since there should be no previous value for it in the record after it is created. Updating the Note field, however, will present a problem if its text has not changed. For some odd reason, MySQL’s update
command won’t actually perform the update if a text value doesn’t change.
You can verify this by manually issuing the same statement as this code generates, which will cause the command-line interface to report 1 row matched but 0 rows changed. This operation won’t generate an error, it just won’t do anything at all, including update any other value in the record. For that reason the code jumps through a lot of hoops to update the end time and note fields separately, see if either operation generated an error, and report back the appropriate information. It’s probably possible that the end time will be properly updated while the note update fails (this would have to be something having to do with the MySQL host at a system level) so I should add some code to check for that condition. There are other options as well, one of which could be to only update the end time if a check of the note field reveals that its text has not changed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
<?php include('SQLFunctions.php'); if (!empty($_GET)) { $tdRecordToDoID = $_GET['ToDoIDx']; $tdNote = $_GET['Notex']; $tdStartTime = $_GET['startTimex']; $tdEndTime = date('Y-m-d H:i:s', time()); $returnText = ""; $returnFlag = true; $link = connectDB(); $sql = "update ToDoSlices set Note='".$tdNote. "' where recordToDoID=".$tdRecordToDoID. " AND startTime='".$tdStartTime."';"; if (!mysqli_query($link, $sql)) { $returnFlag = false; $returnText .= "Note Error: ".$sql."<br>".mysqli_error($link)." "; } $sql = "update ToDoSlices set endTime='".$tdEndTime. "' where recordToDoID=".$tdRecordToDoID. " AND startTime='".$tdStartTime."';"; if (!mysqli_query($link, $sql)) { $returnFlag = false; $returnText .= "End Time Error: ".$sql."<br>".mysqli_error($link)." "; } if ($returnFlag) { echo $tdEndTime; } else { echo $returnText; } mysqli_close( $link ); } ?> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
echo " function endSliceAjax() {"; echo " var xmlhttp = new XMLHttpRequest();"; echo " var handle = document.getElementById('endButton');"; echo " var parentHandle = handle.parentNode;"; echo " var noteHandle = document.getElementById('nextSliceNoteID');"; echo " var noteValue = noteHandle.value;"; echo " sliceIndex = parentHandle.previousElementSibling.previousElementSibling.innerHTML;"; echo " var startTime = parentHandle.previousElementSibling.innerHTML;"; echo " xmlhttp.onreadystatechange = function() {"; echo " if (this.readyState == 4) {"; echo " if (this.status == 200) {"; echo " if (this.responseText.search('Error: ') < 0) {"; echo " alert('response: '+this.responseText);"; echo " parentHandle.innerHTML = this.responseText;"; echo " noteHandle.parentElement.innerHTML = noteValue;"; echo " addTableRow();"; echo " } else {"; echo " alert(this.responseText);"; echo " }"; echo " } else {"; echo " alert('AJAX error encountered');"; echo " }"; echo " }"; echo " };"; echo " xmlhttp.open('GET','UpdateEndTime.php?ToDoIDx='+$ToDoID+'&startTimex='+startTime+'&Notex='+noteValue, true);"; echo " xmlhttp.send();"; echo " }"; |