Professional Web Applications Themes

Search results page - Coldfusion - Getting Started

Hi - hopefully I can express my problem correctly - I'm just trying to create a search results page from a query on a linked table. Current SQL: SELECT MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME, MEMBERS.EMAIL, MEMBERS.ACCESS FROM MEMBERS WHERE LAST_NAME = '#URL.LAST_NAME#' ORDER BY LAST_NAME ASC My problem is that "MEMBERS.ACCESS" contains the id values for a linked table. How can I get these id values to display the actual "text" values taken from the linked table. Many thanks for any help....

  1. #1

    Default Search results page

    Hi - hopefully I can express my problem correctly - I'm just trying to create a
    search results page from a query on a linked table. Current SQL:

    SELECT MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME, MEMBERS.EMAIL,
    MEMBERS.ACCESS
    FROM MEMBERS
    WHERE LAST_NAME = '#URL.LAST_NAME#'
    ORDER BY LAST_NAME ASC

    My problem is that "MEMBERS.ACCESS" contains the id values for a linked table.
    How can I get these id values to display the actual "text" values taken from
    the linked table. Many thanks for any help.



    absolut novice Guest

  2. #2

    Default Re: Search results page

    Without knowing your db structure, you will probably want to do a JOIN to that table.
    [url]http://www.w3schools.com/sql/sql_join.asp[/url] is a pretty good site for the basics.
    rmorgan Guest

  3. #3

    Default Re: Search results page

    Thanks - for the web link - DB structure: MS Access/ Main Table "MEMBERS" Sub
    Table "ACCESS" linked via "MEMBERS.ACCESS" and "ACCESS.ID". ACCESS.ACCESS
    contains the text I want to diplay on the search returns page.

    When I tried:

    SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
    MEMBERS.EMAIL, MEMBERS.ACCESS
    FROM MEMBERS
    RIGHT JOIN ACCESS
    ON MEMBERS.ACCESS=ACCESS.ID
    WHERE LAST_NAME = '#URL.LAST_NAME#'
    ORDER BY LAST_NAME ASC

    It works fine in the Test SQL Statement window however display the error "Too
    few parameters. Expected 1" via the web brower.

    SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
    MEMBERS.EMAIL, MEMBERS.ACCESS
    FROM MEMBERS
    RIGHT JOIN ACCESS
    ON MEMBERS.ACCESS=ACCESS.ID
    WHERE LAST_NAME = '#URL.LAST_NAME#'
    ORDER BY LAST_NAME ASC

    absolut novice Guest

  4. #4

    Default Re: Search results page

    Try an INNER JOIN.
    rmorgan Guest

  5. #5

    Default Re: Search results page

    Thanks but unfortunately still the same result - so far I've tried RIGHT JOIN,
    LEFT JOIN and INNER JOIN with no luck.

    The following:

    SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
    MEMBERS.EMAIL, MEMBERS.ACCESS
    FROM MEMBERS
    INNER JOIN ACCESS
    ON MEMBERS.ACCESS=ACCESS.ID
    WHERE LAST_NAME = '#URL.LAST_NAME#'

    Again works fine in the Test SQL Statement window however display the error
    "Too few parameters. Expected 1" via the web browser. As for datatypes:

    MEMBERS.ACCESS = Data Type = Number/Field Size = Long Integer/Row Source =
    SELECT [ACCESS].[ID], [ACCESS].[ACCESS] FROM ACCESS;
    ACCESS.ID = Data Type = AutoNumber/Field Size = Long Integer
    ACCESS.ACCESS = Data Type = Text/Field Size = 50

    TIA

    absolut novice Guest

  6. #6

    Default Search results page

    Hi - hopefully I can express my problem correctly - I'm just trying to create a
    search results page from a query on a linked table.
    My problem is that "MEMBERS.ACCESS" contains the id values for a linked table.
    How can I get these id values to display the actual "text" values taken from
    the linked table.

    So far I've tried RIGHT JOIN, LEFT JOIN and INNER JOIN with no luck.

    The following:

    SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
    MEMBERS.EMAIL, MEMBERS.ACCESS
    FROM MEMBERS
    INNER JOIN ACCESS
    ON MEMBERS.ACCESS=ACCESS.ID
    WHERE LAST_NAME = '#URL.LAST_NAME#'

    Again works fine in the Test SQL Statement window however display the error
    "Too few parameters. Expected 1" via the web browser. As for datatypes:

    MEMBERS.ACCESS = Data Type = Number/Field Size = Long Integer/Row Source =
    SELECT [ACCESS].[ID], [ACCESS].[ACCESS] FROM ACCESS;
    ACCESS.ID = Data Type = AutoNumber/Field Size = Long Integer
    ACCESS.ACCESS = Data Type = Text/Field Size = 50

    TIA :o

    absolut novice Guest

  7. #7

    Default Re: Search results page

    I can't see anything wrong with the query, except that it looks more
    complicated than it needs to be. Just for fun, change this:

    SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
    MEMBERS.EMAIL, MEMBERS.ACCESS
    FROM MEMBERS
    INNER JOIN ACCESS
    ON MEMBERS.ACCESS=ACCESS.ID
    WHERE LAST_NAME = '#URL.LAST_NAME#'


    to this
    SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
    MEMBERS.EMAIL, MEMBERS.ACCESS
    FROM MEMBERS, ACCESS
    wher MEMBERS.ACCESS=ACCESS.ID
    and LAST_NAME = '#URL.LAST_NAME#'


    Dan Bracuk Guest

  8. #8

    Default Re: Search results page

    The INNER | RIGHT | LEFT JOIN is ANSI style syntax which is probably better than FROM table1, table2 style. However, I agree it does seems a bit bulky in comparison ;-)
    mxstu Guest

  9. #9

    Default Re: Search results page

    Thanks heaps Dan for the amended code:

    SELECT ACCESS.ACCESS, MEMBERS.ID, MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME,
    MEMBERS.EMAIL, MEMBERS.ACCESS
    FROM MEMBERS, ACCESS
    where MEMBERS.ACCESS=ACCESS.ID
    and LAST_NAME = '#URL.LAST_NAME#'

    - again works fine in the TEST SQL statement window however via the web I get
    the following error:

    Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
    Access Driver] Too few parameters. Expected 1.


    absolut novice Guest

  10. #10

    Default Re: Search results page

    absolut novice,

    Use whichever style you prefer, but your original query is the recommended
    ANSI style syntax.

    ... MEMBERS INNER JOIN ACCESS ON MEMBERS.ACCESS=ACCESS.ID ....

    I don't see anything wrong with your query either. You might want to turn on
    debugging and output the actual sql statement sent to the database, then copy
    and past it into Access and see if it still works.


    mxstu Guest

  11. #11

    Default Re: Search results page

    Thanks mxstu - I'll try to get the system admin people to enable the debugging
    however they know nothing about ColdFusion. Does this mean that the TEST SQL
    statement window doesn't accurately test the SQL via the database?

    absolut novice Guest

  12. #12

    Default Re: Search results page

    Let's look at this error message:
    Too few parameters. Expected 1.

    Troubleshooting step number 1:
    do a dump of url and see what you get for url.last_name.



    Dan Bracuk Guest

  13. #13

    Default Re: Search results page

    Sorry Dan - I'm not sure what you mean by "do a dump of url"
    absolut novice Guest

  14. #14

    Default Re: Search results page

    Try the easy debugging method first, like Dan is suggesting. You might also
    want to try deconstructing the statement, one column at a time to see which
    piece is giving you problems.

    I don't usually use any of the IDE's database features, so I'm not much help
    there. I wouldn't say that it doesn't accurately test the sql, but it use a
    different method of communicating with the database than is used by cfquery.
    Access is pretty lenient about what you're allowed to do within its IDE. CF
    uses an odbc socket to communicate with the Access database through CFQUERY,
    and the rules for ODBC are bit different and usually a bit more strict.



    mxstu Guest

  15. #15

    Default Re: Search results page

    To do a dump of url means to stick this in your template and run it:

    <cfdump var="#url#">
    Dan Bracuk Guest

  16. #16

    Default Re: Search results page

    many thanks for your explaination
    absolut novice Guest

Similar Threads

  1. retrieving page numbers from search results
    By mikeleerave@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 2
    Last Post: January 25th, 02:47 PM
  2. Sending search results to a results page..with asp
    By dan.how in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 26th, 04:18 PM
  3. Help with a Grouped Search Results Page...
    By The Ox in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 9th, 02:23 PM
  4. Error in Search Results Page.
    By Braulio Lumbreras in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: June 6th, 05:27 PM
  5. Return Search Results on the Same Page
    By shell in forum ASP Database
    Replies: 2
    Last Post: July 14th, 11:27 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