SELECT id, resort FROM resorts WHERE resort IN ( ) SELECT id, resort FROM resorts WHERE resort = '#List#' [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 5 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> CFLOOP and a Query - Coldfusion - Advanced Techniques

CFLOOP and a Query - Coldfusion - Advanced Techniques

i all, I have a query that gets a column from my database of chalets. The column shows a list of the near by resorts, seperated by commas. So for a chalet it could have: 'Val d'Isere, Meribel, Courcheval' in the field linkedResorts I want to seperate these in order to get the resorts individual id's from the resorts table. I have used cfloop like this: The query: <cfquery name="Recordset1" datasource="dsn"> SELECT linkedResorts FROM resorts WHERE id = '10' </cfquery> The loop: <cfloop delimiters="," list="#Recordset1.linkedResorts#" index="List"> <cfquery name="hump" datasource="dsn"> SELECT id, resort FROM resorts WHERE resort = <cfoutput>'#List#'</ cfoutput> </cfquery> ...

Sponsored Links
  1. #1

    Default CFLOOP and a Query

    i all,

    I have a query that gets a column from my database of chalets. The
    column shows a list of the near by resorts, seperated by commas.

    So for a chalet it could have:

    'Val d'Isere, Meribel, Courcheval' in the field linkedResorts

    I want to seperate these in order to get the resorts individual id's from
    the resorts table.

    I have used cfloop like this:

    The query:

    <cfquery name="Recordset1" datasource="dsn">
    SELECT linkedResorts
    FROM resorts
    WHERE id = '10'
    </cfquery>

    The loop:

    <cfloop delimiters="," list="#Recordset1.linkedResorts#" index="List">
    <cfquery name="hump" datasource="dsn">
    SELECT id, resort FROM resorts WHERE resort = <cfoutput>'#List#'</
    cfoutput>
    </cfquery>
    <cfoutput query="hump">#resort# #id#<br /></cfoutput>
    </cfloop>

    The problem I have is with the bit that is in bold. How do I get the
    individual ids from the individual list parts? So the id for Val d'Isere and
    then the id for Meribel etc etc??

    I have tried ValueList() but this returns a blank page when I use it in this
    way:

    <cfquery name="Recordset1" datasource="dsn">
    SELECT linkedResorts
    FROM resorts
    WHERE id = '10'
    </cfquery>

    <cfquery name = "get_resortId" datasource = "dsn">
    SELECT id, resort FROM resorts WHERE resort IN ('#ValueList
    (Recordset1.linkedResorts)#')
    </cfquery>

    <cfoutput query="get_resortId">
    #resort# #id#<br />
    </cfoutput>

    Is it because the value of of Recordset1.linkedResorts is already delimited by
    a comma that it returns a blank page?

    (ie:

    <cfoutput>#Recordset1.linkedResorts#</cfoutput> = Val d'Isere, Meribel,
    Courcheval
    )

    Thanks

    Jansolo


    Sponsored Links
    Jansolo Guest

  2. #2

    Default Re: CFLOOP and a Query

    The best solution to this problem would be to redesign your table and normalize
    the information. Storing comma-delimited lists of values in a column is a bad
    design from both a modeling and performance perspective, and as you can see,
    because it is difficult to work with.

    A better structure might be to store the information about nearby resorts in a
    separate table. Each "resort + nearbyResort" combination would be in a separate
    row. The table would store the numeric record ID's (not the resort names).

    Resorts
    ------------
    ResortID
    Resort (Name)

    NearbyResorts
    ------------------
    ResortID (Base resort)
    NearbyResortID (ID of a Nearby resort like "Meribel")

    You could then retrieve the information in a simple query:

    SELECT r.ResortID, r.ResortName
    FROM NearbyResort nb INNER JOIN Resorts r ON nb.NearbyResortID = r.ResortID
    WHERE nb.ResortID = 10


    mxstu Guest

  3. #3

    Default Re: CFLOOP and a Query

    This would obviously be the better solution, however I inherited the site from
    another designer and with over 1900 chalets listed this would be a nightmare,
    hence wanting the answer to the existing problem!!

    Cheers

    Jansolo

    Jansolo Guest

  4. #4

    Default Re: CFLOOP and a Query

    Take the cfoutput tag out of your query. It's not necessary.

    Why are both queries selecting from the same table. Is the answer to your
    second query 10?

    Is your id field (value of 10) really char.

    Dan Guest

  5. #5

    Default Re: CFLOOP and a Query

    The conversion from that type of format isn't really that difficult, but I
    understand that it impacts your existing application so obviously that is up to
    you. However, to answer your original question, assuming the values:

    "Val d'Isere, Meribel, Courcheval"

    ... match an existing "resort". You shouldn't need a cfloop. Try using a
    where in clause.

    <!--- not tested --->
    <cfquery name="getNearbyResorts" datasource="dsn">
    SELECT id, resort FROM resorts
    WHERE resort IN (
    <cfqueryparam value="#Recordset1.linkedResorts#" cfsqltype="CF_SQL_VARCHAR"
    list="yes">
    )
    </cfquery>

    SELECT id, resort FROM resorts WHERE resort = <cfoutput>'#List#'</
    cfoutput>
    </cfquery>



    mxstu Guest

  6. #6

    Default Re: CFLOOP and a Query

    Hi there mxstu,

    The query returns a blank page! It is weird as we know that the field linkedResorts contains the value "Val d'Isere, Meribel, Courcheval" .

    It is driving me insane!!!

    Jansolo
    Jansolo Guest

  7. #7

    Default Re: CFLOOP and a Query

    Did you try it?

    No, it's not the same. Your query essentially says find records where the
    resort name equals "Val d'Isere, Meribel, Courcheval". What my example should
    do is essentially say find records where the resort name equals
    "Val d'Isere" OR "Meribel" OR "Courcheval". Note my example is not tested.

    You may also have problems if the values in your list have spaces around them
    and the values in the resort names don't.



    mxstu Guest

  8. #8

    Default Re: CFLOOP and a Query

    Hi there,

    This is what I thouhgt - there is white space around the names of the individual resorts (between , and name).

    I think this is causing the blank page!!

    I am going barmy
    Jansolo Guest

  9. #9

    Default Re: CFLOOP and a Query

    So, are you saying you did try the example using cfqueryparam with the "list" attribute and it didn't work either?
    mxstu Guest

Similar Threads

  1. Query Objects in cfloop and cfoutput
    By Ryan in forum Coldfusion - Advanced Techniques
    Replies: 7
    Last Post: October 14th, 09:20 PM
  2. cfmail / cfloop / query: How to make work?
    By Leonard in forum Macromedia ColdFusion
    Replies: 2
    Last Post: July 31st, 11:31 PM
  3. cfloop from query
    By EINN in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 18th, 02:09 PM
  4. count cfloop query
    By CFMadness in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: April 4th, 06:43 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
  •