Hi there
I am trying to write a simple script to update a users membership to my site but I seem to be doing something wrong with the UPDATE query it runs fine without error and the page runs fine as well but the date never actually updates below is the SQL. What im trying to achieve is adding 365 days to the expiry_date column in my table
UPDATE Customers SET expiry_date = ADDDATE(expiry_date, INTERVAL 365 DAY) WHERE username = '$username'
As always thanks in advance for any help
Regards EC
Update the date field
Moderator: General Moderators
- evilchris2003
- Forum Contributor
- Posts: 106
- Joined: Sun Nov 12, 2006 6:43 am
- Location: Derby, UK
Re: Update the date field
Assuming that you're using MySQL, I don't see anything wrong with your syntax (although I wonder why don't use INTERVAL 1 YEAR). How are you invoking the query? Are you following the mysql_query() with a "or die (mysql_error())"? In other words, do you know if your mysql_query() is failing?
- evilchris2003
- Forum Contributor
- Posts: 106
- Joined: Sun Nov 12, 2006 6:43 am
- Location: Derby, UK
Re: Update the date field
Thanks for the reply apart from completely forgetting to add the mysql_error part to the code when I did it still doesnt show any error which is odd
Ive also tested the exact syntax in the backend (using Phpmyadmin) and it works exactly as expected
Im now completely stuck
Ive also tested the exact syntax in the backend (using Phpmyadmin) and it works exactly as expected
Im now completely stuck
Re: Update the date field
The absence of a mysql_error() message proves that the problem is not with the database connection, nor the syntax of the query, nor the names of the tables or fields. That's a lot of information to get from a non-message, huh?evilchris2003 wrote:Thanks for the reply apart from completely forgetting to add the mysql_error part to the code when I did it still doesnt show any error which is odd
Ive also tested the exact syntax in the backend (using Phpmyadmin) and it works exactly as expected
Im now completely stuck
So I think what would help is for you to show us your PHP code that is pertinent to the entire operation of updating. Most likely, something is happening in your logic to bypass the mysql_query() line. When you post PHP code, please use the "Code" button in the Post A Reply form, which will enclose your code in [ php] ... [ /php] tags, to format it for easier reading.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
Re: Update the date field
Yeah that SQL works fine on my version.
Code: Select all
mysql> describe date;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| date | date | NO | PRI | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> select * from date;
+------------+
| date |
+------------+
| 1971-12-31 |
| 2006-11-12 |
| 2007-04-05 |
| 2010-03-03 |
+------------+
4 rows in set (0.00 sec)
mysql> update date set date = adddate(date, interval 1 year);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from date;
+------------+
| date |
+------------+
| 1972-12-31 |
| 2007-11-12 |
| 2008-04-05 |
| 2011-03-03 |
+------------+
4 rows in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.51a |
+-----------+
1 row in set (0.02 sec)