comparing 2 tables 1 database

[adult.swim]

Im trying to compare the records of 2 different tables in the same database,
using cfif statement(s) and/or coldfusion or sql. If they're the same > output
results, if they're not the same > output different results, if field is empty

> output another result.

I've tried something along the lines of :

<cfif form.mlsnumber EQ homes.mlsnumber>

form = table 1
homes = table 2
mlsnumber = a column thats in both tables being compared

[paross1]

This kind of thing is usually done easily within a query, but you didn't provide enough information for us to help very much.

Phil

[adult.swim]

ok,

I made a form to get info from the user. I want to compare the info that the
user inputs with my database, if they match then i want to output the record
that matches the info input.

I had the form info input into an empty table, to compare the 2 tables, to
attempt to solve this problem.

So step by step, this is what I have so far:

1. formpage.cfm: user inputs fields matching the fields in my database
2. actionpage.cfm: form input goes into an empty table;
I call my databse in a query
and I'm trying to compare the 2 tables
using a cfif statement e.g. <cfif form.mlsnumber EQ homes mlsnumber>
then get output

If anyone has info let me know plz

[Kronin555]

There's no reason to put the user-entered data into a table just so you can
compare it.

actionpage.cfm:

<cfquery datasource="blah" name="checkForMatching">
select count(*) as numMatching from homes where misnumber = #FORM.misnumber#
</cfquery>

<cfif checkForMatching.numMatching gt 0>
Hey! you matched a value in the database!
<cfelse>
The value you entered isn't in the database
</cfif>

[adult.swim]

Dude,

Thank you so much, I've been beating my head against the wall trying to figure
out how to do this.

I have a follow up question:

<cfquery datasource="houses" name="checkForMatching">
SELECT *
FROM props
WHERE props.MLSnumber=#FORM.mlsnumber# OR
props.StreetNumber=#FORM.StreetNumber#
</cfquery>

I took the code u gave me and modified it, to try to include the other fields
in my form. But I get a syntax error when I run my actionpage. Do you c the
propblem with my code that I need to fix, or doies the 1st statement with the
mlsnumber have to pass 1st b4 the other fields in the form.

Is there a way I can write the WHERE clause so that the FORM fields get
checked indenpently, i.e. they can enter 1 field out of the 7 and get results.

[Dan Bracuk]

If the datatype of props.streetnumber is char, you need single quotes in your
query.

Regarding having the query still give results even if some of the form fields
were not filled in, the answer is yes. You have to think your problem through
before you start coding so that you know how you want to handle fields with no
data. Bear in mind that what you get on your action page is dependent on the
type of form field.

The approach I prefer is to build a variable that becomes the where clause of
my query. Others prefer putting their logic code inside the cfquery tag. Both
methods work.

[adult.swim]

Thanks,

Will do.