Issue with UPDATE query containing date field

[rodb]

I am building an UPDATE query but having an issue when updating date fields.  The date fields are 'due_date' and 'completed'.  The fields are populated by date_picker which uses format YYYY-MM-DD.  When a query is built it has the following form:

 

   $query = "UPDATE activities SET (completed = {$p_done}) where (id = {$activity['id']});"

 

when substitutions are made

 

  $query = "UPDATE activities SET (completed = 2015-09-03) where (id = 2);

 

error generated syntax error within the following:

 

   SET(completed= 2015-09-03) where (id=2)

 

Updates work with other fields when dates are not being updated.

 

The code segment is attached.

 

any ideas?

Rod

new 2.txt

[Barand]

Put the date values inside single quotes,

mysql> select 2015-09-03 as A, '2015-09-03' as B;
+------+------------+
| A    | B          |
+------+------------+
| 2003 | 2015-09-03 |
+------+------------+

 otherwise SQL thinks it is 2015 minus 9 minus 3

Edited September 8, 2015 by Barand

[rodb]

I haved this

$query = "UPDATE activities SET (completed = '{$p_done}') where (id = {$activity['id']});"

which gives

$query = "UPDATE activities SET (completed = '2015-09-03") where (id = 2);

but get the same result.

 

should this be

 

$query = "UPDATE activities SET completed = " . '{$p_done} '  . "where (id = {$activity['id']});"

[mac_gyver]

if you post the error, someone can help you find what's causing it.

 

---

 

you can greatly simplify the code that's building the sql query statement by storing the column = 'value' pairs in an array, then simply implode the array with a comma between the elements.

 

also, you don't need to check if values where changed or not in your code. update queries do this for you. they read the row of data and check if the values are the same. if they are, the update query skips actually updating/storing the data back to the disk.

Edited September 9, 2015 by mac_gyver

[rodb]

The error I get is as follows:

 

query = UPDATE activities SET (completed = 2015-09-03) WHERE (id = 2);

error = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(completed = 2015-09-03) WHERE (id = 2)' at line 1

 

---------------------------

I am not sure how to build the query by storing the pairs in an array.  Is there a tutorial or example I can study?   Also the other remark about update queries doing data checking is good to know,  I don't  that happens in other database systems I have used.

 

 

[mac_gyver]

the mysql syntax error is most likely due to the ( ) around the column = 'value' term.

 

to build the terms in an array, instead of concatenating them with the sql, you would store them in an array -

$terms = array(); // define/initialize the array before you use it


// at the point of producing one of the column='value' terms -
$terms[] = "assigned_id = {$p_assg}"; // store the term (you would want to do this dynamically in a loop rather than write out each logic test.)


// at the end of the section of code, test if the array holds anything to operate on -
if(!empty($terms)){
    // there's at least one term in the array
    $term = implode(',',$terms); // note: this works even if there is only one entry in the array - you just get that one entry without the ','

    // your code to form and run the sql query would go here, since if there's no column='value' term, there's nothing to update

}

Edited September 9, 2015 by mac_gyver