Update the date field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Update the date field

Post by evilchris2003 »

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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Update the date field

Post by califdon »

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?
User avatar
evilchris2003
Forum Contributor
Posts: 106
Joined: Sun Nov 12, 2006 6:43 am
Location: Derby, UK

Re: Update the date field

Post by evilchris2003 »

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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Update the date field

Post by califdon »

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
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? :)

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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Update the date field

Post by Ollie Saunders »

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)
Post Reply