How do I evaluate a variable against a Query Result

[scdebaay]

I have script that generates a variable containing the client ip adress.
and I want to check if this ip adress is allready in a specific table.column.

How do I do that, I have tried with
<cfquery name="recordsetvariable" datasource="MyDatabase">
SELECT ip_adress FROM ip_table
<cfquery>

<cfif #recordsetvariable# DOES NOT CONTAIN <#browseripadress#>
Further flow of the pogram.
But it seems I cant get it to work.
Anybody any ideas?

[zoeski80]

See attached code.

HTH

<!--- you could do it directly in the query --->
<cfquery name="recordsetvariable" datasource="MyDatabase">
SELECT ip_adress
FROM ip_table
WHERE ip_address = '#browseripadress#'
<cfquery>


<!--- or use cold fusion --->

<!--- do your query to get all ip addresses --->
<cfquery name="recordsetvariable" datasource="MyDatabase">
SELECT ip_adress
FROM ip_table
<cfquery>

<!--- check if the ip address is in the list of values found in the query --->
<cfif ListFind(ValueList(recordsetvariable.ip_address), browseripadress)>
blah blah
</cfif>

<!--- or loop over whole query --->
<CFLOOP QUERY="#recordsetvariable#">
<CFIF ip_address EQ browseripadr>
blal blah
</CFIF>
</CFLOOP>

<!--- or use a query of query - don't know what you are doing so this may suit
your purpose --->
<cfquery name="thisRecord" DBTYPE="QUERY">
SELECT ip_adress
FROM recordsetvariable
WHERE ip_address = '#browseripadress#'
<cfquery>

[scdebaay]

Wow!

Thanx a lot!
Don't have time to test it now, but I will be working on it soon!

The thing is, I want to check wether the IP exists in the table, and if it
doesn't, add a record in the table for that adress.

Any idea on how to construct that?
But, thanks again, I think this might be a good help.
Did'nt know about the ListFind Function ;o)

Greetz

[mxstu]

scdebaay,

You could use NOT EXISTS to insert the ip address only if it does not already
exist in your table. The exact syntax may vary depending on your database
type. This example works with mySQL 5.



<cfquery name="addRecord" datasource="MyDatabase">
INSERT INTO ip_table (ip_adress)
SELECT '#browserIPAdress#'
FROM dual
WHERE NOT EXISTS (
SELECT 1
FROM ip_table
WHERE ip_adress = '#browserIPAdress#'
)
</cfquery>