UPDATE table set date field

Question

I am trying to update a date field from a row but it does not seem to be affecting it but it is updating other columns as expected. I must be missing something when setting the date:

$task_id = $_POST['task_id']; 
$task_started_status = htmlspecialchars($_POST['task_started_status']);
$task_start_date = date('Y-m-d');


// Attempt update query execution
$sql = "UPDATE task_list 
        SET task_started_status = $task_started_status, task_start_date = $task_start_date 
        WHERE task_id='$task_id'";
if(mysqli_query($con, $sql)){
    header('location: ../tasks.php');
} else {
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($con);
}

The database is using the DATE data type but no errors occur it still believes its all valid - no errors - just not updating and if I VAR_DUMP on the $task_start_date it outputs the date id expect it to be.

What are the values of all those POST arrays/variables? If any of those are strings, you "should" be getting an error. Plus, you're taking this from a form, what does that look like? — Funk Forty Niner, Jun 06 '16 at 14:19
The values are for $task_id = id of the post(INT), $task_started_status = single value of either 0,1,2,3(INT) and the date should be the DATE() as mentioned above — PhpDude, Jun 06 '16 at 14:21
`task_start_date = $task_start_date` that needs to be quoted here `task_start_date = '$task_start_date'` since it's a string `Y-m-d`. what's the column's type also? — Funk Forty Niner, Jun 06 '16 at 14:22
Well that fixed it - is this is because it turns into a string? — PhpDude, Jun 06 '16 at 14:23
Thanks man - I thought I was going crazy (been staring at this for too long clearly) — PhpDude, Jun 06 '16 at 14:25

score 2 · Accepted Answer · edited May 23 '17 at 12:24

As I stated in comments:

task_start_date = $task_start_date that needs to be quoted here
task_start_date = '$task_start_date' since it's a string Y-m-d.

Plus, Y-m-d MySQL is interpreting that as 2016 minus 06 minus 06 (as per today's date here) and that error checking you're using, should have thrown you an error about it, but you said it didn't which I find strange.

but no errors occur it still believes its all valid - no errors

When doing an UPDATE, use mysqli_affected_rows() for actual truthness.

http://php.net/manual/en/mysqli.affected-rows.php

Also adding exit; after header. Otherwise, you code may want to continue to execute.

You could have used CURDATE() instead of $task_start_date = date('Y-m-d'); being task_start_date = CURDATE() where no quoting would have needed to be used.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements.

UPDATE table set date field

1 Answers1