?
Solved

How to i create a drill down for my database table?

Posted on 2011-09-27
26
Medium Priority
?
614 Views
Last Modified: 2013-12-24
this is how my database table looks like

course   classname    class id   task   task no            guid(PK)
 math       algebra             143     001     addition              1
 science   PHY                 343     002      energy               2

drill down for my database

1) course ( should appear first, when the user clicks here)
2) SHOULD APPEAR BELOW COURSE THE CLASS NAME AND CLASS ID ( NEAR DOWN THE VALUES BASED ON THE COURSE SELECTION)
3) based on the user selection the task , task number should appear on the same page.


0
Comment
Question by:rambharatreddy
  • 11
  • 10
  • 2
  • +1
24 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 36716738
So you want the display to look like this?

math
... algebra
........ 001 addition (highlighted because user clicked on it)
... algebra II
........ 999 some algebra II task
... geometry

science
... PHY
....... 002 energy task
... Biology
....... 888 some biology task


Task Detail 001 Addition
Details of this task (assuming the user clicked on this task)



0
 

Author Comment

by:rambharatreddy
ID: 36716907
yes in that way It would be helpful if you guys would help me out ASAP..............
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 36716944
Use  cfoutput with the GROUP attribute, make sure to fetch from your table using the ORDER by to match the grouped columns...

select * from myTable order by course, className, task

<table>
<cfoutput query="myQuery" group="course">
  <tr>
    <td colspan="3">#myQuery.course#</td>
   </tr>
   <cfoutput group="className">
   <tr>
      <td> </td>
      <td colspan="2">#myQuery.className#</td>
   <tr>
    <cfoutput>
     <tr>
        <td></td>
        <td></td>
        <td>#MyQuery.taskNo#</td>
     </tr>
     </cfoutput>
    </cfoutput>
   </cfoutput>
 </table>

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rambharatreddy
ID: 36717165
it is only displaying the select * from but not displaying any values do i need to specify any data source????
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36717218
you need a datasource in your cfquery, is that what you're asking?

Please show your code.
0
 

Author Comment

by:rambharatreddy
ID: 36717232
<cfquery name="qryversocourse" datasource="definedlearning_verso">
select * from dbo.verso123
orderby v_course,v_classname,v_task,v_tasknumber
</cfquery>
<table>
<cfoutput query="qryversocourse" group="course">
<tr>
<td colspan="3">#qryversocourse.v_course#</td>
</tr>
<cfoutput group="className">
<tr>
<td></td>
 <td colspan="2">#qryversocourse.v_className#</td>
   <tr>
    <cfoutput>
     <tr>
        <td></td>
        <td></td>
        <td>#qryversocourse.v_taskno#</td>
     </tr>
     </cfoutput>
    </cfoutput>
   </cfoutput>
 </table>


when i am suing this code nothing is being displayed  when i execute the file in the browser?????
0
 

Author Comment

by:rambharatreddy
ID: 36717249
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36717262
Do you really have columns

 v_course  AND  course ?

You are using course in your group statement and v_course in your display...

Let's show the count and add a table border to see more...

<cfquery name="qryversocourse" datasource="definedlearning_verso">
   select * from dbo.verso123 
  orderby v_course,v_classname,v_task,v_tasknumber
</cfquery>
<cfoutput>found #qryversocourse.recordCount# records</cfoutput>
<table border="1">
<cfoutput query="qryversocourse" group="course">
<tr>
<td colspan="3">#qryversocourse.v_course#</td>
</tr>
<cfoutput group="className">
<tr>
<td></td>
 <td colspan="2">#qryversocourse.v_className#</td>
   <tr>
    <cfoutput>
     <tr>
        <td></td>
        <td></td>
        <td>#qryversocourse.v_taskno#</td>
     </tr>
     </cfoutput>
    </cfoutput>
   </cfoutput>
 </table>

Open in new window

0
 

Author Comment

by:rambharatreddy
ID: 36717281
in my mysql database i have v_course coumn,v_classname and v_tasknumber
0
 

Author Comment

by:rambharatreddy
ID: 36717299
still the same result no showing any output
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36717375
> still the same result no showing any output

nothing?   You just see a blank page??

Not even the statement   "Found X records"   ?

Is the page throwing an error, do you have debugging on?    

Try putting this at the top and see if you can see this message..
 
<h1>Hello World</h1>
<cfexit>

See if you even have the right page in your browser...
0
 

Author Comment

by:rambharatreddy
ID: 36717388
i got the output there should be a space in orderby ( order by)

but if possible caan you help me out in populating the way i put an image

like all the courses should appear.when the user click any of the courses the list of classnames in that course should appear.

please see the image. i think you will get what i wanted..
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36717456
> i got the output there should be a space in orderby ( order by)

You should have seen an error message then.  If you didn't, you must go into coldfusion admin and make sure you have debugging turned on, it's nearly impossible to develop without it.

> but if possible caan you help me out in populating the way i put an image

That is really different from my example and your initial question.
When you click on the course, do you want the page to refresh and show the list or do you want it to be like a menu showing and hiding for each one as you navigate.. (no page refresh)  
0
 

Author Comment

by:rambharatreddy
ID: 36717478
no page refresh just menu showing and hiding????????
0
 

Author Comment

by:rambharatreddy
ID: 36717555
Please help me out i am new to CF ??????????
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36717629
This part isn't coldfusion, it's javascript... first you need the framework to be able to show/hide, then you plug in your data...

Here's a small example of two tags that will show/hide more information.   The important thing here is to have the id name in this format...

divTab1,  divTab2, divTab3,  etc...

and the body div would be...

divTabBody1, divTabBody2, etc....

Try to fit this into your code with the SHow One,  Show Two being your course
and the Section One, Section Two being the item list within the appearing block...


Then post your code when you get stuck..

<script>
var divTabActive = 0
function selectDivTab(id) {
    if (divTabActive != 0) {
	  document.getElementById('divTab'+divTabActive).className='normal';
	  document.getElementById('divTabBody'+divTabActive).style.display='none';
	  }
	obj1 = document.getElementById('divTab'+id);
	obj2 = document.getElementById('divTabBody'+id);
	if (obj1 && obj2) {
	   obj1.className='active';
	   obj2.style.display='block';
	   divTabActive=id;
	   }
}
</script>

<div>
  <a id="divTab1" href="##" onclick="selectDivTab('1')">Show One</a>
</div>
<div id="divTabBody1" style="display:none;">
  <h1>Section One</h1>
</div>
<div>
  <a id="divTab2" href="##" onclick="selectDivTab('2')">Show Two</a>
</div>
<div id="divTabBody2" style="display:none;">
  <h1>Section Two</h1>
</div>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36892059
what happened with this?
0
 

Author Comment

by:rambharatreddy
ID: 36892165
i am not able to get it if possible i am attaching a word file where you can see what i want i am able to get step 1 but not the step 2.Please help me out.I am beginner to CF>
ram-task.docx
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36892261
I need your current code, it doesn't really make sense for me to start from scratch.. but better to help you tweak/update your code...
0
 

Author Comment

by:rambharatreddy
ID: 36892287


<!------select * from verso123
where v_course = '#url.v_course#'
<cfoutput>#qryversocourse.recordCount#</cfoutput>------>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="expires" content="Mon, 06 Jan 1990 00:00:01 GMT">
<cfheader name="expires" value="#now()#">
<cfheader name="pragma" value="no-cache">
<cfheader name="cache-control" value="no-cache, no-store, must-revalidate">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Defined STEM</title>
<link href="http://static.definedlearning.com/css/css3.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://static.definedlearning.com/js/jquery.tools.min.js"></script>
  <script type="text/javascript">
      var GB_ROOT_DIR = "http://stem.definedlearning.com/js/greybox/";
  </script>
  <script type="text/javascript" src="/js/greybox/AJS.js"></script>
  <script type="text/javascript" src="/js/greybox/AJS_fx.js"></script>
  <script type="text/javascript" src="/js/greybox/gb_scripts.js"></script>
  <link href="/js/greybox/gb_styles.css" rel="stylesheet" type="text/css" />
  <script type="text/javascript" src="/js/customSelect.jquery.js"></script>

  <script type="text/javascript">
  $(document).ready(function()
  {
$('select.styled').customStyle();
    //hide the all of the element with class msg_body
    $(".msg_body").hide();
    //toggle the componenet with class msg_body
    $(".msg_head").click(function()
    {
      $(this).next(".msg_body").slideToggle(600);
    });
  });
  </script>
  <script type='text/javascript'>_ajaxConfig = {'_cfscriptLocation':'/cfccore/standardsajax.cfc', '_jsscriptFolder':'/js'};</script>
  <script type='text/javascript' src='/js/ajax.js'></script>

  <script type="text/javascript">

        var current_parent_id  = '';
                    function getChildren(parent_id)
                    {
                          var tmpVarPlus1 = 'plus' + parent_id;
                          var textVal1 = document.getElementById(tmpVarPlus1).innerHTML;
                          var      tmpVar1 = 'subtax' + parent_id;
                          //just close
                          if (textVal1 == "--") {

                                document.getElementById(tmpVar1).innerHTML = '';
                                toggleText(tmpVarPlus1);
                          } else {
                          document.getElementById(tmpVar1).innerHTML = '<img src="/images/loading1.gif" height="16" width="16" style="margin-left:10px;" />';

                          current_parent_id = parent_id;
                          DWREngine._execute(_ajaxConfig._cfscriptLocation, null, 'getChildren', parent_id, getChildrenResult);
                          }
                    }
                    function getChildrenResult(r)
                    {

                          tmpVar = 'subtax' + current_parent_id;
                          tmpVarPlus = 'plus' + current_parent_id;
                          //toggle_visibility(tmpVar);
                          toggleText(tmpVarPlus);
                          //$(tmpVar).innerHTML = r;
                          document.getElementById(tmpVar).innerHTML = r;
                    }
                  function getContent(parent_id)
                    {
                          var      tmpVar1 = 'content' + parent_id;

                          document.getElementById(tmpVar1).innerHTML = '<img src="/images/loading1.gif" height="16" width="16" style="margin-left:10px;" />';

                          current_parent_id = parent_id;
                          DWREngine._execute(_ajaxConfig._cfscriptLocation, null, 'getContent', parent_id, getContentResult);

                    }
                    function getContentResult(r)
                    {

                          tmpVar = 'content' + current_parent_id;

                          document.getElementById(tmpVar).innerHTML = r;
                    }



                        function toggle_visibility(id) {
                           var e = document.getElementById(id);
                           if(e.style.display == 'block')
                              e.style.display = 'none';
                           else
                              e.style.display = 'block';
                        }

              function toggleDetails(elem,action) {
              var thisNode =  document.getElementById(elem);
              //alert(thisNode + '***');
              if (!action) {
                    if (thisNode.style.display == 'none') {
                          action = 'show';
                    } else {
                          action = 'hide';
                    }
              }

              if (action == 'show'){
                    thisNode.style.display = 'block';
                    //thisNode.style.backgroundColor  = '##CCCCCC';
              }
              else if (action == 'hide'){
                    thisNode.style.display = 'none';
                    thisNode.style.backgroundColor  = '##ffffff';
              }
        }

        function toggleText(TextElem) {
              var textVal = document.getElementById(TextElem).innerHTML;
              if (textVal == "[+]")
                    document.getElementById(TextElem).innerHTML = "--";
              else
                    document.getElementById(TextElem).innerHTML = "[+]";



        }


</script>
<style type="text/css">

span.customStyleSelectBox { font-size:11px; background-color: #f5f0de; color:#7c7c7c; padding:5px 7px; border:1px solid #e7dab0; -moz-border-radius: 5px; -webkit-border-radius: 5px;border-radius: 5px 5px; }
span.customStyleSelectBox.changed { background-color: #f0dea4; }
.customStyleSelectBoxInner { background:url(images/select_down.gif) no-repeat center right; }
</style>

  <style>
  .msg_list {
  margin: 0px;
  padding: 0px;
  width: 625px;
  }
  .msg_head {
  padding: 5px 10px;
  cursor: pointer;
  position: relative;
  background-color:#ADD8E6;
  margin:1opx;
  width:90%;

  float:left;
  margin-left:1px;
  padding:0;
  line-height:16px;
  font-weight:bold;
  background: url(../images/right_heading640.png) no-repeat bottom left;
  font-size:10px;
  display: block;

  padding-left:10px;
  text-decoration: none;
  height:40px;
  color:#057bae;

  }
  .msg_body {
  padding: 5px 30px 15px;

  float:left;
  }
</style>
<style>

      #demotip {
            display:none;
            background:transparent url(http://static.definedlearning.com/images/white_arrow.png);
            font-size:11px;
            height:90px;
            width:180px;
            padding:15px;
            color:#000;
            line-height:12px;
      }

      /* style the trigger elements */
      #demo img {
            border:0;
            cursor:pointer;
            margin:0 8px;

      }


</style>
<!-- use gif image for IE -->
<!--[if lt IE 7]>
<style>
#demotip {
      background-image:url(http://static.definedlearning.com/images/white_arrow.png);
}
</style>
<![endif]-->


<!--[if IE 7]>
      <link href="/css/IE-7-SPECIFIC.css" rel="stylesheet" type="text/css">
<![endif]-->

<!--[if lte IE 6]>
      <script type="text/javascript" src="/js/supersleight.js"></script>
<![endif]-->
      <!--[if lt IE 7]>
                    <link href="/css/IE-6-SPECIFIC.css" rel="stylesheet" type="text/css">
      <![endif]-->

</head>

<body>
<div class="main_div">
      <div class="inner_bg_div">
      <!--Header Div Start-->
    <cfinclude template="/header.cfm">
    <cfinclude template="/header_sub_nosearch.cfm">
    </div>
    <!---
    <div class="logo_div" align="center"><img src="/images/logo_tagsmall.png"" width="350" height="98" alt="Defined STEM - Inspiring Our Future" /></div>
      --->
    <!--Header Div End-->


 <div style="width:675px; float:left;">

           <div id="header">
            <div class="content_top"></div>
          <div class="content_mid">
            <div style="float:left; width:615px; margin-left:25px;display:inline;">
              <div class="content_head">Curriculum Standards Search</div>
              <div style="float:left; width:615px; margin-top:10px;">
      <ul>
      <h3>Subjects</h3>
     <div class="msg_list" align="left">
      <cfquery name="qryversocourse" datasource="definedlearning_verso">
 
      select v_course from verso123
       order by v_course

      </cfquery>
                                                   
    <cfoutput query="qryversocourse" group="v_course">
   
                                                                                 
                           <div class="msg_head">#qryversocourse.v_course#</div>
                           <!---<div id="subtax#qryversocourse.v_course#"  class="msg_head">--->
                         
                       
  </cfoutput>
        </ul>
                </div>
                </div>
               
              </div>
           </div>
      </div>
  </div>

<div id="demotip">&nbsp;</div>


<div style=" width:315px; margin-left:10px; float:left;display:inline;">
      <div style=" width:315px; float:left;display:inline;">
      <div class="teacher_head">Search </div>
    <div style=" width:315px; float:left;">
          <div class="teacher_top"></div>
        <div class="teacher_mid">
                  <div style="float:left; width:285px; margin-left:14px;display:inline;">
                   <div class="login_data_div">
                        Select your state, grade, and subject to find connections matching your curriculum standards.

               </div><!--- close login data div --->


                </div><!--- close above login data div --->
        </div><!--- close teacher_mid div --->
        <div class="teacher_btm"></div>

    </div>
</div>
</div>




    <!--Panel Div end-->
    <!--Footer Div start-->

     <cfinclude template="/footer.cfm">
    <!--Footer Div end-->
      <cfinclude template="/inc_autocomplete.cfm">

</div>
<!---
<cfoutput group="v_classname">
<tr>
<td></td>
 <td colspan="2">#qryversocourse.v_classname#</td>
   <tr>
    <cfoutput>
     <tr>
        <td></td>
        <td></td>
        <td>#qryversocourse.v_tasknumber#</td>
     </tr>
     </cfoutput>
    </cfoutput>
   </cfoutput> --->
code.docx
0
 

Expert Comment

by:brad-murray
ID: 37406469
Im pretty sure you need to apply grouping at the SQL level before using it in cfoutput.

course   classname
math       algebra
math       calculus
science   physics
science   Chemistry          

So if you wanted:

math
 - algebra
 - calculus

science
 - physics
 - chemistry

I would go about it like so...

<cfquery name='name' datasource='datasourceName'>
SELECT coursename,classname,classId
FROM {database}
GROUP BY course,classname
</cfquery>

<cfoutput group='course'>
#course#
   <cfoutput>
      #classname#
   </cfoutput>
</cfoutput>

0
 

Expert Comment

by:brad-murray
ID: 37406488
Sorry i am mistaken, you do not need to apply grouping at the sql level first.

Although doing so does make execution time faster.(railo + mysql)
0
 
LVL 15

Expert Comment

by:Jeff Perkins
ID: 38093036
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an &quot;answer&quot;.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 38093037
Wow, delete the question, really?  Look at all the work on this question - the original question was answered and then a new question was asked in the same thread.

---------   I recommend accepting this post that answered the original question -----

This post answered the original question ...

by: gdemariaPosted on 2011-09-28 at 09:53:01ID: 36716944

-------------

Then asker then changed the question   on 2011-09-28 at 10:46:10  ID: 36717388

i got the output t...but if possible caan you help me out in populating the way i put an image ...when the user click any of the courses the list of classnames in that course should appear.

Which was answered on 2011-09-28 at 11:14:29ID: 36717629
-------------


Then another expert was helping and the asker never returned to continue the thread.
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.

Question has a verified solution.

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

When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

864 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