Solved

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

Posted on 2011-09-27
26
561 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
26 Comments
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
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
Comment Utility
yes in that way It would be helpful if you guys would help me out ASAP..............
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
Comment Utility
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
 

Author Comment

by:rambharatreddy
Comment Utility
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
Comment Utility
you need a datasource in your cfquery, is that what you're asking?

Please show your code.
0
 

Author Comment

by:rambharatreddy
Comment Utility
<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
Comment Utility
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
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
Comment Utility
in my mysql database i have v_course coumn,v_classname and v_tasknumber
0
 

Author Comment

by:rambharatreddy
Comment Utility
still the same result no showing any output
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> 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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> 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
Comment Utility
no page refresh just menu showing and hiding????????
0
 

Author Comment

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

Expert Comment

by:gdemaria
Comment Utility
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
Comment Utility
what happened with this?
0
 

Author Comment

by:rambharatreddy
Comment Utility
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
Comment Utility
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
Comment Utility


<!------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
Comment Utility
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
Comment Utility
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:riteheer
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In Solr 4.0 it is possible to atomically (or partially) update individual fields in a document. This article will show the operations possible for atomic updating as well as setting up your Solr instance to be able to perform the actions. One major …
Read about why website design really matters in today's demanding market.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

763 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