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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

display of query data

Hello,

What I am looking to do is set up a program where I enter data into a database and have the data displayed in a table cell individually. I would like to be able to set the program to display so many records across before the program starts a new row. How can I do that using coldfusion?

Thanks,
Brandon
0
brandonpowell
Asked:
brandonpowell
1 Solution
 
aseusaincCommented:
something like this:

<cfset howmanycols=5>
<table>
  <tr>
    <cfoutput query="yourquery">
      <td>#recorddata#</td>
      <cfif currentrow MOD howmanycols EQ 0 AND currentrow NEQ yourquery.recordcount>
        </tr><tr>
      </cfif>
    </cfoutput>
  </tr>
</table>
0
 
javasharpCommented:
I will give you an example,suppose the query is like:

<cfquery name="myquery'>
select first_name,age,pin from mytable
</cfquery>

you can output the data like this :

<table>
    <cfloop query="myquery">
      <tr>
        <td>#first_name#</td>
         <td>#age#</td>
         <td>#pin#</td>
      </tr>
   </cfloop
</table>
0
 
trailblazzyr55Commented:
here's how you can control the display either displaying the records top to bottom or left to right

also you'll see this below in your column output

#myQuery.CarMake[queryItem]# <!--- format is: queryName.ColumnName[queryItem] --->

queryItem is really your query's currentrow, since we're modifying how you want things to display, we make a custom currentrow variable called "queryItem" so you don't need to change anything here, it'll adjust based on the display type you want and number of columns you want.

and the format for anything you want to display will always be: "queryName.ColumnName[queryItem]"

here's a visual of the display types....

top to bottom
----------------
1 5  9  13
2 6 10 14
3 7 11 15
4 8 12 16

left to right
---------------
1    2  3   4
5    6  7   8
9   10 11 12
13 14 15 16

<!--- Sample Query --->
<cfquery name="myQuery" datasource="YOUR_DSN">
SELECT CarMake FROM myCarsTable
</cfquery>

<!--- specify number of columns to show --->
<cfset colCount = 4>
<!--- TopToBottom or LeftToRight --->
<cfset displayType = "TopToBottom">

<cfset rowCount = ceiling(myQuery.recordcount/colCount)>
<cfset itemTotal = myQuery.recordcount>
<cfset queryItem = 0>

<cfoutput>
<table cellspacing="0" cellpadding="0" border="1">
      <cfloop from="1" to="#rowCount#" index="i">
      <cfif displayType is "TopToBottom">
            <cfset queryItem = i>
      </cfif>
      <tr>
            <cfloop from="1" to="#colCount#" index="ii">
            <cfswitch expression="#displayType#">
                  <cfcase value="TopToBottom">
                        <cfif ii gt 1>
                              <cfset queryItem = queryItem + colCount>
                        <cfelse>
                              <cfset queryItem = queryItem>
                        </cfif>
                  </cfcase>
                  <cfdefaultcase>
                        <cfset queryItem = queryItem + 1>
                  </cfdefaultcase>
            </cfswitch>
            <td>
                  <cfif queryItem gt itemTotal>&nbsp;
                  <cfelse>
                        #myQuery.CarMake[queryItem]# <!--- format is: queryName.ColumnName[queryItem] --->
                  </cfif>
            </td>
            </cfloop>
      </tr>
      </cfloop>
</table>
</cfoutput>

if you want to test a sample, here's an example query with the code above used to display this....

<cfset myCars = queryNew("ID,Make")>
<cfset addRow = queryAddRow(myCars,15)>
<cfset setRow = querySetCell(myCars,"ID",1,1)>
<cfset setRow = querySetCell(myCars,"Make","Honda",1)>
<cfset setRow = querySetCell(myCars,"ID",2,2)>
<cfset setRow = querySetCell(myCars,"Make","Toyota",2)>
<cfset setRow = querySetCell(myCars,"ID",3,3)>
<cfset setRow = querySetCell(myCars,"Make","Ford",3)>
<cfset setRow = querySetCell(myCars,"ID",4,4)>
<cfset setRow = querySetCell(myCars,"Make","Mitsubishi",4)>
<cfset setRow = querySetCell(myCars,"ID",5,5)>
<cfset setRow = querySetCell(myCars,"Make","Aston-Martin",5)>
<cfset setRow = querySetCell(myCars,"ID",6,6)>
<cfset setRow = querySetCell(myCars,"Make","Buick",6)>
<cfset setRow = querySetCell(myCars,"ID",7,7)>
<cfset setRow = querySetCell(myCars,"Make","Saleen",7)>
<cfset setRow = querySetCell(myCars,"ID",8,8)>
<cfset setRow = querySetCell(myCars,"Make","Mini",8)>
<cfset setRow = querySetCell(myCars,"ID",9,9)>
<cfset setRow = querySetCell(myCars,"Make","Suburu",9)>
<cfset setRow = querySetCell(myCars,"ID",10,10)>
<cfset setRow = querySetCell(myCars,"Make","Mazda",10)>
<cfset setRow = querySetCell(myCars,"ID",11,11)>
<cfset setRow = querySetCell(myCars,"Make","Chevy",11)>
<cfset setRow = querySetCell(myCars,"ID",12,12)>
<cfset setRow = querySetCell(myCars,"Make","Lincoln",12)>
<cfset setRow = querySetCell(myCars,"ID",13,13)>
<cfset setRow = querySetCell(myCars,"Make","Ferrari",13)>
<cfset setRow = querySetCell(myCars,"ID",14,14)>
<cfset setRow = querySetCell(myCars,"Make","Porsch",14)>
<cfset setRow = querySetCell(myCars,"ID",15,15)>
<cfset setRow = querySetCell(myCars,"Make","Audi",15)>

<cfquery name="myQuery" dbtype="query">
SELECT * FROM myCars
</cfquery>

<cfset colCount = 4>
<cfset rowCount = ceiling(myQuery.recordcount/colCount)>
<cfset itemTotal = myQuery.recordcount>
<cfset displayType = "TopToBottom"> <!--- TopToBottom or LeftToRight --->
<cfset queryItem = 0>

<cfoutput>
<table cellspacing="0" cellpadding="0" border="1">
      <cfloop from="1" to="#rowCount#" index="i">
      <cfif displayType is "TopToBottom">
            <cfset queryItem = i>
      </cfif>
      <tr>
            <cfloop from="1" to="#colCount#" index="ii">
            <cfswitch expression="#displayType#">
                  <cfcase value="TopToBottom">
                        <cfif ii gt 1>
                              <cfset queryItem = queryItem + colCount>
                        <cfelse>
                              <cfset queryItem = queryItem>
                        </cfif>
                  </cfcase>
                  <cfdefaultcase>
                        <cfset queryItem = queryItem + 1>
                  </cfdefaultcase>
            </cfswitch>
            <td>
                  <cfif queryItem gt itemTotal>&nbsp;
                  <cfelse>
                        #myQuery.Make[queryItem]#
                  </cfif>
            </td>
            </cfloop>
      </tr>
      </cfloop>
</table>
</cfoutput>




hope that helps,
~trail
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now