Jump to content

Issue with UPDATE query containing date field


rodb
Go to solution Solved by mac_gyver,

Recommended Posts

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

Link to comment
Share on other sites

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']});"

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

  • Solution

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 by mac_gyver
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.