CODEWITHSUNDEEP
mysqlcoldfusioncoldfusion-9
user2203774
user2203774

Reputation: 619

How can I remove the duplicate contents?

I'm learning ColdFusion. I would like to display an article and 3 pictures. My code compiles, but it shows the article 3 times and 3 pictures. I just want to show the article one time. Can someone take a look at my code and give me a hint? Thanks for your time! 

<html>
<head>
    <title>Hello</title>
</head>
<body>
    <h3>Full Article View</h3>

    <CFQUERY NAME="myQuery1" Datasource="mydb" >
        SELECT *
        FROM articles
        INNER JOIN article_image_mapping ON articles.article_ID = article_image_mapping.aim_articleID
        INNER JOIN images ON aim_imageID = images.image_ID
        WHERE articles.article_ID = #URL.ID#
    </CFQUERY>


    <div align="left">
        <cfoutput query="myQuery1">
            #ucase(myquery1.article_title)# <br/>
            -------------------------------------<br>
            #myquery1.article_author# :: #myquery1.article_date#<br/>
            #myquery1.article_content#<br/>
            #myquery1.image_thumbpath#<br/>
        </cfoutput>
    </div>
</body>
</html>

Upvotes: 0

Views: 81

Answers (2)

Samuel Dealey
Samuel Dealey

Reputation: 263

Matt's right, you're looking for the group attribute in cfoutput. Although I think the placement of the inner cfoutput loop isn't quite what you're looking for. I think this is what you're trying to accomplish. 

<!--- use the group attribute to show the article once --->
<cfoutput query="myQuery1" group="article_id">
    #ucase(myquery1.article_title)# <br/>
    -------------------------------------<br>
    #myquery1.article_author# :: #myquery1.article_date#<br/>
    #myquery1.article_content#<br/>
    <!--- use an inner cfoutput to loop over the associated images --->
    <cfoutput>
        #myquery1.image_thumbpath#<br/>
    </cfoutput>
</cfoutput>

Anything that's inside your inner cfoutput is going to get one display for each record in your query. Items in the outer cfoutput (with the group attribute) will only appear once per group, so if your group is article_id, it will only show once for a given article_id.

In this particular case, you should only have one record in the articles table, so you don't need to worry about the sort order, since the images will all be attached to that record and that should cause them to occur next to each other in the result query*. If you were going to group on some other column however, then Matt would be correct that you would need to make sure your records are sorted correctly -- if the grouped records aren't next to each other in the query, then they won't be grouped in the output.

Unrelated: I would also recommend cfqueryparam for the article_id in your cfquery above, because numeric columns are particularly susceptible to SQL injection attacks.

(* as long as you're not doing something odd like sorting by the image_thumbpath column -- although technically the standard for SQL says you're not guaranteed any kind of sorting unless you explicitly specify it in an order by clause)

Upvotes: 0

Matt Busche
Matt Busche

Reputation: 14333

You need to use the group attribute on your query and also put an ORDER BY in your query. I'd also suggest only listing out the columns you need in your SELECT statement

<CFQUERY NAME="myQuery1" Datasource="mydb" >
 SELECT article_title, article_author, article_content, image_thumpath
 FROM articles
 INNER JOIN article_image_mapping ON articles.article_ID = article_image_mapping.aim_articleID
 INNER JOIN images ON aim_imageID = images.image_ID
 WHERE articles.article_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.ID#">
 ORDER BY article_title <!--- this is important! --->
</CFQUERY>


<div align="left">
  <cfoutput query="myQuery1" group="article_title"><!--- show the article_title once --->
    #ucase(myquery1.article_title)# <br/>
    -------------------------------------<br>
     <cfoutput><!--- loop through all results with same article title --->
      #myquery1.article_author# :: #myquery1.article_date#<br/>
      #myquery1.article_content#<br/>
      #myquery1.image_thumbpath#<br/>
     </cfoutput>
  </cfoutput>
</div>

Upvotes: 2

Related Questions