Solved

display of query data

Posted on 2006-11-01
3
146 Views
Last Modified: 2013-12-24
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
Comment
Question by:brandonpowell
3 Comments
 
LVL 7

Expert Comment

by:aseusainc
ID: 17854489
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
 
LVL 6

Expert Comment

by:javasharp
ID: 17855917
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
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 500 total points
ID: 17931207
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
remove Handler and Add Handler for one file 3 39
php.ini on ipage hosting 12 47
Remove Index.php in Codeigniter 12 55
PHP in Apache server 20 88
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now