IF Exists inserting into a DB

I am having a problem with the following code (see below): when cf gets to the insert statement, it bombs out. i just can't figure out what the issue is. i am trying to insert into a MySQL 5.x database..


Code:<cfquery name="putIPInfo" datasource="ipinfo">
if exists (select ipAddr from tbl_ipInfo where where ipAddr = '#remote_addr#')
<cfset currentdatetime = '#dateformat(now(), "YYYY-MM-DD")# #timeformat(now(), "HH:MM:SS")#'>
update tbl_ipInfo set last_seen='#currentdatetime#' where ipAddr='#remote_addr#'
else
<cfset currentdatetime = '#dateformat(now(), "YYYY-MM-DD")# #timeformat(now(), "HH:MM:SS")#'>
insert into tbl_ipInfo (ipAddr,user_agent,first_seen) values ('#remote_addr#', '#http_user_agent#', '#currentdatetime#')
end if
</cfquery>


Error:

Error Executing Database Query.
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 'if exists (select ipAddr from tbl_ipInfo where where ipAddr = '192.168.101.15') ' at line 1

The error occurred in /var/www/html/index.cfm: line 8

6 : else
7 : <cfset currentdatetime = '#dateformat(now(), "YYYY-MM-DD")# #timeformat(now(), "HH:MM:SS")#'>
8 : insert into tbl_ipInfo (ipAddr,user_agent,first_seen) values ('#remote_addr#', '#http_user_agent#', '#currentdatetime#')
9 : end if
10 : </cfquery>

 

first thing i noticed, you have 2 "where"s in the query

if exists (select ipAddr from tbl_ipInfo where where ipAddr = '#remote_addr#')

Another thing, why do you put the cfset statements inside the query, setting it before the cfquery is fine, or if you are concerned that it will not show the exact time the query was executed, why not use the now() function of mysql..

Lastly, i havent tried using the if exist query before, so if all else fails, try doing it in separate queries, first check if the IPaddress exist using a select statement, then check the results, if it exists, create a update query or if not create a insert query...

 

I'll second the idea that you should not have the cfset within the cfquery tag. cfset that variable first on a line above the cfquery tag.