CODEWITHSUNDEEP
mysqldatabasecoldfusioncfml
Getek99
Getek99

Reputation: 529

Can't figure out why some of my SQL code doesn't work

I'm building an update query with ColdFusion and can't figure out why this piece of code doesn't work? Note, it works directly within mysql server so the SQL must be valid, it just isn't updating my data when called within tags.

I'm banging my head off the wall over this one and would love to get the help of someone out there that has an idea. My feeling is that ColdFusion is messing the format before it reaches the DB. Thanks you all for your time. 

<cfquery name="qry" datasource="uf">
UPDATE users set expiry = (SELECT DATE_ADD("#user_qry.expiry#", INTERVAL 10 DAY)), active = 1 where user_id = #user_id#
</cfquery>

Upvotes: 0

Views: 79

Answers (1)

Shawn
Shawn

Reputation: 4786

You're mixing CF and MySQL date logic. I'd stick to just one. Use SELECT dateAdd("d",10,CreateODBCDate(user_qry.expiry)).... This way, with CF's dateAdd(), you're adding 10 days to a CF date object inside of CF rather than trying to get MySQL to add 10 days to a date string generated by CF.

Also, you want a cfqueryparam in your WHERE statement. Make sure you sanitize your inputs.

Upvotes: 2

Related Questions