Link to home
Start Free TrialLog in
Avatar of nmarano
nmarano

asked on

UPDATE HELP

Experts,

I have an update form that I am having trouble getting it to load with the correct data.  This particular form has a Javascript function that shows checkboxes based on which grade was selected from re_grade on the form.  With the update form, the proper checkboxes load based on what was sent to the form from the database, but I can't get all of the proper checkboxes to be checked.  It seems as if only the first checkbox gets checked, and the rest are overlooked.  I did a <cfdump> to see what was being returned from my query, and three values are returned.  I've hard coded the values for each checkbox on this form like this <CFIF getsub.subcontentID EQ 11>checked="checked"</cfif>> thinking if you pull from the database and if one of the returned values is 11, then mark this box as checked.  Like I said, the issue is it is only checking one instead of all that apply to the query returned.  When the data gets inserted into the db, each checkbox that has been checked gets inserted into it's own row.    I believe I need help with a <cfloop> to loop through the results of this query

<cfquery name="getsub" datasource="aptvre">
      Select * from re_subcontenttoclass
      Where ClassID = #url.class_id#
      AND teacherID =#varteacherID#
      </cfquery>

And then run it through my cfif statements to set it properly.  Unfortunately I'm not sure how to do it.  Any help would be appreciated.  I'm going to post just the form part of this code as the page is rather long.



Thanks
Nick


<form action="re_updateClass.cfm" method="POST" name="updateclass" id="updateClass"  onSubmit="return checkForm(this)">
<table border="0" cellpadding="6">
  <tr>
    <td><div align="right"><strong>Class Name:</strong></div></td>
    <td><input name="className" type="text" id="className" value="<cfoutput>#getClass.ClassName#</cfoutput>" size="40" maxlength="60" /></td>
  </tr>
  <tr>
    <td><div align="right"><strong>Grade:</strong></div></td>
    <td>
      
      <select name="re_grade" onChange="toggleChecks(this.value)">
      <cfoutput query="getGrades">
        <option value="#getGrades.grade#" <cfif (isDefined("getClass.grade") AND #getGrades.grade# EQ #getClass.grade#)>selected="selected" </cfif> >#getGrades.grade#</option>
      </cfoutput>
    </select>
      </td>
  </tr>
  <tr>
    <td><div align="right"><strong>Number Of Students:</strong></div></td>
    <td><input name="num_students" type="text" id="num_students" value="<cfoutput>#getClass.no_students#</cfoutput>" size="40" maxlength="60" /></td>
 <tr>
      <td align="right">Sub-Content Area:</td>
      <td>        (check all subjects this class will participate in) </td>
    </tr>
    <tr>
      <td align="right">&nbsp;
      </td>
      <td>
                              <div id="Grade4_chx" style="display:none;">
<input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="1"<CFIF getsub.subcontentID EQ 11>checked="checked"</cfif>>Vocabulary<br />

<input name="chkElmNar" type="checkbox" id="chkElmNar" value="1"<CFIF getsub.subcontentID EQ 12>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkElmExpo" type="checkbox" id="chkElmExpo" value="1"<CFIF getsub.subcontentID EQ 13>checked="checked"</cfif>>Reading Expository<br />
               
              <input name="chkElmWriting" type="checkbox" id="chkElmWriting" value="1"<CFIF getsub.subcontentID EQ 14>checked="checked"</cfif>>Writing<br />
              </div>
             
              <div id="Grade5_chx" style="display:none;">  
             <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="1"<CFIF getsub.subcontentID EQ 11>checked="checked"</cfif>>Vocabulary<br />

<input name="chkElmNar" type="checkbox" id="chkElmNar" value="1"<CFIF getsub.subcontentID EQ 12>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkElmExpo" type="checkbox" id="chkElmExpo" value="1"<CFIF getsub.subcontentID EQ 13>checked="checked"</cfif>>Reading Expository<br />
               
              <input name="chkElmWriting" type="checkbox" id="chkElmWriting" value="1"<CFIF getsub.subcontentID EQ 14>checked="checked"</cfif>>Writing<br />
<!---Elem MAth--->              
                    <input name="chkElmFrac" type="checkbox" id="chkElmFrac" value="1"<CFIF getsub.subcontentID EQ 17>checked="checked"</cfif>>Fractions<br />
                 
              <input name="chkElmAlg" type="checkbox" id="chkElmAlg" value="1"<CFIF getsub.subcontentID EQ 16>checked="checked"</cfif>>Algebraic Thinking<br />
               
              <input name="chkElmMeas" type="checkbox" id="chkElmMeas" value="1"<CFIF getsub.subcontentID EQ 15>checked="checked"</cfif>>Measurement<br />
              </div>
                           
              <div id="Grade6_chx" style="display:none;">  
             <!---Elem MAth--->              
                    <input name="chkElmFrac" type="checkbox" id="chkElmFrac" value="1"<CFIF getsub.subcontentID EQ 17>checked="checked"</cfif>>Fractions<br />
                 
              <input name="chkElmAlg" type="checkbox" id="chkElmAlg" value="1"<CFIF getsub.subcontentID EQ 16>checked="checked"</cfif>>Algebraic Thinking<br />
               
              <input name="chkElmMeas" type="checkbox" id="chkElmMeas" value="1"<CFIF getsub.subcontentID EQ 15>checked="checked"</cfif>>Measurement<br />
              </div>
<!---MIDDLE SCHOOL OPTIONS--->                   
                    <div id="Grade7_chx" style="display:none;">  
              <input name="chkMidVocab" type="checkbox" id="chkMidVocab" value="1"<CFIF getsub.subcontentID EQ 21>checked="checked"</cfif>>Vocabulary<br />
                 
              <input name="chkMidNar" type="checkbox" id="chkMidNar" value="1"<CFIF getsub.subcontentID EQ 22>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkMidExpo" type="checkbox" id="chkMidExpo" value="1" <CFIF getsub.subcontentID EQ 23>checked="checked"</cfif>>Reading Expository<br />
                    
                     <input name="chkMidWriting" type="checkbox" id="chkMidWriting" value="1"  <CFIF getsub.subcontentID EQ 24>checked="checked"</cfif>>Writing<br />
              </div>
                    <div id="Grade8_chx" style="display:none;">  
              <input name="chkMidVocab" type="checkbox" id="chkMidVocab" value="1" <CFIF getsub.subcontentID EQ 21>checked="checked"</cfif>>Vocabulary<br />
                 
              <input name="chkMidNar" type="checkbox" id="chkMidNar" value="1" <CFIF getsub.subcontentID EQ 22>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkMidExpo" type="checkbox" id="chkMidExpo" value="1" <CFIF getsub.subcontentID EQ 23>checked="checked"</cfif>>Reading Expository<br />
                    
                     <input name="chkMidWriting" type="checkbox" id="chkMidWriting" value="1"<CFIF getsub.subcontentID EQ 24>checked="checked"</cfif>>Writing<br />
                    
                    <input name="chkMidPro" type="checkbox" id="chkMidPro" value="1" <CFIF getsub.subcontentID EQ 27>checked="checked"</cfif>>Proportional Reasoning<br />
                 
              <input name="chkMidFun" type="checkbox" id="chkMidFun" value="1" <CFIF getsub.subcontentID EQ 25>checked="checked"</cfif>>Functions<br />
                 
              <input name="chkMidGeo" type="checkbox" id="chkMidGeo" value="1" <CFIF getsub.subcontentID EQ 26>checked="checked"</cfif>>Geometric Measurement<br />
              </div>
                    
                    <div id="Grade9_chx" style="display:none;">  
              <input name="chkMidPro" type="checkbox" id="chkMidPro" value="1" <CFIF getsub.subcontentID EQ 27>checked="checked"</cfif>>Proportional Reasoning<br />
                 
              <input name="chkMidFun" type="checkbox" id="chkMidFun" value="1" <CFIF getsub.subcontentID EQ 25>checked="checked"</cfif> >Functions<br />
                 
                    <input name="chkMidGeo" type="checkbox" id="chkMidGeo" value="1" <CFIF getsub.subcontentID EQ 26>checked="checked"</cfif> >Geometric Measurement<br />
              </div>
            </td>
    </tr>
  <!---<tr>
    <td><div align="right"><strong>Active:</strong></div></td>
    <td>&nbsp;</td>
  </tr>--->
</table>
<p>&nbsp;<input name="teacherID" type="hidden" id="teacherID" value="<cfoutput>#varteacherID#</cfoutput>" /></p>
<p>&nbsp;<input name="classID" type="hidden" id="classID" value="<cfoutput>#getClass.classID#</cfoutput>" /></p>
<input name="show" type="submit" value="Update Class Information" />
<input type="hidden" name="MM_UpdateRecord" value="updateclass">
</p>
</form>
Avatar of nmarano
nmarano

ASKER

If I am returning multiple rows do I need to loop my query?  
Avatar of nmarano
nmarano

ASKER

Should I loop through the <CFIF getsub.subcontentID EQ 11>checked="checked"</cfif> statements in order to load the multiple rows returned from the db?  Will that load the checks properly?  
Avatar of gdemaria
gdemaria
Flag of United States of America image

 It appears that this query will return several records depending on the class and teacher you are showing on the edit page.

<cfquery name="getsub" datasource="aptvre">
 Select * from re_subcontenttoclass
 Where ClassID = #url.class_id#
 AND teacherID =#varteacherID#
</cfquery>

 However, in your CFIF statements,  you are only comparing the number against ONE record, the frist record..

<input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="1"
    <CFIF getsub.subcontentID EQ 11>checked="checked"</cfif>>Vocabulary<br />
              ^^^^^^^^  

 You don't have a loop or any one to collect and compare the multiple records coming from getSub against the number 11, you are only comparing 11 against the first record of getSub.

 Therefore, try this...


<cfquery name="getsub" datasource="aptvre">
 Select * from re_subcontenttoclass
 Where ClassID = #url.class_id#
 AND teacherID =#varteacherID#
</cfquery>

 <cfset variables.all_subcontentIDs = valueList(getSub.subcontentID)>   <!--- make a list from the query:  11,15,19,22 ---->

 Then change your CFIF to this:

 <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="1"
    <CFIF ListFind(getsub.subcontentID,11)>checked="checked"</cfif>>Vocabulary<br />



 By the way, this looks like a bug...  

 On this check box you are comparing 11 but the value is only 1... the should be the same, true?

  <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="1"  <!---- ONE ?
    <CFIF ListFind(getsub.subcontentID,11)>checked="checked"</cfif>>Vocabulary<br />
                                                       ^^^ ELEVEN ?

 All your values are 1, you should change them to the corresponding number.


 Also,  do you have a table that stores all the subcontent options?   Its much easier to pull from the table than to write each one out.

 Tell me the table (and couple pertinent columns) that holds these values:  Vocabulary, Reading Narrative, Reading Expository, etc.
 And I will show you how to reduce this code to a few lines.



 

Avatar of nmarano
nmarano

ASKER

I will change all the values.  I wasn't sure if I had to or not, but glad you're telling me to.  I will try your suggestion above.  

I finally got my way and had them change some of the table structure.  The check boxes are all done with <div> tags.  When someone selects there grade, the proper check boxes show based on the <div> tags.  Anyhow here are my tables

re_subcontent                      re_subcontenttoclass                re_class
************                  ****************              *******
subcontentID                        subcontentID                            classid
subCOnentName                   classID                                    className
subcontentLabel                    teacherID                                teacherID

Avatar of nmarano
nmarano

ASKER

gdemaria,

Am I missing something?  I've changed the code, but not all of my checkboxes that are being returned from the database, are being "checked" when the update form loads.  I'm putting all the code below so you can see the way the checkboxes get set

Thanks
Nick


<cfquery name="getClass" datasource="aptvre" >
      SELECT * FROM re_classTEST
      WHERE classID = #url.class_id#
</cfquery>

<cfquery name="getsub" datasource="aptvre">
      Select * from re_subcontenttoclass
      Where ClassID = #url.class_id#
      AND teacherID =#varteacherID#
      </cfquery>
      
      <cfset variables.all_subcontentIDs = valueList(getSub.subcontentID)>
      
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfquery name="getGrades" datasource="aptvre">
Select *
FROM re_grade
</cfquery>

<cflock type="exclusive" scope="session" timeout="30">
<cfset session.classID = #getclass.ClassID#>
<cfset session.className = #getclass.className#>
<cfset session.teacherID = #getclass.teacherID#>
</cflock>
<!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>
<title>Modify Class Information</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<link rel="stylesheet" type="text/css" href="../../styles/stylesheet.css" />
</link>

<link rel="SHORTCUT ICON" href="/favicon.ico" />
<script type="text/javascript">
function toggleChecks(val) {
  var theForm = document.getElementById("updateClass");

  var sDiv = 4; // This is the first div you want to toggle on (i.e. Grade4_chx)
  var eDiv = 9; // This is the last div you want to toggle on (i.e. Grade9_chx)
  for (var i=sDiv;i<=eDiv;i++){
    var theDiv = document.getElementById("Grade"+i+"_chx");
    theDiv.style.display ="none";
    var box = theDiv.getElementsByTagName("input");
    for(var j=0;j<box.length;j++){
      box[j].disabled=true;
    }
  }
  var theDiv = document.getElementById("Grade"+val+"_chx");
  theDiv.style.display ="block";
  var box = theDiv.getElementsByTagName("input");
  for(var j=0;j<box.length;j++){
    box[j].disabled=false;
  }
}


function checkForm(theForm){
if(trim(theForm.re_className.value)==""){
     alert("Please Enter Your Class Name.");
     theForm.re_className.focus();
     return false;
     }
if(theForm.re_grade.value==0){
     alert("Please Select a Grade.");
     theForm.re_grade.focus();
     return false;
     }
if( isNaN(parseInt(theForm.re_numStudents.value)) ){
     alert("Please type in the number of students.");
     theForm.re_numStudents.focus();
     return false;
     }
 /*idea for checking values: you need to make sure that the value of all the checkboxes added together is greater than 1, otherwise we know nothing has been checked. There are 14 possible checkboxes.*/
 /*PROBLEM: if a checkbox is programmatically set as checked, it submits as 1,1*/
return true;
}
</script>
<!--[if IE 6]>
<style type="text/css">
div#teacher_report {border-right:none;}
</style><![endif]-->
<style type="text/css">
<!--
.grayline {
      border-bottom:solid 2px #CCCC99;
      border-left:solid 1px #CCCC99;
}
-->
</style>
</head>
<body style="background-image:url(../images/efe_logo.gif); background-repeat:no-repeat; background-position: 710px 14px;"onload="toggleChecks(document.updateclass.re_grade.value);">
<div id="teacher_home" >


<div id="teacher_top" style="position:relative; float:left; top:0px; width: 630px;">
<p style="font-size:16px; font-weight:bold;"><cfoutput>Modify Class
    Information  </cfoutput></p>
<p style="margin-top:-0.5em;"><cfoutput>#DateFormat(Now(),"full")#</cfoutput>&nbsp;&nbsp;&nbsp;&nbsp;</p>
  </div>

<div id="view" style="position:relative; top:20px; float:left; width: 780px;">
 
<div class="orgbox">
<h3 style="margin-top:0.2em;">Modify <cfoutput>#getclass.className#</cfoutput>: </h3>
<p class="legal">Only change the information that needs to be changed, then press
  the &quot;Update the Class Information&quot; button.</p>
<form action="re_updateClass.cfm" method="POST" name="updateclass" id="updateClass"  onSubmit="return checkForm(this)">
<table border="0" cellpadding="6">
  <tr>
    <td><div align="right"><strong>Class Name:</strong></div></td>
    <td><input name="className" type="text" id="className" value="<cfoutput>#getClass.ClassName#</cfoutput>" size="40" maxlength="60" /></td>
  </tr>
  <tr>
    <td><div align="right"><strong>Grade:</strong></div></td>
    <td>
      
      <select name="re_grade" onChange="toggleChecks(this.value)">
      <cfoutput query="getGrades">
        <option value="#getGrades.grade#" <cfif (isDefined("getClass.grade") AND #getGrades.grade# EQ #getClass.grade#)>selected="selected" </cfif> >#getGrades.grade#</option>
      </cfoutput>
    </select>
      </td>
  </tr>
  <tr>
    <td><div align="right"><strong>Number Of Students:</strong></div></td>
    <td><input name="num_students" type="text" id="num_students" value="<cfoutput>#getClass.no_students#</cfoutput>" size="40" maxlength="60" /></td>
 <tr>
      <td align="right">Sub-Content Area:</td>
      <td>        (check all subjects this class will participate in) </td>
    </tr>
    <tr>
      <td align="right">&nbsp;
      </td>
      <td>
        <!--- New code for the update
        
        <!--- Query to pull subcontentID, and subcontentLabel --->
<cfquery name="getContentID" datasource="aptvre">
 select subContentID
  from  re_subcontent
</cfquery>
<cfdump var="#getContentID#">
      
             <cfdump var="#getsub#">
        <cfloop from="11" to="27" index="i">

        <!---<cfset variables.theValue =getsub[i][getsub.currentRow]><cfdump var="#variables.theValue#"><cfabort>--->
        <cfquery name="ContentQry" dbtype="query">
        Select * from getContentID where subContentID ='#i#'
        </cfquery><cfdump var="#i#"></cfloop><cfdump var="#ContentQry#"><cfabort>
      
      
      <cfquery name="getContentID" datasource="aptvre">
 select subContentID
  from  re_subcontent
</cfquery>
 <cfloop query="getsub">
       <Cfset counter = 1>
<cfloop from="11" to="27" index="i">
 <cfquery name="ContentQry" dbtype="query">
        Select * from getContentID where subContentID =#i#
       </cfquery><cfdump var="#ContentQry#">
<cfset counter = counter + 1>
</cfloop></cfloop>--->
                              <div id="Grade4_chx" style="display:none;">
<input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="11"<CFIF ListFind(getsub.subcontentID,11)>checked="checked"</cfif>>Vocabulary<br />


<input name="chkElmNar" type="checkbox" id="chkElmNar" value="12"<CFIF ListFind(getsub.subcontentID,12)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkElmExpo" type="checkbox" id="chkElmExpo" value="13"<CFIF ListFind(getsub.subcontentID,13)>checked="checked"</cfif>>Reading Expository<br />
               
              <input name="chkElmWriting" type="checkbox" id="chkElmWriting" value="14"<CFIF ListFind(getsub.subcontentID,14)>checked="checked"</cfif>>Writing<br />
              </div>
             
              <div id="Grade5_chx" style="display:none;">  
             <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="11"<CFIF ListFind(getsub.subcontentID,11)>checked="checked"</cfif>>Vocabulary<br />

<input name="chkElmNar" type="checkbox" id="chkElmNar" value="12"<CFIF ListFind(getsub.subcontentID,12)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkElmExpo" type="checkbox" id="chkElmExpo" value="13"<CFIF ListFind(getsub.subcontentID,13)>checked="checked"</cfif>>Reading Expository<br />
               
              <input name="chkElmWriting" type="checkbox" id="chkElmWriting" value="14"<CFIF ListFind(getsub.subcontentID,14)>checked="checked"</cfif>>Writing<br />
<!---Elem MAth--->              
                    <input name="chkElmFrac" type="checkbox" id="chkElmFrac" value="17"<CFIF ListFind(getsub.subcontentID,17)>checked="checked"</cfif>>Fractions<br />
                 
              <input name="chkElmAlg" type="checkbox" id="chkElmAlg" value="16"<CFIF ListFind(getsub.subcontentID,16)>checked="checked"</cfif>>Algebraic Thinking<br />
               
              <input name="chkElmMeas" type="checkbox" id="chkElmMeas" value="15"<CFIF ListFind(getsub.subcontentID,15)>checked="checked"</cfif>>Measurement<br />
              </div>
                           
              <div id="Grade6_chx" style="display:none;">  
             <!---Elem MAth--->              
                    <input name="chkElmFrac" type="checkbox" id="chkElmFrac" value="17"<CFIF ListFind(getsub.subcontentID,17)>checked="checked"</cfif>>Fractions<br />
                 
              <input name="chkElmAlg" type="checkbox" id="chkElmAlg" value="16"<CFIF ListFind(getsub.subcontentID,16)>checked="checked"</cfif>>Algebraic Thinking<br />
               
              <input name="chkElmMeas" type="checkbox" id="chkElmMeas" value="15"<CFIF ListFind(getsub.subcontentID,15)>checked="checked"</cfif>>Measurement<br />
              </div>
<!---MIDDLE SCHOOL OPTIONS--->                   
                    <div id="Grade7_chx" style="display:none;">  
              <input name="chkMidVocab" type="checkbox" id="chkMidVocab" value="21"<CFIF ListFind(getsub.subcontentID,21)>checked="checked"</cfif>>Vocabulary<br />
                 
              <input name="chkMidNar" type="checkbox" id="chkMidNar" value="22"<CFIF ListFind(getsub.subcontentID,22)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkMidExpo" type="checkbox" id="chkMidExpo" value="23" <CFIF ListFind(getsub.subcontentID,23)>checked="checked"</cfif>>Reading Expository<br />
                    
                     <input name="chkMidWriting" type="checkbox" id="chkMidWriting" value="24"  <CFIF ListFind(getsub.subcontentID,24)>checked="checked"</cfif>>Writing<br />
              </div>
                    <div id="Grade8_chx" style="display:none;">  
              <input name="chkMidVocab" type="checkbox" id="chkMidVocab" value="21" <CFIF ListFind(getsub.subcontentID,21)>>checked="checked"</cfif>>Vocabulary<br />
                 
              <input name="chkMidNar" type="checkbox" id="chkMidNar" value="22" <CFIF ListFind(getsub.subcontentID,22)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkMidExpo" type="checkbox" id="chkMidExpo" value="23" <CFIF ListFind(getsub.subcontentID,23)>checked="checked"</cfif>>Reading Expository<br />
                    
                     <input name="chkMidWriting" type="checkbox" id="chkMidWriting" value="24"<CFIF ListFind(getsub.subcontentID,24)>hecked="checked"</cfif>>Writing<br />
                    
                    <input name="chkMidPro" type="checkbox" id="chkMidPro" value="27" <CFIF ListFind(getsub.subcontentID,27)>checked="checked"</cfif>>Proportional Reasoning<br />
                 
              <input name="chkMidFun" type="checkbox" id="chkMidFun" value="25" <CFIF ListFind(getsub.subcontentID,25)>checked="checked"</cfif>>Functions<br />
                 
              <input name="chkMidGeo" type="checkbox" id="chkMidGeo" value="26" <CFIF ListFind(getsub.subcontentID,26)>checked="checked"</cfif>>Geometric Measurement<br />
              </div>
                    
                    <div id="Grade9_chx" style="display:none;">  
              <input name="chkMidPro" type="checkbox" id="chkMidPro" value="27" <CFIF ListFind(getsub.subcontentID,27)>checked="checked"</cfif>>Proportional Reasoning<br />
                 
              <input name="chkMidFun" type="checkbox" id="chkMidFun" value="25" <CFIF ListFind(getsub.subcontentID,25)>checked="checked"</cfif> >Functions<br />
                 
                    <input name="chkMidGeo" type="checkbox" id="chkMidGeo" value="26" <CFIF ListFind(getsub.subcontentID,26)>checked="checked"</cfif> >Geometric Measurement<br />
              </div><cfdump var="#getsub#">
                    
      
            </td>
    </tr>
  <!---<tr>
    <td><div align="right"><strong>Active:</strong></div></td>
    <td>&nbsp;</td>
  </tr>--->
</table>
<p>&nbsp;<input name="teacherID" type="hidden" id="teacherID" value="<cfoutput>#varteacherID#</cfoutput>" /></p>
<p>&nbsp;<input name="classID" type="hidden" id="classID" value="<cfoutput>#getClass.classID#</cfoutput>" /></p>
<input name="show" type="submit" value="Update Class Information" />
<input type="hidden" name="MM_UpdateRecord" value="updateclass">
</p>
</form>
<p>&nbsp;</p>
</div>
<!---Information--->
<div class="orgbox" style="border-bottom:solid 1px #CCC;" >
<p>Return to <a href="tManagement.cfm"><strong>Teacher Manager</strong></a></p>
</div>

</div>
</div>
</body>
</html>
Avatar of nmarano
nmarano

ASKER

It still only checks one checkbox instead of all that are returned

Nick
Avatar of nmarano
nmarano

ASKER

Very strange that it only checks one value instead of all that apply.  I've done a CFDUMp and it returns the proper records.   Do I need to loop through the cfifs in order to get it to find each value checked?
Avatar of gdemaria
gdemaria
Flag of United States of America image

 What does all the javascript do?   I see you're validating your form using javascript, I am not a fan of this at all.  My own opinion is not to do it that way but to validate on form submit.    The reason is that javascript cannot validate everything, some validations require database look-ups, which require the form submit,  once you add that, you suddenly have TWO validation methods which causes confusion to the user.  The other reason I don't like it is because of all the nasty little pop-up alerts.  You have an alert for every error, the user may get 6 or 7 pop-ups that he has to click before completing the form.

 Anyway, here's the example I promised that will replace a large block of your code.  
 If you can, just add this somewhere onto your page and see what it does.  It should show you all the checkboxes that you want with the right ones checked off.   You can delete a bunch of lines and replace it with this..

<cfquery name="getsub" datasource="aptvre">
 Select * from re_subcontenttoclass
 Where ClassID = #url.class_id#
 AND teacherID =#varteacherID#
</cfquery>
<!--- create a list of the checked off IDs ----->
<cfset variables.CheckedSubcontents = valueList(getSub.subcontentID)>

<cfquery name="getAllSubs" datasource="aptvre">
  select * from re_subcontent
  order by subConentName
</cfquery>

<cfoutput query="getAllSubs">
  <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="#getAllSubs.subcontentID#" <cfif listFind(variables.CheckedSubcontents,getsub.subcontentID)>checked</cfif>> #getAllSubs.subCOnentName# <br/>
</cfoutput>

Avatar of nmarano
nmarano

ASKER

The Javascript actually shows the checkboxes based on the Grade that was selected.  I tried your code, but it lists all of the checkboxes for all grades, whereas it should only show checkboxes based on the grade that is returned.  For instance, with the record that I'm using, my #getsub# query returns this...

CLASSID SUBCONTENTID TEACHERID
28           27                        60
28           24                        60
28           23                        60
28           22                        60
28          21                         60
28          26                         60
28           25                        60

my other query #getAllSubs# returns

SUBCONTENTID           SUBCONTENTNAME
16                               Algebraic Thinking
17                               Fractions
25                               Functions
26                                Geometric Measurement
15                                Measurement
27                                Proportional Reasoning
23                                 Reading Comprehension Expository
13                                Reading Comprehension Expository
12                                Reading Comprehension Narrative
22                              Reading Comprehension Narrative
11                               Vocabulary
21                             Vocabulary
14                             Writing
24                                Writing

With these two reults, the following checkboxes should be checked

25                               Functions
26                                Geometric Measurement
27                                Proportional Reasoning
23                                 Reading Comprehension Expository
22                              Reading Comprehension Narrative
21                             Vocabulary
24                                Writing

Any suggestions?

Thanks
Nickk
Avatar of gdemaria
gdemaria
Flag of United States of America image

  Are you saying you join the tables re_subcontent and re_subcontenttoclass
  to figure out which subcontent checkboxes to display?

  Like this?

<cfset variables.CheckedSubcontents = "">

<cfquery name="getAllSubs" datasource="aptvre">
 Select re_subcontent.subConentName
      , re_subcontent.subcontentID
 from re_subcontent
   inner join re_subcontenttoclass on re_subcontenttoclass.subcontentID = re_subcontent.subcontentID
         and re_subcontenttoclass.ClassID = #url.class_id#
         and re_subcontenttoclass.teacherID = #varteacherID#
</cfquery>

<cfoutput query="getAllSubs">
  <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="#getAllSubs.subcontentID#" <cfif listFind(variables.CheckedSubcontents,getsub.subcontentID)>checked</cfif>> #getAllSubs.subCOnentName# <br/>
</cfoutput>


 If someone checks one of the boxes, where in the database do you store what subcontents are checked or not checked?


Avatar of nmarano
nmarano

ASKER

When someone fills the Addclass.cfm form this is what gets submitted

<cfquery name="insertre_Class" datasource="aptvre">
    INSERT INTO re_classTEST (className, teacherID, grade, no_students)
    VALUES ('#FORM.re_className#',#FORM.hidteacherID#,'#FORM.re_grade#',#FORM.re_numStudents#)
  </cfquery>

<cfquery name="insertSubtoClass" datasource="aptvre">
               INSERT INTO re_subcontenttoclass (classID, teacherID, subcontentID)
               VALUES (#getclassID.classID#,#getteachID.teacherID#,#listgetat(listValue,counter)#)
          </cfquery>

So the subcontent that gets checked gets sent to re_subcontenttoClass table, and it inserts classID, teacherID, and the subcontentID
Avatar of nmarano
nmarano

ASKER

Sorry, so when the update form loads, it pulls the data from re_class and re_subcontenttoclass.  So if there are four seperate records in re_subcontenttoclass with the same teacherID, and classID, then it belongs to this record, and those values should fill in the proper checkboxes

Nick
Avatar of nmarano
nmarano

ASKER

Sorry for the muliple posts.  I don't use a join because I was using the Javascript with the <div> tags to fill in the proper check boxes.  
Avatar of nmarano
nmarano

ASKER

Okay,

It seems to load fine, but when I change the grade, the checkboxes don't change.  Is there a way for that to happen?
Avatar of gdemaria
gdemaria
Flag of United States of America image

 > So the subcontent that gets checked gets sent to re_subcontenttoClass table, and it inserts classID, teacherID, and the subcontentID

Ok, so that brings us back to my previous post where the checked answers are coming from the first query.

<cfquery name="getsub" datasource="aptvre">
 Select * from re_subcontenttoclass
 Where ClassID = #url.class_id#
 AND teacherID =#varteacherID#
</cfquery>
<!--- create a list of the checked off IDs ----->
<cfset variables.CheckedSubcontents = valueList(getSub.subcontentID)>

<cfquery name="getAllSubs" datasource="aptvre">
  select * from re_subcontent
  order by subConentName
</cfquery>

<cfoutput query="getAllSubs">
  <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="#getAllSubs.subcontentID#" <cfif listFind(variables.CheckedSubcontents,getsub.subcontentID)>checked</cfif>> #getAllSubs.subCOnentName# <br/>
</cfoutput>



The second query (above) is supposed to show you all the checkboxes that you want to see on the page.
 Obviously its pulling to many from the database, but I'm not sure how to filter it down for you.

 Do you have a need to do this dynamically on the page using javascript?   Are you saying when someone click on a grade you want to show / hide variables checkboxes (or disable them)?   If you knew the grade before entering the form, or if you reloaded the page when they changed the grade, you could do without javascript.    Does the record have to be saved to the database when the grade is changed?  Or are you abandoning any changes to that grade if they change the grade without submitting...

 sorry, I really don't know the "big picture" for your application..
 


Avatar of nmarano
nmarano

ASKER

Thanks for your time with this.  No I don't have to do this dynamically with Javascript, but I do have to have it work when  someone click on a grade you want to show / hide variables checkboxes, based on the grade selected.  I do know the grade before I enter the form as I can pull the grade from the re_class table.   Yes once the record is changed, I do need to save it to the database.  If someone changes anything on the form, then I do need to save the changes made.  
Avatar of nmarano
nmarano

ASKER

Would it help if I create a seperate table sith subcontentID, subcontentName, and then list each grade for each subconetntID?

Nick
Avatar of nmarano
nmarano

ASKER

Okay,

so i created a new table called re_gradetosubcontent

subcontentID subcontentName grade

Now is there a way for me to use this and set the checkboxes based on the grade that is returned from the database record?  

Select * from re_gradetosubcontent
Where grade =#getclass.grade#

And then populate the check boxes based on that?
Avatar of nmarano
nmarano

ASKER

gdemaria,

Is there a way you can help me to get my initial code to work?  I think I need to work with the original code, so that my checkboxes work properly.  The thing that I am having an issue with is getting all teh check boxes checked based on the query returned?

Nick
Avatar of nmarano
nmarano

ASKER

I've reverted back to the code that worked with the checkboxes and grade.  I'm still having trouble getting the proper checkboxes to be checked based on the query returned.  I am posting the code below, so you'll know where I'm at.  Again any help would be appreciated

Nick

<cflock scope="Session" type="ReadOnly" timeout="30" throwontimeout="no">
  <cfset MM_Username=Iif(IsDefined("Session.MM_Username"),"Session.MM_Username",DE(""))>
  <cfset MM_UserAuthorization=Iif(IsDefined("Session.MM_UserAuthorization"),"Session.MM_UserAuthorization",DE(""))>
</cflock>

<cfif MM_Username EQ "">
  <cfset MM_referer=CGI.SCRIPT_NAME>
  <cfif CGI.QUERY_STRING NEQ "">
    <cfset MM_referer=MM_referer & "?" & CGI.QUERY_STRING>
  </cfif>
  <cfset MM_failureURL="loginfail.cfm?accessdenied=" & URLEncodedFormat(MM_referer)>
  <cflocation url="#MM_failureURL#" addtoken="no">
</cfif>
<cflock type="readonly" scope="session" timeout="30">
  <br />
  <cfset varteacherid = session.teacherid>
  <cfset varclassID =session.classid>
</cflock>

<cfquery name="getClass" datasource="aptvre" >
      SELECT * FROM re_classTEST
      WHERE classID = #url.class_id#
</cfquery>

<cfquery name="getsub" datasource="aptvre">
      Select * from re_subcontenttoclass
      Where ClassID = #url.class_id#
      AND teacherID =#varteacherID#
      </cfquery>
      <cfdump var="#getsub#">
      
       <cfset variables.all_subcontentIDs = valueList(getSub.subcontentID)>
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfquery name="getGrades" datasource="aptvre">
Select *
FROM re_grade
</cfquery>

<cflock type="exclusive" scope="session" timeout="30">
<cfset session.classID = #getclass.ClassID#>
<cfset session.className = #getclass.className#>
<cfset session.teacherID = #getclass.teacherID#>
</cflock>
<!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>
<title>Modify Class Information</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<link rel="stylesheet" type="text/css" href="../../styles/stylesheet.css" />
</link>

<link rel="SHORTCUT ICON" href="/favicon.ico" />
<script type="text/javascript">
function toggleChecks(val) {
  var theForm = document.getElementById("updateClass");

  var sDiv = 4; // This is the first div you want to toggle on (i.e. Grade4_chx)
  var eDiv = 9; // This is the last div you want to toggle on (i.e. Grade9_chx)
  for (var i=sDiv;i<=eDiv;i++){
    var theDiv = document.getElementById("Grade"+i+"_chx");
    theDiv.style.display ="none";
    var box = theDiv.getElementsByTagName("input");
    for(var j=0;j<box.length;j++){
      box[j].disabled=true;
    }
  }
  var theDiv = document.getElementById("Grade"+val+"_chx");
  theDiv.style.display ="block";
  var box = theDiv.getElementsByTagName("input");
  for(var j=0;j<box.length;j++){
    box[j].disabled=false;
  }
}


function checkForm(theForm){
if(trim(theForm.re_className.value)==""){
     alert("Please Enter Your Class Name.");
     theForm.re_className.focus();
     return false;
     }
if(theForm.re_grade.value==0){
     alert("Please Select a Grade.");
     theForm.re_grade.focus();
     return false;
     }
if( isNaN(parseInt(theForm.re_numStudents.value)) ){
     alert("Please type in the number of students.");
     theForm.re_numStudents.focus();
     return false;
     }
 /*idea for checking values: you need to make sure that the value of all the checkboxes added together is greater than 1, otherwise we know nothing has been checked. There are 14 possible checkboxes.*/
 /*PROBLEM: if a checkbox is programmatically set as checked, it submits as 1,1*/
return true;
}
</script>
<!--[if IE 6]>
<style type="text/css">
div#teacher_report {border-right:none;}
</style><![endif]-->
<style type="text/css">
<!--
.grayline {
      border-bottom:solid 2px #CCCC99;
      border-left:solid 1px #CCCC99;
}
-->
</style>
</head>
<body style="background-image:url(../images/efe_logo.gif); background-repeat:no-repeat; background-position: 710px 14px;"onload="toggleChecks(document.updateclass.re_grade.value);">
<div id="teacher_home" >


<div id="teacher_top" style="position:relative; float:left; top:0px; width: 630px;">
<p style="font-size:16px; font-weight:bold;"><cfoutput>Modify Class
    Information  </cfoutput></p>
<p style="margin-top:-0.5em;"><cfoutput>#DateFormat(Now(),"full")#</cfoutput>&nbsp;&nbsp;&nbsp;&nbsp;</p>
  </div>

<div id="view" style="position:relative; top:20px; float:left; width: 780px;">
 
<div class="orgbox">
<h3 style="margin-top:0.2em;">Modify <cfoutput>#getclass.className#</cfoutput>: </h3>
<p class="legal">Only change the information that needs to be changed, then press
  the &quot;Update the Class Information&quot; button.</p>
<form action="re_updateClass.cfm" method="POST" name="updateclass" id="updateClass"  onSubmit="return checkForm(this)">
<table border="0" cellpadding="6">
  <tr>
    <td><div align="right"><strong>Class Name:</strong></div></td>
    <td><input name="className" type="text" id="className" value="<cfoutput>#getClass.ClassName#</cfoutput>" size="40" maxlength="60" /></td>
  </tr>
  <tr>
    <td><div align="right"><strong>Grade:</strong></div></td>
    <td>
      
      <select name="re_grade" onChange="toggleChecks(this.value)">
      <cfoutput query="getGrades">
        <option value="#getGrades.grade#" <cfif (isDefined("getClass.grade") AND #getGrades.grade# EQ #getClass.grade#)>selected="selected" </cfif> >#getGrades.grade#</option>
      </cfoutput>
    </select>
      </td>
  </tr>
  <tr>
    <td><div align="right"><strong>Number Of Students:</strong></div></td>
    <td><input name="num_students" type="text" id="num_students" value="<cfoutput>#getClass.no_students#</cfoutput>" size="40" maxlength="60" /></td>
 <tr>
      <td align="right">Sub-Content Area:</td>
      <td>        (check all subjects this class will participate in) </td>
    </tr>
    <tr>
      <td align="right">&nbsp;
      </td>
      <td>
              <div id="Grade4_chx" style="display:none;">
<input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="11"<CFIF ListFind(getsub.subcontentID,11)>checked="checked"</cfif>>Vocabulary<br />

<input name="chkElmNar" type="checkbox" id="chkElmNar" value="12"<CFIF ListFind(getsub.subcontentID,12)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkElmExpo" type="checkbox" id="chkElmExpo" value="13"<CFIF ListFind(getsub.subcontentID,13)>checked="checked"</cfif>>Reading Expository<br />
               
              <input name="chkElmWriting" type="checkbox" id="chkElmWriting" value="14"<CFIF ListFind(getsub.subcontentID,14)>checked="checked"</cfif>>Writing<br />
              </div>
             
              <div id="Grade5_chx" style="display:none;">  
             <input name="chkElmVoc" type="checkbox" id="chkElmVoc" value="11"<CFIF ListFind(getsub.subcontentID,11)>checked="checked"</cfif>>Vocabulary<br />

<input name="chkElmNar" type="checkbox" id="chkElmNar" value="12"<CFIF ListFind(getsub.subcontentID,12)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkElmExpo" type="checkbox" id="chkElmExpo" value="13"<CFIF ListFind(getsub.subcontentID,13)>checked="checked"</cfif>>Reading Expository<br />
               
              <input name="chkElmWriting" type="checkbox" id="chkElmWriting" value="14"<CFIF ListFind(getsub.subcontentID,14)>checked="checked"</cfif>>Writing<br />
<!---Elem MAth--->              
                    <input name="chkElmFrac" type="checkbox" id="chkElmFrac" value="17"<CFIF ListFind(getsub.subcontentID,17)>checked="checked"</cfif>>Fractions<br />
                 
              <input name="chkElmAlg" type="checkbox" id="chkElmAlg" value="16"<CFIF ListFind(getsub.subcontentID,16)>checked="checked"</cfif>>Algebraic Thinking<br />
               
              <input name="chkElmMeas" type="checkbox" id="chkElmMeas" value="15"<CFIF ListFind(getsub.subcontentID,15)>checked="checked"</cfif>>Measurement<br />
              </div>
                           
              <div id="Grade6_chx" style="display:none;">  
             <!---Elem MAth--->              
                    <input name="chkElmFrac" type="checkbox" id="chkElmFrac" value="17"<CFIF ListFind(getsub.subcontentID,17)>checked="checked"</cfif>>Fractions<br />
                 
              <input name="chkElmAlg" type="checkbox" id="chkElmAlg" value="16"<CFIF ListFind(getsub.subcontentID,16)>checked="checked"</cfif>>Algebraic Thinking<br />
               
              <input name="chkElmMeas" type="checkbox" id="chkElmMeas" value="15"<CFIF ListFind(getsub.subcontentID,15)>checked="checked"</cfif>>Measurement<br />
              </div>
<!---MIDDLE SCHOOL OPTIONS--->                   
                    <div id="Grade7_chx" style="display:none;">  
              <input name="chkMidVocab" type="checkbox" id="chkMidVocab" value="21"<CFIF ListFind(getsub.subcontentID,21)>checked="checked"</cfif>>Vocabulary<br />
                 
              <input name="chkMidNar" type="checkbox" id="chkMidNar" value="22"<CFIF ListFind(getsub.subcontentID,22)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkMidExpo" type="checkbox" id="chkMidExpo" value="23"<CFIF ListFind(getsub.subcontentID,23)>checked="checked"</cfif>>Reading Expository<br />
                    
                     <input name="chkMidWriting" type="checkbox" id="chkMidWriting" value="24"<CFIF ListFind(getsub.subcontentID,24)>checked="checked"</cfif>>Writing<br />
              </div>
                    <div id="Grade8_chx" style="display:none;">  
              <input name="chkMidVocab" type="checkbox" id="chkMidVocab" value="21"<CFIF ListFind(getsub.subcontentID,21)>checked="checked"</cfif>>Vocabulary<br />
                 
              <input name="chkMidNar" type="checkbox" id="chkMidNar" value="22" <CFIF ListFind(getsub.subcontentID,22)>checked="checked"</cfif>>Reading Narrative<br />
                 
              <input name="chkMidExpo" type="checkbox" id="chkMidExpo" value="23" <CFIF ListFind(getsub.subcontentID,23)>checked="checked"</cfif>>Reading Expository<br />
                    
                     <input name="chkMidWriting" type="checkbox" id="chkMidWriting" value="24"<CFIF ListFind(getsub.subcontentID,24)>checked="checked"</cfif>>Writing<br />
                    
                    <input name="chkMidPro" type="checkbox" id="chkMidPro" value="27" <CFIF ListFind(getsub.subcontentID,27)>checked="checked"</cfif>>Proportional Reasoning<br />
                 
              <input name="chkMidFun" type="checkbox" id="chkMidFun" value="25"<CFIF ListFind(getsub.subcontentID,25)>checked="checked"</cfif>>Functions<br />
                 
              <input name="chkMidGeo" type="checkbox" id="chkMidGeo" value="26" <CFIF ListFind(getsub.subcontentID,26)>checked="checked"</cfif>>Geometric Measurement<br />
              </div>
                    
                    <div id="Grade9_chx" style="display:none;">  
              <input name="chkMidPro" type="checkbox" id="chkMidPro" value="27" <CFIF ListFind(getsub.subcontentID,27)>checked="checked"</cfif>>Proportional Reasoning<br />
                 
              <input name="chkMidFun" type="checkbox" id="chkMidFun" value="25" <CFIF ListFind(getsub.subcontentID,25)>checked="checked"</cfif>>Functions<br />
                 
                    <input name="chkMidGeo" type="checkbox" id="chkMidGeo" value="26" <CFIF ListFind(getsub.subcontentID,26)>checked="checked"</cfif>>Geometric Measurement<br />
              </div>
                     

            </td>
    </tr>
  <!---<tr>
    <td><div align="right"><strong>Active:</strong></div></td>
    <td>&nbsp;</td>
  </tr>--->
</table>
<p>&nbsp;<input name="teacherID" type="hidden" id="teacherID" value="<cfoutput>#varteacherID#</cfoutput>" /></p>
<p>&nbsp;<input name="classID" type="hidden" id="classID" value="<cfoutput>#getClass.classID#</cfoutput>" /></p>
<input name="show" type="submit" value="Update Class Information" />
<input type="hidden" name="MM_UpdateRecord" value="updateclass">
</p>
</form>
<p>&nbsp;</p>
</div>
<!---Information--->
<div class="orgbox" style="border-bottom:solid 1px #CCC;" >
<p>Return to <a href="tManagement.cfm"><strong>Teacher Manager</strong></a></p>
</div>

</div>
</div>
</body>
</html>
Avatar of nmarano
nmarano

ASKER

Can anyone tell me if this theory would work, and if so, how I could do this?  Is there a way to count the amount of records returned from #getSub# and then loop through my CFIF statements until all check boxes are found?  I think what is happening is it is only checking the first check box returned from the query.  
Avatar of nmarano
nmarano

ASKER

gdemaria,

Sorry for all of the posts.  After further testing, it is taking the very first row returned from #getsub# and checking that one box.  Is there a way it to go through each record returned and check the appropriate box?  Again thanks for your time and patience, sorry about all of the posts

Nick
Avatar of gdemaria
gdemaria
Flag of United States of America image

 Ok, I created some "dummy" queries so I could run your code on my computer.  


I need to know what table or tables joined together will tell me which subcontent names are available for any particular grade

For example, this table:
 RE_SUBCONTENT has two columns, the data looks like this:

SUBCONTENTID           SUBCONTENTNAME
16                               Algebraic Thinking
17                               Fractions
25                               Functions
26                                Geometric Measurement
15                                Measurement
27                                Proportional Reasoning
23                                 Reading Comprehension Expository
13                                Reading Comprehension Expository
12                                Reading Comprehension Narrative
22                              Reading Comprehension Narrative
11                               Vocabulary
21                             Vocabulary
14                             Writing
24                                Writing

this seems like it should be the table, but a gradeID field is missing, are there any other fields in this table?  

So, can you provide the query that will filter this table down to show only the subcontents available for  Grade X.
If its not this table, please show the table or query that will do that...

thanks,





Avatar of nmarano
nmarano

ASKER

re_subcontent holds the subcontent info for the pages.  re_subcontenttoclass holds the particular subcontent for a particular class, and I drill down based on classID, and teacherID.  

Select *
from re_subcontenttoclass
where classID =#varclassid#
and teacherID =#varteacherID#

re_subcontenttoclass
*****************
subcontentID               classID             teacherID

Now, if needed, I can create a new table that can relate each grade with subcontent also

re_gradetosub(if needed to be created
**************
subcontentID         grade
11                          4
12                          4
13                          4
14                           4
11                          5
12                          5
22                           5
23                           5
etc etc

Again thanks
Nick
Avatar of gdemaria
gdemaria
Flag of United States of America image

> Now, if needed, I can create a new table that can relate each grade with subcontent also

based on your comments in previous posts, it seems that you need to show / hide certain subcontents based on grades.  If this is true, I think you should create that extra table.

One more thing to explore, is this table:

RE_SUBCONTENT

SUBCONTENTID           SUBCONTENTNAME
16                               Algebraic Thinking
17                               Fractions
25                               Functions
26                                Geometric Measurement
15                                Measurement
27                                Proportional Reasoning
23                                 Reading Comprehension Expository
13                                Reading Comprehension Expository
12                                Reading Comprehension Narrative
22                              Reading Comprehension Narrative
11                               Vocabulary
21                             Vocabulary
14                             Writing
24                                Writing

Why are values duplicated in this table, what is the difference between
#14 Writing and #24 Writing ?

Unless there is something else I'm not aware of this table should be changed to hold unique values in the name column


SUBCONTENTID           SUBCONTENTNAME
16                               Algebraic Thinking
17                               Fractions
26                               Geometric Measurement
15                               Measurement
27                               Proportional Reasoning
23                               Reading Comprehension Expository
12                               Reading Comprehension Narrative
11                               Vocabulary
14                               Writing

Then create the table re_gradetosub as you described in the previous post to identify which subcontents work with which grades

The table RE_SUBCONTENTTOCLASS, which show which checkboxes are checked off on the edit page.

CLASSID SUBCONTENTID TEACHERID
28           27                        60
28           24                        60
28           23                        60
28           22                        60
28          21                         60
28          26                         60
28           25                        60

Note that TeacherID does not appear to be needed in this table.   Since the teacher is at the class level, its redundant to have it here as well.  UNLESS, you are able to set a different teacher for each subcontent area, which seems perfectly reasonable.  Then you can keep the column.  But I don't see that in your code; perhaps not there yet...


If you agree with all this, I will show you how to code it..

Avatar of nmarano
nmarano

ASKER

The difference between writing and writing is elementary opposed to middle school

Thanks
Nick
Avatar of gdemaria
gdemaria
Flag of United States of America image

 Ok, but where is this distinguished?   There is no field in the RE_SUBCONTENT table to specify school level or grade?
 That is, how do I know writing 14 is for a particular grade level?

 I think you should consider do as I suggested and normalizing the data.   You will be able to tell which writing is for elementary school by using the grade join table we discussed creating.   If you "re_grade" table has a field for "school level" with values such as Elementary and Middle school, that will tell you which are available.
 
  This will have the added benefit of your being able to select "writing" and show ALL teachers who teach writing and all classes that are for writing regardless of the grade or school level.  

  However, if Writing #14 is a different topic than writing #25,  in the same way as Math - Algebra  and Math - Geometry are different, then add the additional description to the name and make them unique.

 Sorry to go down this tangent so far, but this is why coding is complicated here.  The database doesn't seem to be well structured and we are unable to pull the data we need in the right way.

 If you don't want to go down this route of changing your tables, just say so...   just offering my 2 cents..

Avatar of nmarano
nmarano

ASKER

Believe me I appreciate all of your suggestions.  Okay, what I have for tables are

re_subcontent--which just stores the actual subcontentNames, and subcontentID's.  If new subcontent areas get put into place, this is where the subcontentID gets assigned.

re_subcontenttoclass--is a table that basically related the subcontentID with the proper class

re_gradetosub--(New table I created that stores the subcontentID and grade.)  

Does this answer your question?

nick
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gdemaria
gdemaria
Flag of United States of America image

 how goes it Nick?
Avatar of nmarano
nmarano

ASKER

gdemaria,

I'm going to close this question and award you the points for all of your suggestions.  I've put up a big enough stink with my bosses that they are going to allow me to revamp all tables, and the actual forms for better code.  Again thanks for all the help and suggestions

Nick
Avatar of gdemaria
gdemaria
Flag of United States of America image

 Hey congratulations!   Let us know if you need help normalizing the data structures..
Avatar of nmarano
nmarano

ASKER

I will!  Thanks!  We launched the actual app on Wednesday with some really convoluted crappy code and data structures, but  I made it a point to let them know this is not the way to program, and they agreed to allow me to revamp all tables and code as needed.  I'm sure I'll have some question for you.  Again thanks for all the suggestions

nick