[RESOLVED] updating wrong column

[vang]

hi,
i have a little problem updating my tables.
When i execute this query, all went well except that the "db_register_date" which has a value in it, is also updated with the same value as "db_date_account_activate"!
I cannot find my problem lies, any pointers is appreciated, thanks.

	$qry = "UPDATE member
			SET db_member_status = '1', db_date_account_activate = now()
				WHERE db_date_register = '$date'
						AND db_code = '$code'";

[djjjozsi]

lets try to use the ` around you rfield names.

$date="2009-06-09";
$code="xxxxx";

$sql="UPDATE `member` SET `db_member_status` = '1',
`db_date_account_activate` = NOW( ) WHERE 
 `db_date_register` = '$date' AND  `db_code`= '$code'";

this should be working.

[jwagner]

and why would this work any better? as far as i know mysql does not require the quotes around the field or table names. isn't that so?

[vang]

i tried, but it is not working.

[jwagner]

Hi vang,

As far as I know (and I don't know much), in order to alter values you would use this syntax:

UPDATE table_name SET (field1, field2) VALUES (value1, value2)

by the way,
is now() a function you declared or what?

[vang]

I use now() to store the current time & date in database.

[djjjozsi]

its a tested SQL code, so you should test an update query in phpmyadmin. If that manager program uses some kind of a CAST on the field names, you should use that method.

if you're using a reserved field name such as order, date now , group as a field name, you should use the ` qoute. i've never heard a syntax you've mentioned on UPDATE statement.

i've tested this:

 UPDATE `usr` SET( username, pass ) VALUES (
'a', 'aaa') WHERE 1

MySQL said: Documentation
#1064 - 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 '(username,pass) VALUES ('a','aaa')' at line 1

[jwagner]

Yeah, sorry, I mixed it all up with the INSERT syntax.

Anyway, I guess the best way would be to know what now() returns and what $date holds, before trying to fix things.

[djjjozsi]

such things will happen 🙂 and that field needed to be in a date type...

[vang]

both dates are in type : timestamp
i want to store the register day, which i had done successfully.
but when i activated the account & want to store the activation date, i encounter problem.
i modify the code to below and still the register date updated itself!:
$qry = "UPDATE member
SET db_member_status = '1'
WHERE db_code = '$code'";

[vang]

db_date_register TIMESTAMP NOT NULL,
db_date_account_activate TIMESTAMP NOT NULL default '0000-00-00 00:00:00',

[djjjozsi]

timestamp type usually have an addition feature to update current timestamp on UPDATE.

lets set OFF that feature:

ALTER TABLE `member` CHANGE `db_date_account_activate` `db_date_account_activate` TIMESTAMP NULL 

[vang]

i think our problem has been identified, it is easier to solve now, thanks a lot.

[djjjozsi]

vang;10917863 wrote:

i think our problem has been identified, it is easier to solve now, thanks a lot.

you're welcome