Professional Web Applications Themes

Nested query results - Coldfusion Database Access

Hello I'm sure this question is quite common, would be very grateful for any guidance. I have an access database, which contains two tables: Table 1 is a list of Servers (IP address of, comments field etc, there is a serverID also, which is set to primary key). Table 2 is a chatroom table and has fields such as a comments field, chatroom name (which is primary key), and also a field named serverID - using Access, I created a relationship between the two serverID fields. I now wanted to query the data in the list of servers, and subsequently ...

  1. #1

    Default Nested query results

    Hello

    I'm sure this question is quite common, would be very grateful for any
    guidance.

    I have an access database, which contains two tables: Table 1 is a list of
    Servers (IP address of, comments field etc, there is a serverID also, which is
    set to primary key). Table 2 is a chatroom table and has fields such as a
    comments field, chatroom name (which is primary key), and also a field named
    serverID - using Access, I created a relationship between the two serverID
    fields.

    I now wanted to query the data in the list of servers, and subsequently grab
    all the chat rooms associated with that specific server, presenting the results
    something like:

    IP of server (as a title for example)
    chatroom name 1
    chatroom name 2
    ... etc

    so I want to dump out on the web page all of the channel names associated with
    each server listed in the Servers table (preferably using a nice table format).

    What Im confused about is, getting the query correct, and even more confused
    about, is outputting the results... so far I think I have the query correct but
    I get duplication in the output of the results, for example, if i have 3 chat
    channels associated with one Server, the server will be listed three times,
    with each chat room name listed next to each - so its almost there, I just want
    to bunch all the chat room names under just one title.

    thanks
    Ian

    IanHindle Guest

  2. #2

    Default Re: Nested query results

    Make sure your query is sorted by your IP address. Then use the group
    attribute of the cfoutput tag to format your results. Details are in the cfml
    reference manual. If you don't have one, the internet does.

    Dan Bracuk Guest

  3. #3

    Default Re: Nested query results

    Something like this?

    Phil



    SELECT s.IPaddress, c.name
    FROM chatrom c
    INNER JOIN servers s ON c.serverID = s.serverID
    ORDER BY s.IPaddress, c.name

    <cfoutput query="your_query" group = "IPaddress">
    #IPaddress# <BR>
    <cfoutput>#name# <BR>
    </cfoutput>
    </cfoutput>

    paross1 Guest

  4. #4

    Default Re: Nested query results

    Hi there, thanks for the replies gents. I've made some modifications to the
    code, and it still is displaying the results in a multiple fashion, as apposed
    to grouped together. Here is the code below:

    I've never seen the s. and c. thing much before, Im guessing that is some kind
    of aliasing?



    <cfquery name="getCurrentlist" datasource="ircservers">
    SELECT s.IRCServerIP,
    s.IRCServerInfo,s.IRCServerDateTimeAdded,c.IRCchan nelName,c.IRCchannelInfo,c.IRC
    channelDateTimeAdded
    FROM IRCchannels c
    INNER JOIN IRCservers s ON c.IRCServerID = s.IRCServerID
    ORDER BY s.IRCServerIP, c.IRCchannelName
    </cfquery>



    <p><b><font face="Arial">View/Manage List</font></b><br>
    &nbsp;</p>

    <table border="1" width="100%" id="table1">
    <tr>
    <td align="left" valign="top"><b>IRC Server IP</b></td>
    <td align="left" valign="top"><b>Server Comments</b></td>
    <td align="left" valign="top"><b>Associated Channels</b></td>
    </tr>
    <tr>
    <cfoutput query="getCurrentlist" group="IRCchannelName">
    <td align="left" valign="top">#IRCServerIP# added on
    #IRCServerDateTimeAdded#</td>
    <td align="left" valign="top">#IRCServerInfo#</td>
    <td align="left" valign="top">

    <table border="1" width="100%" id="table2">
    <tr>
    <td><b>IRC Channel Name</b></td>
    <td><b>IRC channel Info</b></td>
    </tr>

    <tr>
    <td>#IRCchannelName# added on #IRCchannelDateTimeAdded#</td>
    <td>#IRCchannelInfo#</td>

    </tr>

    </table>
    </td>
    </tr>
    <tr>
    <td align="left" valign="top">&nbsp;</td>
    <td align="left" valign="top">&nbsp;</td>
    <td align="left" valign="top">&nbsp;</td>
    </tr></cfoutput>
    </table>

    IanHindle Guest

  5. #5

    Default Re: Nested query results

    Give your fields aliases in the select clause of your query. Then use those names in cfoutput.
    Dan Bracuk Guest

  6. #6

    Default Re: Nested query results

    hmmm, I just dont get it. I dont know enough SQL to understand aliasing :-S
    anyone know where an example set of code is?
    IanHindle Guest

  7. #7

    Default Re: Nested query results

    I guess that you missed it in my previous example, but you neglected to nest an
    additional <cfoutput> tag within the other <cfoutput>. Something like the
    attached.

    Phil



    <table border="1" width="100%" id="table1">
    <tr>
    <td align="left" valign="top"><b>IRC Server IP</b></td>
    <td align="left" valign="top"><b>Server Comments</b></td>
    <td align="left" valign="top"><b>Associated Channels</b></td>
    </tr>
    <tr>
    <cfoutput query="getCurrentlist" group="IRCchannelName">
    <td align="left" valign="top">#IRCServerIP# added on
    #IRCServerDateTimeAdded#</td>
    <td align="left" valign="top">#IRCServerInfo#</td>
    <td align="left" valign="top">


    <tr>
    <td><b>IRC Channel Name</b></td>
    <td><b>IRC channel Info</b></td>
    </tr>
    <cfoutput>
    <tr>
    <td>#IRCchannelName# added on #IRCchannelDateTimeAdded#</td>
    <td>#IRCchannelInfo#</td>

    </tr>

    </cfoutput>
    </td>
    </tr>
    <tr>
    <td align="left" valign="top">&nbsp;</td>
    <td align="left" valign="top">&nbsp;</td>
    <td align="left" valign="top">&nbsp;</td>
    </tr></cfoutput>
    </table>

    paross1 Guest

  8. #8

    Default Re: Nested query results

    I have the additional <cfoutput> tags but the result is still the same: I am getting duplication of the same IP address with one channel name under each instead of one IP address and all chan names
    IanHindle Guest

  9. #9

    Default Re: Nested query results

    Sorry, change this <cfoutput query="getCurrentlist" group="IRCchannelName"> to this <cfoutput query="getCurrentlist" group="IRCServerIP"> as you need to group on the IRCServerIP field.

    Phil
    paross1 Guest

  10. #10

    Default Re: Nested query results

    Excellent! I understand now, grouping on the actual IP. Thanks phil, this has worked.
    IanHindle Guest

Similar Threads

  1. Complex join = no results (for a query that shouldreturn results)
    By jchapman16 in forum Coldfusion Database Access
    Replies: 4
    Last Post: August 23rd, 10:49 PM
  2. Query without Nested
    By BenAmy in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 1st, 01:44 AM
  3. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM
  4. Query results don't display properly in results table.
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 24th, 07:17 PM
  5. Getting too many results from a query!
    By Wm in forum PHP Development
    Replies: 2
    Last Post: August 17th, 03:45 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