Professional Web Applications Themes

comparing 2 tables 1 database - Coldfusion Database Access

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...

  1. #1

    Default comparing 2 tables 1 database

    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


    adult.swim Guest

  2. #2

    Default Re: comparing 2 tables 1 database

    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
    paross1 Guest

  3. #3

    Default Re: comparing 2 tables 1 database

    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

    adult.swim Guest

  4. #4

    Default Re: comparing 2 tables 1 database

    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>

    Kronin555 Guest

  5. #5

    Default Re: comparing 2 tables 1 database

    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.



    adult.swim Guest

  6. #6

    Default Re: comparing 2 tables 1 database

    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.

    Dan Bracuk Guest

  7. #7

    Default Re: comparing 2 tables 1 database

    Thanks,

    Will do.


    adult.swim Guest

Similar Threads

  1. Replies: 2
    Last Post: March 5th, 01:39 AM
  2. Missing Database Tables
    By mike400hp in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 26th, 09:20 PM
  3. Database is connecting but not seeing tables
    By saadiq31 in forum Dreamweaver AppDev
    Replies: 0
    Last Post: February 22nd, 02:23 PM
  4. Comparing two tables
    By mkarja in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 11th, 12:42 PM
  5. Comparing rows in 2 tables
    By DA Morgan in forum Oracle Server
    Replies: 4
    Last Post: December 18th, 06:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139