I am building an UPDATE query but have an issue when updating a date.
The field is defined as a date. Field name is “completed”.
The query looks like:
UPDATE activities SET (completed = 2015-09-04) WHERE id = 2;
Any ideas?
Rod
new 2.txt (686 Bytes)
If you are posting the code as an attachment, please just post it in the code tags, the php button.
Is the date variable or the current date? If the current date, I would suggest using CURDAT() or NOW() in the query.
the date is a variable.
Attached is the code segment. The $org variable is an array contain the original values.
new 2.txt (1.6 KB)
Post the code, not an attachment.
You aren’t listening.
Just a little dense. Below is the section for updating the data. Only the variables ‘due_date’ and ‘completed’ is causing the issue. Both are varialbe date fields containing YYYY-MM-DD information.
[php]
if(isset($_POST[‘edit_activity’])){
$req_fields = array(‘activity-task’,‘activity-assg’,‘activity-due’,‘activity-done’);
validate_activity_fields($req_fields);
if(empty($errors)){
$p_prj = $project[‘id’];
$p_task = remove_junk(real_escape($_POST[‘activity-task’]));
$p_assg = remove_junk(real_escape($_POST[‘activity-assg’]));
$p_due = remove_junk(real_escape($_POST[‘activity-due’]));
$p_done = remove_junk(real_escape($_POST[‘activity-done’]));
$query = “UPDATE activities SET (”;
$append = 0;
if ($p_task <> $org[‘task_id’]) {
$query .=" task_id = {$p_task}";
$append = 1;
}
if ($p_assg <> $org[‘assigned_id’]) {
if ($append) : $query .= ", "; $append = 0; endif;
$query .=“assigned_id = {$p_assg}”;
$append = 1;
}
if ($p_due <> $org[‘due_date’]) {
if ($append) : $query .= “, “; $append = 0; endif;
$query .=“due_date = {$p_due}”;
$append = 1;
}
if ($p_done <> $org[‘completed’]) {
if ($append) : $query .= “, “; $append = 0; endif;
$query .=“completed = {$p_done}”;
$append = 1;
}
$query .=”) WHERE (id = {$activity[‘id’]});”;
$query .=” ON DUPLICATE KEY UPDATE name=’{$p_name}’”; */
$session->msg(‘d’, $query);
$result = mysqli_query($con, $query);
error_log("error = " . mysqli_error($con) . “\n”,3, “./my-errors.txt”);
if($result){
$session->msg(‘s’,"Activity updated ");
redirect(‘edit_project.php?id=’ . $project[‘id’], false);
} else {
error_log("result = " . $result . “\n”,3, “./my-errors.txt”);
$session->msg(‘d’,’ Sorry failed to update!’);
redirect(‘edit_project.php?id=’ . $project[‘id’], false);
}
} else{
$session->msg(“d”, $errors);
redirect(‘edit_project.php?id=’ . $project[‘id’],false);
}
}
[/php]
My guess,
[php]
// missing quotes around the string. You should be using prepared statements and this wouldn’t be an issue.
$query .=“completed = {$p_done}”;[/php]
[php]// What is this exact value and how did you create it?
$query .=“due_date = {$p_due}”;[/php]
It should be following a specific format, ei, yyyy-mm-dd hh:mm:ss
If you are not following that pattern, it would likely throw an error and I don’t see you attempting to catch any exception that may be thrown by the database or anything else.
I have tried putting quotes around the date string with the same results.
Both $p_done and $p_due are created with the following code:
[php]
$p_due = remove_junk(real_escape($_POST[‘activity-due’]));
$p_done = remove_junk(real_escape($_POST[‘activity-done’]));
[/php]
Values of ‘activity-due’ and ‘activity-done’ are created for inputs on a form: follow example is for ‘activity-done’
[php]
[/php]
the values for ‘activity-due’ and ‘activity-done’ appear as 2015-09-09
$activity represents the record to be updated with current values. In the form $activity[‘completed’] shows 2015-09-01
hope that helps with your question.
Rod
Open up a terminal and log into mysql. Put the query in that way with what a value would be and see if it works.