phpmyadmin Question posted in
The official documentation can be found here.

MySQL update query is updating all fields except date (using PHP) – Phpmyadmin

I have a simple update query for when someone subscribes to my web app, updating the details of their license.

Recently I made some minor changes to this and it has stopped working (I presume these are linked, but haven’t actually managed to prove this) – I’ve tried reversing whatever I did but seems to be to no avail.

For some reason, the update runs successfully and all fields update, except the expiry field.

I write my sql query into a log file and I’ve copied this into phpmyadmin command line and it works exactly as expected.

[Result is:

UPDATE License 
   SET type='Full'
     , payer_id = 'XXXXXXX'
     , gross = 'XXXX'
     , payer_email = 'XXXXXXX' 
     , license_start = '2019-08-21 14:46:17'
     , expiry = '2019-09-21'
     , product = 'MON'
     , stripe_id = 'XXXXXX' 
 WHERE user_id = XXX

which seems fine to me

I’ve adapted the format, apostrophes, I’ve even just put a date in like ‘2019-12-10’ and it still won’t update via the mysqli query. I’ve also tried expiry = $start, which requires truncation, but should work… still just doesn’t update that field.

$expiry = date("Y-m-d", strtotime("+$couponmonths months", strtotime(date("Y-m-d H:i:s",$expiryts))));
$start = date ("Y-m-d H:i:s");
$sql = "UPDATE `$license_table` SET `type`='Full', `payer_id`='$subid', `gross`='$price', `payer_email`='$email', `license_start`='$start', `expiry` = '$expiry', `product`='$product_name', `stripe_id`='$stripeid' WHERE `user_id`=$userid";
    }
...
if (mysqli_query($connection,$sql) === TRUE) {...}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks those of you that tried to help. I've fixed this now by literally creating a new column called expiry_date instead of expiry, copying the data across to the new column, and updating all references.

    Feel like I haven't solved what went wrong, but I have at least got it working again. It felt like the column had decided to to accept updates via PHP - not sure this is even possible???

    I decided to try this course of action when I sent a query saying only to update this column and it still didn't work... madness!

    Would love to hear an explanation if there is one, but just happy to have it working for now.


  2. 0

    I think you should be setting $expiry, which presumably gets inserted into a datetime or timestamp type column in your DB, with the default SQL datetime format Y-m-d H:i:s as you did below.

    Also, make sure that

    strtotime("+$couponmonths months", strtotime(date("Y-m-d H:i:s", $expiryts)))

    actually works the way you intend and returns the desired time.

    Also, you should use {} to insert variables into strings.
    Like so:

    "UPDATE `{$license_table}` SET `type`='Full', `payer_id`='{$subid}', `gross`='{$price}', `payer_email`='{$email}', `license_start`='{$start}', `expiry` = '{$expiry}', [...]"

    and so on.

    This makes sure php is interpreting the variables correctly.

    If all still fails, try to dump the query string and run it manually with the DB-manager or cli of your choice, for you to debug.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search