Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Coldfusion  recordset output in vertical , paging  issue!

Posted on 2011-05-11
17
Medium Priority
?
760 Views
Last Modified: 2013-12-16
Hi,

I was trying to show my records in vertically  for example

<table width="100%" border="0" cellspacing="0" cellpadding="1">
  <tr>
    <td>Record 1</td>
    <td>Record 2</td>
    <td>Record 3</td>
    <td>Record 4</td>
  </tr>
  <tr>
    <td>Record 5</td>
    <td>Record 6</td>
    <td>Record 7</td>
    <td>Record 8</td>
  </tr>
</table>


Now I can show my records vertically,
Can you please help me to write paging script for my code.




<!--- Query  --->
<cfquery name="fetch_records" datasource="#request.MainDSN#">
            select * from tbl where cat_ID = 2 order by date DESC
</CFQUERY>

  <table border="1">
           
            <cfset E = 4>
            <cfset S = 1>
         <cfloop index = "tableLoop" from = "1" to = #fetch_records.recordcount#>
         
         <tr>
       
         <cfoutput query="fetch_records" startrow="#S#" maxrows="4">
                  
          <td align="left" width="25%"><a href="show_records.cfm?catID=#url.catID#">
         
         <img src="#photoFileName#" width="137" height="141" />
         
          </a>
         
         
         
          </td>
             
      <cfif #fetch_records.recordcount# LT 4 AND #fetch_records.recordcount# EQ #fetch_records.currentrow#>
                    <cfset  missingTD = 4 - fetch_records.recordcount >
                          <cfloop index="missTD" from="1" to="#missingTD#">
                              <td align="left" width="25%">&nbsp;</td>
                          </cfloop>
       </cfif>
             
             
             
             
             
         </cfoutput>
         </tr>
         
         
         <tr align="center" >
     
         <cfoutput query="fetch_records" startrow="#S#" maxrows="4">
         
          <td width="25%" align="center"  class="innerPagePicDec">#fetch_records.rec_Name# &nbsp;| &nbsp;#fetch_records.rec_id#</td>
             
             
             
               <cfif #fetch_records.recordcount# LT 4 AND #fetch_records.recordcount# EQ #fetch_records.currentrow#>
                    <cfset  missingTD = 4 -fetch_records.recordcount >
                          <cfloop index="missTD" from="1" to="#missingTD#">
                              <td width="25%">&nbsp;</td>
                          </cfloop>
                   </cfif>
         
          </cfoutput>
             
         </tr>
         
               <cfset S = S+4>
         
           
         </cfloop>
            </table>
       
0
Comment
Question by:xeondxb
  • 7
  • 5
  • 4
  • +1
17 Comments
 
LVL 1

Expert Comment

by:mateojaime07
ID: 35736878
add a startRow url param to your href so that when the link is clicked it will show the next recordset. something like this

<cfparam name="startrow" default="1">
<cfparam name="maxrows" default="4">

<cfoutput query="yourQuery" startrow="#startrow#" maxrows="#maxrows#">
      your content
</cfoutput>

<a href="link.cfm?startrow=Evaluate(startRow+maxRows)>forward</a>

Open in new window

0
 

Author Comment

by:xeondxb
ID: 35736917
thank you for reply my problem not paging normal paging i can do my issue is in this scenario i mean in this code how can i do i tried these things.
0
 
LVL 1

Expert Comment

by:mateojaime07
ID: 35737000
my apologies i do not understand your last comment. Are you trying to do pagination without page refreshing? if so you can use ajax methodology to pass a startrow  and get a return dataset without the page refreshing. Else you can use the href link to link to the same page, but you would have to pass in a startrow so every time the page loads it loads that number and pulls the records based on the start row passed in. Also if you are using MYSql, it has a wonderful pagination feature to it as well that you could use so that you are not querying the entire recordset everytime and then just display your paginated results..... please give some more detail of what you are trying to acheive. Thank you
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:coldchillin
ID: 35740055
If you're using MS SQL, look into Common Table Expressions.

Here is a very simple tutorial:

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005
0
 

Author Comment

by:xeondxb
ID: 35743802
thank you for reply sorry i didn't write im using MSAccess not SQL server coldfusion 9 is there anyway i can do on msaccess
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35744309
>> normal paging i can do <<
@xeondxb - your response is confusing.

Are you asking how paginate ?

                    -OR-

Do you already know how to paginate and just need help displaying the results vertically?
0
 

Author Comment

by:xeondxb
ID: 35744372
thanks for reply @aqx

1st i was facing problem to show my records in horizontal  coz, i had limit to show only 4 records in 1 row, means 4 columns in one row, so now you can see my script it is working fine
i don't know this code is good or messy but it is working it is showing all records without any problem but
now i need to do paging for my script  because i have 300+ records  with pictures when i shows in same page it is very slow and messy ... hope you got the answer...

0
 
LVL 53

Expert Comment

by:_agx_
ID: 35745610
So you need to know how to paginate *and* display those results vertically.

Unfortunately MS Access doesn't provide an elegant way of paginating AFAIK.  You have 2 options:

1) Do the pagination on the db side. This would involve 2 queries. The first would count the total records to display ie 300+ records.

      SELECT COUNT(*) As TotalRecords FROM tbl WHERE cat_ID = 2 

Open in new window


You'd use the "TotalRecords" to determine the number of pages and to retrieve the next page of records. Since you'd only retrieve 8 records at a time, you could use your existing code to display the records vertically.

You can retrieve the next 8 records by ranking with a subquery. Another slightly more complex method is with a derived query.

2) The other option is to retrieve ALL records each time, but only display 8 of them at a time.  It's simpler, but less efficient, than option #1: ColdFusion and pagination

0
 

Author Comment

by:xeondxb
ID: 35745641
sorry i wrote wrong vertically  im trying to show "horizontal "  limit is 4 records for example

in table

1234
5678
0
 
LVL 1

Expert Comment

by:mateojaime07
ID: 35745755
This is how i did it on a picture gallery, it displays a certain number per row before it goes to the next row and it also paginates. Hope this helps!

<script type="text/javascript">

						function showIt(imgsrc, desc){

							document.getElementById('imageshow').src=imgsrc;

							document.getElementById('descShow').value=desc;

							document.getElementById('imageshow').style.display='block';

						}

						

						function hideIt(){

							document.getElementById('imageshow').style.display='none';

						}

				

						function NewWindow(page){

							window.open(page, "View_image", "scrollbars=no,menubar=no,resizable=yes");

						}

			    	</script>

        

        <cfquery datasource="#datasource#" name="getimage_right">

        	SELECT image_loc_name, image_id, description

            FROM gallery

            WHERE image_cur_id='gallery'

        </cfquery>

		

		

		<CFPARAM NAME="image_thumb_loc_name" DEFAULT="">

        <cfparam name="description" default="">

		<CFPARAM NAME="image_loc_name" DEFAULT="">

        <cfparam name="image_select" default="6">

		<CFPARAM NAME="endrow" DEFAULT="2">

        <CFPARAM NAME="maxrows" DEFAULT="40">

        <CFPARAM NAME="set1" DEFAULT="">

        <CFPARAM NAME="set2" DEFAULT="2">

        <CFPARAM NAME="set3" DEFAULT="">

        <CFPARAM NAME="set4" DEFAULT="2">

		<CFPARAM NAME="startrow" DEFAULT="1">

		<cfparam name="nameToUse" default="title2.jpg">

		

	

		<cfform method="post" name="gallery" action="http://#urlLink#/include.cfm?action=gallery&loc=galleryview">

       

		<table border="0" cellpadding="0" cellspacing="0" height="625px">

			<tr>

				<td>

	                <table border="0" cellpadding="0" cellspacing="0" width="100%" align="center">

	                    <tr>

	                      <td colspan="2" align="center">

	                         <CFOUTPUT QUERY="getimage_right" startrow="#startrow#" maxrows="#maxrows#">

	                         	<cfset path = expandPath('/images/gallery/')>

		              

		                        <cfif !fileExists('#path##IMAGE_LOC_NAME#')>

									<cfset nameToUse = listFirst(IMAGE_LOC_NAME,'.')>

									<cfset extToUse = UCASE(listLast(IMAGE_LOC_NAME,'.'))>

									<cfset nameToUse = nameToUse & "." & extToUse>

								<cfelse>

									<cfset nameToUse = IMAGE_LOC_NAME>

								</cfif>

	                            <img src="images/gallery/#nameToUse#" border="0" alt="." title="#description#" align="middle" onmouseover="showIt(this.src, '#description#')" <!--- onmouseout="hideIt()" ---> width=50 height=50></a>

	                         </CFOUTPUT>   

	                       </td>

	                    </tr>

	                    <tr>

	                        <td align="left">

		                        <cfif startrow GT 1>

	                        	 	<a href="http://<cfoutput>#urlLink#</cfoutput>/include.cfm?action=gallery&loc=galleryview&startrow=<CFOUTPUT>#Evaluate(StartRow - MaxRows)#</CFOUTPUT>"><font class="verdana_11" onMouseOver="this.style.color = '#000000'" onMouseOut="this.style.color = '#000000'">&laquo;&nbsp;Previous Pictures</font></A>

	                        	</cfif>

							</td>

	                        <td align="right">

	                            <a href="http://<cfoutput>#urlLink#</cfoutput>/include.cfm?action=gallery&loc=galleryview&startrow=<CFOUTPUT>#Evaluate(StartRow + MaxRows)#</CFOUTPUT>"><FONT class="verdana_11" onMouseOver="this.style.color = '#000000'" onMouseOut="this.style.color = '#000000'">Next  Pictures&nbsp;&raquo;</font></A>	                        	

							</td>

	                    </tr>

	                </table>

	              </td>

	              <td>

	            	<CFOUTPUT>

	                	<table border="0" cellpadding="0" cellspacing="0" width="100%" align="center">

	                    	<tr>

	                        	<td align="center"><img src="images/gallery/#nameToUse#" id="imageshow" style="display:none" width="200" height="200"></td>

	                        </tr>

	                        <tr>

	                        	<td height="20"></td>

	                        </tr>

	                        <tr id="descShow">

	                        	<td><font class="verdana_11"> 

	                            place your mouse over the picture you would like to see and watch the picture appear to the left enlarged. Enjoy!!</font></td>

	                        </tr>

	                    </table>

	                </cfoutput>

	              </td>

	           </tr>

	       </table>

    

		</CFFORM>

Open in new window

0
 
LVL 53

Expert Comment

by:_agx_
ID: 35745828
@xeondxb - now we're back to confusing ;-)

I think what I said still applies. you need to pick a method for paginating *first*.  then you can add the display code.  but horizontal displays are actually simpler. just use the MOD operator

<table>
<tr>
<cfoutput query="someQuery">
      <td>#someColumnValue#</td>
      <!--- start a new row --->
      <cfif currentRow mod 4 eq 0>
            </tr>      
      </cfif>
</cfoutput>
</tr>
</table>

0
 
LVL 1

Expert Comment

by:mateojaime07
ID: 35746522
@agx is right using mod to end your row each time will get you the result you are looking for. My come i believe was alot more convuluted.
0
 

Author Comment

by:xeondxb
ID: 35762747
thanks for reply @mateojaime07

i tried to change this script for my layout 1st it is not solving my problem it showing all images in TD and back link always giving me error i guess i forget  to change one of ur veritable
0
 
LVL 53

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 35780174
@xeondxb - Did you see the pagination options in http:#a35745610? You could easily implement the simpler one and horizontal pagination by changing a few lines.

Simple example
<cfquery name="data" datasource="#request.MainDSN#">
            select * from tbl where cat_ID = 2 order by date DESC
</CFQUERY>

<cfset perpage = 12>
<cfparam name="url.start" default="1">
<cfif not isNumeric(url.start) or url.start lt 1 or url.start gt data.recordCount or round(url.start) neq url.start>
	<cfset url.start = 1>
</cfif>

<h2>Your Results</h2>
<table>
<tr>
<cfoutput query="data" startrow="#url.start#" maxrows="#perpage#">
      <td>(#currentrow#)  #photoFileName#</td>
      <!--- start a new row --->
      <cfif currentRow mod 4 eq 0>
            </tr>      
      </cfif>
</cfoutput>
</tr>
</table>

<p align="right">
[
<cfif url.start gt 1>
	<cfset link = cgi.script_name & "?start=" & (url.start - perpage)>
	<cfoutput><a href="#link#">Previous Page</a></cfoutput>
<cfelse>
	Previous Page
</cfif>
/
<cfif (url.start + perpage - 1) lt data.recordCount>
	<cfset link = cgi.script_name & "?start=" & (url.start + perpage)>
	<cfoutput><a href="#link#">Next Page</a></cfoutput>
<cfelse>
	Next Page
</cfif>
]
</p>

Open in new window

0
 

Author Comment

by:xeondxb
ID: 35784141
thanks @_agx_:

it is working just one one small issue i want to show small description bottom of the picture some thing like this ...


pic1                   | pic2                  | pic3
pic1Description |pic2Description |pic3Description

Thanks
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35784378
Output your description column right after the photo name

....
<td>#photoFileName#<br>#yourDescriptionColumn#</td>
<cfif currentRow mod 4 eq 0>
      </tr>      
</cfif>
....

Open in new window


0
 

Author Closing Comment

by:xeondxb
ID: 35784624
Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question