Page 1 of 1
Update the date field
Posted: Fri Jun 27, 2008 5:07 pm
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
Re: Update the date field
Posted: Fri Jun 27, 2008 7:18 pm
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?
Re: Update the date field
Posted: Sat Jun 28, 2008 8:52 am
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
Re: Update the date field
Posted: Sat Jun 28, 2008 1:47 pm
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.
Re: Update the date field
Posted: Sat Jun 28, 2008 10:02 pm
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)