SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: CF8 group

  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,061
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CF8 group

    Hi all. I'm working on a website for a record store, there are between 8 and 10 categories. On the frontpage I show the latest arrivals for which I use a tapped jQuery content slider. It works great but I would like to limit the number of records per group (category_id) . If I use maxrows in my query it limit the total number of records to the number specified. If I use maxrows in the output it limit the total number of categories to the number specified. What should I do to limit the number of records per group, please see query and output below


    Query

    Code CFM:
    <cfquery name="getProducts" datasource="Highfidelity">
    	SELECT p.product_id, p.artist, p.title, p.publisher, p.release_date, p.price, p.category_id, c.category
        FROM products p
    	INNER JOIN categories c ON p.category_id = c.category_id
        ORDER BY p.category_id
    </cfquery>
    Output
    Code CFM:
    <cfoutput query="getProducts" group="category_id">
    <div class="tabbed-content" style="margin-top:40px;">
    <cfoutput>
    <cfquery name="getPhotos" datasource="Highfidelity">
    	SELECT photo
        FROM photos
        WHERE product_id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( getProducts.product_id )#" /> )
    </cfquery>
    <cfquery name="getDescriptions" datasource="Highfidelity">
    	SELECT SUBSTRING_INDEX(description, ' ' , 20) AS shortDesc
        FROM descriptions
        WHERE product_id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#Val( getProducts.product_id )#" /> )
    </cfquery>
    <div class="listing #IIf(CurrentRow Mod 2, DE('left-listing'), DE('listing'))#">
    <cfif category_id EQ 1 >
    <img src="photos/#getPhotos.photo#" width="65" height="90">
    <cfelse>
    <img src="photos/#getPhotos.photo#" width="90" height="90">
    </cfif>
    <span class="listingheader">#artist#</span>
    #title#<br>
    #publisher#, #jaar#<br>
    #LSCurrencyFormat( price, 'none' )#<br style="clear:both;">
    <p class="listingdescription">#ReplaceNoCase( getDescriptions.shortDesc, chr( 13 )&chr( 10 ), '<br />', 'all')#</p>
    </div>
    </cfoutput>
    </div>
    </cfoutput>

    Thank you in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    first of all, you need to rewrite that section of the code where you do two additional queries inside the cfoutput loop for the categories -- use a single join query instead

    second, i don't see anything in your code that handles the "latest arrivals" aspect -- the only ORDER BY is on category_id

    finally, i'm puzzled why you would have photos and descriptions in separate tables -- i could see the need for a separate photos table if you wanted to have multiple photos per product (but then you're not handling that in the code at present), but i'm really curious why there isn't just a description column in the main products table

    you should probably resolve the above issues before thinking about limiting the number of products displayed for each category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,061
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudi, thank you for the reply. Your absolutly right about the aditional categories query. I should have think about that.

    You don't see the additional clause for latest arrivals as yet since the client is not sure himself how he would like to handle this (adding new arrivals once a week to the database or more frequently per week is the question) so that wil be something I need to adjust later.

    There will be a possibility for multple photos per product, since the store has quite a large selection of Collectors Items which the owner would like to promote as good as possible (with multiple photos)

    Finally the descriptions, I have to admid your right again This comes, because the site will come in three languages, and somehow I thought it would be practical to sepperate the descriptions from the main products table. If you say It is not a good idea, than I think I probably reconsider.

    Leaves me with the question, I started off with. How do I limit the records per group. I realy can't think of something

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    let me know when your database design has settled down a bit

    separate table for multiple language descriptions is correct

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,061
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will rudi, thank you

  6. #6
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,061
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The database is more settled now. For the new arrivals I have added two new fields (isNew int(1)) and (added(timestamp default current_timestamp)). For isNew 1 will indicate that it is a new arrival. The Owner will update database once a week on Friday. When new arrivals will be added the value for older records with value 1 will be set to 0. This is the query:
    Code CFM:
    <cfquery name="getProducts" datasource="#application.dsn#">
        SELECT p.product_id, p.artist, p.title, p.publisher, p.release_date, p.condition, p.price, p.category_id, c.category, p.genre_id, g.genre
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.category_id
        LEFT JOIN genres g ON p.genre_id = g.genre_id
        WHERE isNew = 1               
        ORDER BY category_id
    </cfquery>

    But I still don't have a sollution for the limited group output. I would like to limit the output per group to 4 records. I tried a conditional loop:
    Code CFM:
    <cfoutput query="getProducts" group="category_id">
        <ul>
        <cfset count = 1>
        <cfloop condition="count lte 3">
            <cfoutput><li>#artist#</li></cfoutput>
            <cfset count = count + 1>
        </cfloop>
        </ul>
    </cfoutput>
    but it isn't working at all. Do you have any idea what to do?

    Thank you in advance

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    let's start with the query, which won't work as written because of the ambiguous column in the ORDER BY clause

    (hint: qualify all columns used in a query that involves more than one table)

    is it possible that you have a product for a category that doesn't exist? is it possible that you have a product for a genre that doesn't exist? if the answer to these questions is no, then you should be using inner joins, not outer joins

    as for your output, you don't need the CFLOOP but you do need a CFIF to test the counter against your number 4
    Code:
    <cfoutput query="getProducts" group="category_id">
        <ul>
        <cfset count = 1>
        <cfoutput>
            <cfif count LT 4>
               <li>#artist#</li>
            </cfif>
            <cfset count = count + 1>
        </cfoutput>
        </ul>
    </cfoutput>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,061
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi rudi! thank you very very much. You're right the order by was ambiguous indeed. It is now working like a charm.

    About the joins! I have the queiries like this so that for example in the navigation only the categories are shown that have products related to them (hope this make any sense). In total I have 7 categories. If only 4 categories have products related only those categories will show in the drop down menus.

    Again thank you so much

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by donboe View Post
    About the joins! I have the queiries like this so that for example in the navigation only the categories are shown that have products related to them (hope this make any sense).
    in that case you definitely want INNER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •