Professional Web Applications Themes

more where clause - Coldfusion - Advanced Techniques

I have a showoneitem page, which appears after 3 search page, one my first page i have a IDNO search which i want to go straight to the showoneitem page, i have tried the code below but with no luck <cfif isdefined('form.IDNO')> <cfset session.formvar=#form.IDNO#> </cfif> <CFQUERY DATASOURCE="DPSGuide" NAME="GetItem" MAXROWS="1"> SELECT * FROM States, Aged_Care_Facility_Details WHERE ID = #ID# OR ID = '#session.formvar#' </CFQUERY>...

  1. #1

    Default more where clause

    I have a showoneitem page, which appears after 3 search page, one my first page
    i have a IDNO search which i want to go straight to the showoneitem page, i
    have tried the code below but with no luck

    <cfif isdefined('form.IDNO')>
    <cfset session.formvar=#form.IDNO#>
    </cfif>



    <CFQUERY DATASOURCE="DPSGuide" NAME="GetItem" MAXROWS="1">
    SELECT *
    FROM States, Aged_Care_Facility_Details
    WHERE ID = #ID# OR
    ID = '#session.formvar#'
    </CFQUERY>

    macca2727 Guest

  2. #2

    Default Re: more where clause

    What do you mean by "no luck"? What exactly happens?

    One problem is that you're missing a join between the two tables in your FROM
    clause. The result of your query will likely be some type of cartesian product
    which A) could return a LOT of records and B) will probably return the wrong
    information. Assuming both tables contain a "StateID" column you should first
    join on the state column.

    .... WHERE States.StateID = Aged_Care_Facility_Details.StateID ....



    mxstu Guest

  3. #3

    Default Re: more where clause

    ok this was my original code which works when you go through all the searcg
    pages, what i now need to do is add a short cut
    so users can type in a IDNo to go straight to the showoneitem page

    i have set a from up on the first search page code attached

    SHOWONEITEM
    <CFQUERY DATASOURCE="DPSGuide" NAME="GetItem" MAXROWS="1">
    SELECT *
    FROM States, Aged_Care_Facility_Details
    WHERE ID = #ID#
    </CFQUERY>

    SEARCH PAGE
    <form name="form1" method="post" action="showoneitem.cfm">
    GUIDE TO AGED CARE ID No SEARCH
    <input name="IDNO" type="text" size="5">
    <input type="submit" name="Submit" value="Submit">
    </form>

    macca2727 Guest

  4. #4

    Default Re: more where clause

    >ok this was my original code which works when you go through all the searcg
    pages

    macca2727,

    As mentioned, you first need to join on the "StateID" column (or whatever the
    column name is) so that the results are not a cartesian product. Is this why
    you are using MAXROWS ...because you are getting many records in the results
    instead of just one record?

    SELECT *
    FROM States, Aged_Care_Facility_Details
    WHERE ID = #ID#



    mxstu Guest

  5. #5

    Default Re: more where clause

    i dont understand stateid column? my search is working fine, i just need a short cut to search my "ID" column in my table and return that record
    macca2727 Guest

  6. #6

    Default Re: more where clause

    can i not use a variable to show the results i have tried the code below but
    still dosnt work

    <cfif isdefined('form.IDFIELD')>
    <cfset session.formvar=#form.IDFIELD#>
    </cfif>

    <CFQUERY DATASOURCE="DPSGuide" NAME="GetItem" MAXROWS="1">
    SELECT *
    FROM States, Aged_Care_Facility_Details
    WHERE ID = #ID# OR
    session.formvar = #ID#
    </CFQUERY>

    macca2727 Guest

  7. #7

    Default Re: more where clause

    Originally posted by: macca2727
    i dont understand stateid column? my search is working fine, i just need a
    short cut to search my "ID" column in my table and return that record
    ....
    SELECT *
    FROM States, Aged_Care_Facility_Details
    WHERE ID = #ID#


    I will assuming that the "States" table contains multiple records. That
    being the case, I would be very suprised if your sql statement is returning the
    correct results, because your JOIN is incorrect.

    It looks like your query is trying to retrieving a single record. However, if
    you get rid of the MAXROWS="1" in your cfquery, and CFDUMP the query, you'll
    probably discover that the query contains multiple records, which is wrong.

    If you run this code and how many records does it return?

    <CFQUERY DATASOURCE="DPSGuide" NAME="GetItem">
    SELECT *
    FROM States, Aged_Care_Facility_Details
    WHERE ID = #ID#
    </CFQUERY>
    <CFDUMP var="#DPSGuide#">


    mxstu Guest

  8. #8

    Default Re: more where clause

    ok sorry i dont need states, the code below works fine, but not i need to have
    a short cut search of id from Aged_Care_Facility_Details table, which when i
    submit i get the following error, because the ID comes from the page that i
    need to skip.

    Variable ID is undefined.
    The error occurred in C:\CFusionMX7\wwwroot\CFIDE\DPSGuideWeb\All
    Files\WebSite\Files\showoneitem.cfm: line 10

    8 : SELECT *
    9 : FROM Aged_Care_Facility_Details
    10 : WHERE ID = #ID#
    11 : </CFQUERY>

    THIS IS THE FORM I AM USING
    <form name="form1" method="post" action="showoneitem.cfm"><tr>
    <input name="IDFORM" type="text" size="5">
    <input type="submit" name="Submit" value="Submit">

    </tr></form>

    macca2727 Guest

  9. #9

    Default Re: more where clause

    What happened when somebody actually submitted form1?

    Originally posted by: macca2727
    ok this was my original code which works when you go through all the searcg
    pages, what i now need to do is add a short cut
    so users can type in a IDNo to go straight to the showoneitem page

    i have set a from up on the first search page code attached
    SEARCH PAGE
    <form name="form1" method="post" action="showoneitem.cfm">
    GUIDE TO AGED CARE ID No SEARCH
    <input name="IDNO" type="text" size="5">
    <input type="submit" name="Submit" value="Submit">
    </form>




    Dan Guest

  10. #10

    Default Re: more where clause

    i get the error id is undefined, which i know why, because i am missing the
    page out which defines the id, I just tried this on the form
    <form name="form1" method="post" action="showoneitem.cfm?ID=#ID#">

    but still get the error

    the results page uses this code to get to showoneitem.cfm which works fine
    <a href="showoneitem.cfm?ID=#ID#">Profile</a>

    macca2727 Guest

Similar Threads

  1. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  2. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  3. if -s clause
    By DBSMITH@OhioHealth.com in forum PERL Beginners
    Replies: 11
    Last Post: June 8th, 03:54 PM
  4. Where to put a WHERE clause
    By Dale in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 14th, 03:48 PM

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