7 Replies Latest reply on Dec 23, 2010 11:46 AM by ACS LLC

    Populating cookies from a SQL Query

    ACS LLC Level 1

      I have a SQL query that I know for a fact will produce 4 records without fail

       

      What I need to do is to store the data on an individual basis so that I can use each record independantly on the following page allowing me to set up hyperlinks based on each one including it's text description (from the query - cat_tiitle , and it's uid from the query, cat_uid)

       

      I have written some code but it seems a little primative, I am thinking that there might be a better way to do this? I know it's only a small piece of code but it has the potential to be used a LOT so I need to make sure I have this as efficient as possible -->

       

                <!--- GET THE 4 RECORDS --->

       

      <CFQUERY NAME="GetCategories" DATASOURCE="#datasource#">
          SELECT cat_uid,cat_title
          FROM categories
          WHERE cat_uid IN (#valuelist(Validateinstall.install_preferences)#)
          ORDER by cat_title
      </CFQUERY>

       

       

                <!--- SET UP 4 INDIVIDUAL COOKIES WHICH HAVE THE UID AND THE TITLE --->

      <CFLOOP query="GetCategories">

       

      <CFCOOKIE NAME="pref_uid_#GetCategories.CurrentRow#" value="#GetCategories.cat_uid#">
      <CFCOOKIE NAME="pref_title_#GetCategories.CurrentRow#" value="#GetCategories.cat_title#">

       

      </CFLOOP>

       

       

       

                <!--- DISPLAY THE 4 HYPERLINKS --->

       

      <CFOUTPUT>

       

      1. <a href="cat.cfm?cat_uid=#cookie.pref_uid_1#,#URLEncodedFormat(cookie.pref_title_1)#"

       

      class="reg_text">#cookie.pref_title_1#</a>

       

      2. <a href="cat.cfm?cat_uid=#cookie.pref_uid_2#,#URLEncodedFormat(cookie.pref_title_2)#"

       

      class="reg_text">#cookie.pref_title_2#</a>

       

      3. <a href="cat.cfm?cat_uid=#cookie.pref_uid_1#,#URLEncodedFormat(cookie.pref_title_3)#"

       

      class="reg_text">#cookie.pref_title_3#</a>

       

      4. <a href="cat.cfm?cat_uid=#cookie.pref_uid_4#,#URLEncodedFormat(cookie.pref_title_4)#"

       

      class="reg_text">#cookie.pref_title_4#</a>

       

      </CFOUTPUT>

       

      Appreciate any thoughts on making this more efficient

       

      Thanks

       

      Mark

        • 1. Re: Populating cookies from a SQL Query
          Dan Bracuk Level 5

          It would be more efficient to forgo the cookies and use the query results in your anchor tags.

          • 2. Re: Populating cookies from a SQL Query
            ilssac Level 5

            Is there some reason you don't use SESSION variables.  Which are like COOKIES only better!

             

                     <!--- GET THE 4 RECORDS --->
             
            <CFQUERY NAME="Session.GetCategories" DATASOURCE="#datasource#">
                SELECT cat_uid,cat_title
                FROM categories
                WHERE cat_uid IN (#valuelist(Validateinstall.install_preferences)#)
                ORDER by cat_title
            </CFQUERY>
             
             
            
                      <!--- DISPLAY THE 4 HYPERLINKS --->
            <cfoutput query="session.GetCategories">
                 <a href="cat.cfm?catuid="#session.GetCategories.cat_uid#",#urlEncodedFormat(session.GetCategories.cat_title)#"
                      class="reg_text">#session.GetCategories.cat_title#</a>
            </cfoutput>
            
            
            
            • 3. Re: Populating cookies from a SQL Query
              ACS LLC Level 1

              that's interesting, I did not know you could read the results of a query directly into a session like that.

               

              The issue I have is that we are running this on a shared server, and I know that this page could potentially receive a large amount of hits.

               

              My concern I have is that there could be problems with too many sessions on the server at any time, I have no control over the administrator settings, plus the session could expire.

               

              I figured that a cookie was a safer way to ensure that I did not have to be too concerned about the server settings.

              • 4. Re: Populating cookies from a SQL Query
                ilssac Level 5

                Sessions do expire.  If you are trying to persist this data for days and days, the first choice would be a database, or other data store, that would store the query recordset tied to a user specific key.  You would then just store the key in a single cookie.

                 

                Working out for there, you can store the recordset itself in a cookie as long as it is not too large.  You just have to incode it, <cfwddx....> would be the old school way to encode it, there are others now adays.


                Or you can continue your current option.  I would probably use a <cfloop from="1" to="4" index="i"> to consolidate that output code some.

                • 5. Re: Populating cookies from a SQL Query
                  ACS LLC Level 1

                  yeah, I did wonder about stuffing that cookie with more data.

                   

                  You think a CFLOOP over the results is quicker than just hard coding it to display each cookie?

                   

                  Output -->

                   

                  <a href="cat.cfm?cat_uid=#cookie.pref_uid_1#,#URLEncodedFormat

                   

                  (cookie.pref_title_1)#"

                   

                  class="newhyper_wht_12">#cookie.pref_title_1#</a>

                   

                   

                   

                  <a href="cat.cfm?cat_uid=#cookie.pref_uid_2#,#URLEncodedFormat

                   

                  (cookie.pref_title_2)#"

                   

                  class="newhyper_wht_12">#cookie.pref_title_2#</a>

                   

                   

                   

                  <a href="cat.cfm?cat_uid=#cookie.pref_uid_3#,#URLEncodedFormat

                   

                  (cookie.pref_title_3)#"

                   

                  class="newhyper_wht_12">#cookie.pref_title_3#</a>

                   

                   

                   

                  <a href="cat.cfm?cat_uid=#cookie.pref_uid_4#,#URLEncodedFormat

                   

                  (cookie.pref_title_4)#"

                   

                  class="newhyper_wht_12">#cookie.pref_title_4#</a></CFOUTPUT>

                  • 6. Re: Populating cookies from a SQL Query
                    ilssac Level 5

                    I don't know about FASTER?

                     

                    But I would think it would be easier to edit and maintain, which 9 times out of 11 is the more expensive and critical aspect.

                    • 7. Re: Populating cookies from a SQL Query
                      ACS LLC Level 1

                      True, normall I would use a loop but with it being a page that is going to get a lot of traffic I thought for this particular situation I'd avoid the loop, and try to squeeze as much as possible

                       

                      sessions were a no go due to the risk of expiring. It looks like I've actually probably got the best solution in place for my situation.

                       

                      I guess I'll leave it as-is.

                       

                      Thanks for the input guys, I did learn something regarding the sessions into a query, very nice!

                       

                      Mark