pagination through records when record starts based on url variable

I'm outputing an image and some other data based on this query:

<cfquery name="newaqimages" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1,
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5,
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName FROM Artwork, Artists WHERE Artwork.AutoArtID=#URL.AutoArtID# and Artwork.ArtistID=Artists.ArtistID
</cfquery>

I need to be able to page through all of the records related to the ArtistID associated with a specific artwork piece (AutoArtID). So, I'm guessing, I pass the ArtistID along as a url variable as well.

Something like this query:

<cfquery name="rsPage" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1,
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5,
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName FROM Artwork, Artists WHERE  Artwork.ArtistID=#URL.ArtistID#
</cfquery>

<div>Prev Next </div>

Any idea how to do this?
phillystyle123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

parikaaCommented:
Basically pagination in a query deals with the use of startrow and max rows in the cfquery tag:




http://paginationcfc.riaforge.org/

One quick way of getting where you want to be is that you can use pagination cfc, which is a free component

<cfquery name="rsPage" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1, 
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5, 
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName 
FROM Artwork, Artists 
WHERE  Artwork.ArtistID=#URL.ArtistID#
</cfquery>

<cfset pagination = createObject("component", "components.Pagination").init() />
<cfset pagination.setQueryToPaginate(rsPage) />
<!--- Enter the base link of your page  and any url variables needed to be passed for persistence --->
<cfset pagination.setBaseLink("/appname/photolist.cfm?year=2007") />

<cfset pagination.setItemsPerPage(25) />
<cfset pagination.setUrlPageIndicator("page") />
<cfset pagination.setShowNumericLinks(true) />

#pagination.getRenderedHTML()#
<cfoutput query="rsPage" startrow="#pagination.getStartRow()#"
maxrows="#pagination.getMaxRows()#">
<li>#id# - #name#</li>
</cfoutput>

#pagination.getRenderedHTML()#

Open in new window

parikaaCommented:
Correction:

...deals with the use of startrow and max rows in the cfoutput tag:

phillystyle123Author Commented:
thanks-getting this error:

Could not find the ColdFusion component or interface components.Pagination.
<cfparam name="URL.AutoArtID" default="1">
<cfquery name="newaqimages" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1, 
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5, 
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName FROM Artwork, Artists WHERE Artwork.AutoArtID=#URL.AutoArtID# and Artwork.ArtistID=Artists.ArtistID
</cfquery>

<cfquery name="rsPage" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1, 
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5, 
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName 
FROM Artwork, Artists 
WHERE  Artwork.ArtistID=#URL.ArtistID#
</cfquery>

<cfset pagination = createObject("component", "components.Pagination").init() />
<cfset pagination.setQueryToPaginate(rsPage) />
<!--- Enter the base link of your page  and any url variables needed to be passed for persistence --->
<cfset pagination.setBaseLink("/appname/photolist.cfm?year=2007") />

<cfset pagination.setItemsPerPage(25) />
<cfset pagination.setUrlPageIndicator("page") />
<cfset pagination.setShowNumericLinks(true) />

<cfquery name="titledates" datasource="Ikonltd">
SELECT  ExTitle, BeginDate, EndDate FROM CurrExExhibition
</cfquery><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title><cfoutput>#newaqimages.FirstName# #newaqimages.LastName#: #newaqimages.Title#</cfoutput></title>

<link href="../css/main.css" rel="stylesheet" type="text/css" />

<!--background from nahem site-->
<script type="text/javascript" src="http://a1.exhibit-e.com/js_libs/mootools/1.2/core.js"></script>
<script type="text/javascript" src="http://localhost:8500/ikonltd.com/js/fullscreen_bg/moo_12.js"></script>



</head>

<body>
<div id="wrap">
<div id="contentWrap">

<div id="masthead"><img src="../images/2010/temporary_for_development/ikon_banner1_smaller_logo.gif" /></div>

<ul class="topNav">
<li><a href="#">Current Exhibition</a></li>
<li><a href="#">Past Exhibitions</a></li>
<li><a href="#">Artists</a></li>
<li><a href="#">New Acquisitions</a></li>
<li><a href="#">Private Room</a></li>
<li><a href="#">About</a></li>
<li><a href="#">Contact</a></li>
<li><a href="#">Home</a></li>
</ul>

<div id="artistPageContentWrap">

<div id="artistInfoNav"><!--left column-->
<cfoutput>
<h1>#newaqimages.FirstName# #newaqimages.LastName#</h1>
</cfoutput>
<div id="coolmenu">
<div id="tabledescription">
<cfoutput> 
        <p><b>#newaqimages.Title#</b><br>
          #newaqimages.Date#
          <cfif "#newaqimages.DescriptionWhole#" is "">
            <cfelse>
            #newaqimages.DescriptionWhole# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine1#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine1# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine2#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine2# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine3#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine3# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine4#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine4# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine5#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine5# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine6#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine6# 
          </cfif>
          <cfif "#newaqimages.WebDescriptionLine7#" is "">
            <cfelse>
            <br>
            #newaqimages.WebDescriptionLine7# 
          </cfif>
        </p>
      </cfoutput>
	  
	  <cfoutput> 
       <a href="mailto:ikonltd@earthlink.net?Subject=Re: #newaqimages.FirstName#&nbsp;#newaqimages.LastName# - #newaqimages.Title#">Contact 
          Gallery for Further Information</a></p>
      </cfoutput>
	  </div>
	  </div>
	  <div>#pagination.getRenderedHTML()#
<cfoutput query="rsPage" startrow="#pagination.getStartRow()#"
maxrows="#pagination.getMaxRows()#">
<li>#id# - #name#</li>
</cfoutput>

#pagination.getRenderedHTML()#
 </div>
	  <cfoutput>
	  <p><a href="artist.cfm?ArtistID=#newaqimages.ArtistID#">Back to Artist Page</a>
<br /><br /><a href="index.cfm">Back to Artist List</a>
<br />
<br />

<a href="">Artist Exhibitions</a>
</cfoutput>
</div>

<div id="artistWorks"><!-- right column-->

  <cfoutput><img src="../images/lg/#newaqimages.lg#" border="0"></cfoutput></div>
<!--eof artistWorks-->

</div><!--eof artistPageContentWrap-->



</div><!--eof contentWrap-->
</div><!--eof wrap-->


</div>

</div>


<script type="text/javascript">
//<![CDATA[

FullScreenBG.init('http://localhost:8500/ikonltd.com/images/2010/ikon-blank-wall.jpg');

//]]>
</script>
<!--eof show thumb on rollover for artist landing page-->

</body>
</html>

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

parikaaCommented:
Did you download the pagination cfc component from riaforge?


components.pagination refers to the path components/pagaination.cfc

You should create a directory called components and save the pagination.cfc component inside it. -- Unless of course you already have a differently named components folder like idk cfcs?

phillystyle123Author Commented:
oh! ok - i get it - so just install it in a directory in my site root?
parikaaCommented:
You have to change the base link to your site's base still...

Also, I changed your code, but I can't say it will work the first time...just let me know if there are any errors
<cfparam name="URL.AutoArtID" default="1">


<cfquery name="rsPage" datasource="Ikonltd">
SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1, 
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5, 
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName 
FROM Artwork, Artists 
WHERE  Artwork.ArtistID=#URL.ArtistID#
</cfquery>

<cfset pagination = createObject("component", "components.Pagination").init() />
<cfset pagination.setQueryToPaginate(rsPage) />
<!--- Enter the base link of your page  and any url variables needed to be passed for persistence --->
<cfset pagination.setBaseLink("/appname/photolist.cfm?year=2007") />

<cfset pagination.setItemsPerPage(25) />
<cfset pagination.setUrlPageIndicator("page") />
<cfset pagination.setShowNumericLinks(true) />

<cfquery name="titledates" datasource="Ikonltd">
SELECT  ExTitle, BeginDate, EndDate FROM CurrExExhibition
</cfquery>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title><cfoutput>#newaqimages.FirstName# #newaqimages.LastName#: #newaqimages.Title#</cfoutput></title>

<link href="../css/main.css" rel="stylesheet" type="text/css" />

<!--background from nahem site-->
<script type="text/javascript" src="http://a1.exhibit-e.com/js_libs/mootools/1.2/core.js"></script>
<script type="text/javascript" src="http://localhost:8500/ikonltd.com/js/fullscreen_bg/moo_12.js"></script>



</head>

<body>
<div id="wrap">
<div id="contentWrap">

<div id="masthead"><img src="../images/2010/temporary_for_development/ikon_banner1_smaller_logo.gif" /></div>

<ul class="topNav">
<li><a href="#">Current Exhibition</a></li>
<li><a href="#">Past Exhibitions</a></li>
<li><a href="#">Artists</a></li>
<li><a href="#">New Acquisitions</a></li>
<li><a href="#">Private Room</a></li>
<li><a href="#">About</a></li>
<li><a href="#">Contact</a></li>
<li><a href="#">Home</a></li>
</ul>

<div id="artistPageContentWrap">
<cfoutput query="rsPage" startrow="#pagination.getStartRow()#" maxrows="#pagination.getMaxRows()#">
<div id="artistInfoNav"><!--left column-->

	<h1>#newaqimages.FirstName# #newaqimages.LastName#</h1>
	<div id="coolmenu">
	<div id="tabledescription">

    <p><b>#newaqimages.Title#</b><br>
      #newaqimages.Date#
      <cfif "#newaqimages.DescriptionWhole#" is "">
        <cfelse>
        #newaqimages.DescriptionWhole# 
      </cfif>
      <cfif "#newaqimages.WebDescriptionLine1#" is "">
        <cfelse>
        <br>
        #newaqimages.WebDescriptionLine1# 
      </cfif>
      <cfif "#newaqimages.WebDescriptionLine2#" is "">
        <cfelse>
        <br>
        #newaqimages.WebDescriptionLine2# 
      </cfif>
      <cfif "#newaqimages.WebDescriptionLine3#" is "">
        <cfelse>
        <br>
        #newaqimages.WebDescriptionLine3# 
      </cfif>
      <cfif "#newaqimages.WebDescriptionLine4#" is "">
        <cfelse>
        <br>
        #newaqimages.WebDescriptionLine4# 
      </cfif>
      <cfif "#newaqimages.WebDescriptionLine5#" is "">
        <cfelse>
        <br>
        #newaqimages.WebDescriptionLine5# 
      </cfif>
      <cfif "#newaqimages.WebDescriptionLine6#" is "">
        <cfelse>
        <br>
        #newaqimages.WebDescriptionLine6# 
      </cfif>
      <cfif "#newaqimages.WebDescriptionLine7#" is "">
        <cfelse>
        <br>
        #newaqimages.WebDescriptionLine7# 
      </cfif>
    </p>
	
	<p>
   	<a href="mailto:ikonltd@earthlink.net?Subject=Re: #newaqimages.FirstName#&nbsp;#newaqimages.LastName# - #newaqimages.Title#"
	>Contact Gallery for Further Information</a>
	</p>
  </cfoutput>
</div>
</div>
<div>#pagination.getRenderedHTML()#</div>
<cfoutput>
<p>
<a href="artist.cfm?ArtistID=#newaqimages.ArtistID#">Back to Artist Page</a>
</p>
<br /><br />
<a href="index.cfm">Back to Artist List</a>
<br />
<br />
<a href="">Artist Exhibitions</a>
</cfoutput>
</div>

<div id="artistWorks"><!-- right column-->

  <cfoutput><img src="../images/lg/#newaqimages.lg#" border="0"></cfoutput></div>
<!--eof artistWorks-->

</div><!--eof artistPageContentWrap-->



</div><!--eof contentWrap-->
</div><!--eof wrap-->


</div>

</div>


<script type="text/javascript">
//<![CDATA[

FullScreenBG.init('http://localhost:8500/ikonltd.com/images/2010/ikon-blank-wall.jpg');

//]]>
</script>
<!--eof show thumb on rollover for artist landing page-->

</body>
</html>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
parikaaCommented:
Yes install it into a directory you name "components"
phillystyle123Author Commented:
I got this to work to a point but decided to go in a different direction using the built in DW pagination code.






SELECT AutoArtID, Artwork.ArtistID, Title, Date, lg, DescriptionWhole, WebDescriptionLine1,
WebDescriptionLine2, WebDescriptionLine3, WebDescriptionLine4, WebDescriptionLine5,
WebDescriptionLine6, WebDescriptionLine7, FirstName, LastName FROM Artwork, Artists WHERE Artwork.ArtistID=#URL.ArtistID# and Artwork.ArtistID=Artists.ArtistID

SELECT  ExTitle, BeginDate, EndDate FROM CurrExExhibition








 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">



<cfoutput>#newaqimages.FirstName# #newaqimages.LastName#: #newaqimages.Title#</cfoutput>



<!--background from nahem site-->
<script type="text/javascript" src="http://a1.exhibit-e.com/js_libs/mootools/1.2/core.js"></script>
<script type="text/javascript" src="http://localhost:8500/ikonltd.com/js/fullscreen_bg/moo_12.js"></script>














<!--left column-->

#newaqimages.FirstName# #newaqimages.LastName#



        #newaqimages.Title#

          #newaqimages.Date#
         
           
            #newaqimages.DescriptionWhole#
         
         
           
           

            #newaqimages.WebDescriptionLine1#
         
         
           
           

            #newaqimages.WebDescriptionLine2#
         
         
           
           

            #newaqimages.WebDescriptionLine3#
         
         
           
           

            #newaqimages.WebDescriptionLine4#
         
         
           
           

            #newaqimages.WebDescriptionLine5#
         
         
           
           

            #newaqimages.WebDescriptionLine6#
         
         
           
           

            #newaqimages.WebDescriptionLine7#
         
       

            
       Contact
          Gallery for Further Information


   
        
        
        PrevNext
      
        Back to Artist Page
Back to Artist List



Artist Exhibitions


<!-- right column-->

 
<!--eof artistWorks-->

<!--eof artistPageContentWrap-->



<!--eof contentWrap-->
<!--eof wrap-->







<script type="text/javascript">
//<![CDATA[

FullScreenBG.init('http://localhost:8500/ikonltd.com/images/2010/ikon-blank-wall.jpg');

//]]>
</script>
<!--eof show thumb on rollover for artist landing page-->



It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.