Duplicated names (Need help!)

genzedu777 · February 14, 2011

Hi mjdamato,

Perhaps, just one question.

I realised the objective for this $query is to match the numbers of selected subject_level_id to one tutor_id

//Run query
    $tutor_id = mysqli_real_escape_string($dbc, $_GET['tutor_id']);
    $query = "SELECT tl.level_id, tl.level_name,
                     ts.subject_id, ts.subject_name, tsl.subject_level_id, 
                     IF(tosl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              INNER JOIN tutor_subject_level AS tsl
                  USING (level_id)
              INNER JOIN tutor_subject AS ts
                  USING (subject_id)
              LEFT JOIN tutor_overall_level_subject AS tosl
                  ON tosl.subject_level_id = tsl.subject_level_id
                  AND tosl.tutor_id = '{$tutor_id}'
              ORDER BY tl.level_id, ts.subject_name";
    $sql = mysqli_query($dbc, $query) or die(mysqli_error($dbc));

Now I will need to match the numbers of selected level_ids to one tutor_id, from a new table called tutor_selected_level, could it mean that I have to recreate another query call $query2, so that I will not mess up $query's results?

$query1 = "SELECT tl.level_id, tl.level_name,
                     IF(tslvl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              LEFT JOIN tutor_selected_level AS tslvl
                  ON tslvl.level_id = tl.level_id
                  AND tslvl.tutor_id='{$tutor_id}'
              ORDER BY tl.level_id, tl.level_name";
    $sql1 = mysqli_query($dbc, $query1) or die(mysqli_error($dbc));

I am not too sure if I have done the correct method, currently I have 2 while loop in this command

//Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false; //Flag to check when records change level
$level_data = array(); //Temp array to hold all the records for a level
// Line above is not actually not needed since it is defined in the IF statement below on the first record
    while($data = mysqli_fetch_array($sql)) //Iterate throug the DB results
    {
        if($current_level_id != $data['level_id']) //Determine if this level is different from the last
        {
		print_r ($data);
            $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $current_level_id = $data['level_id'];
            $subject_data = array();
        }
        //Add the current record to the $level_data array
        $subject_data[] = $data;
    }
while($data1 = mysqli_fetch_array($sql1))
{
		print_r ($data1);
		$checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $level_data = array();
		$level_data[] = $data1;
}

    //Call the createLevelCheckboxes() function to generate the HTML for the LAST level records
    $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);

Psycho · February 14, 2011

As I said, it takes me way to long to try and wrap my head around this each time you ask a different question. Backup the tables needed to a file and attach to this post.

genzedu777 · February 14, 2011

Okay. I have attached my DB. Thanks.

As I was doing some trials and errors with the code. I managed to get the checkboxes of the level checked

As you can see in example11.jpg

Pre-School, Lower Pri, Upper Pri and Lower Secondary - the levels are checked.

I have an issue, if you realised, the subjects in the levels are all the same. They are repeated subjects taken from 'Others' level. Makes me wonder what had happened. All the subjects belong to other levels are not shown.

Really appreciate your help....Thank you so much

<?php	
    function createLevelCheckboxes($subjectArray, $levelArray, $columns)
    {
        if(count($levelArray)==0) { return false; }
        $htmlOutput = '';
	foreach($levelArray as $data1)
        {
        //Display level header row
        $levelID   = $levelArray[0]['level_id'];
        $levelName = $levelArray[0]['level_name'];
	$checked = ($data1['checked'] == 1) ? '  checked="checked"' : '';
        $htmlOutput .= "<tr>\n";
        $htmlOutput .= "<td colspan=\"{$columns}\">";
        $htmlOutput .= "<input name=\"level[]\" type=\"checkbox\" id=\"level_{$levelID}\" type=\"checkbox\" {$checked} value=\"{$levelID}\">";
        $htmlOutput .= "<span class=\"zone_text_enlarge\"><label for=\"level_{$levelID}\">{$levelName}</label></span>";
        $htmlOutput .= "</td>\n";
        $htmlOutput .= "</tr>\n";
	}

        //Display each subject
        $recordCount = 0;
        foreach($subjectArray as $data)
        {
            //Increment counter
            $recordCount++;

            //Start new row if needed, 1/5 = R1 --> So create a new row
            if ($recordCount % $columns == 1)
            {
                $htmlOutput .= "<tr>\n";
            }
            
            //Display the record
            $subjectID   = $data['subject_level_id'];
            $subjectName = $data['subject_name'];
            $checked = ($data['checked'] == 1) ? '  checked="checked"' : '';
            $htmlOutput .= "  <td>\n";
            $htmlOutput .= "    <input name=\"subject_level[]\" class=\"subject_a\" type=\"checkbox\" {$checked}";
            $htmlOutput .= " id=\"subject_level_{$subjectID}\" value=\"{$subjectID}\"/>\n";
            $htmlOutput .= "    <label for=\"subject_level_{$subjectID}\" class=\"subject_1\">{$subjectName}</label>\n";
            $htmlOutput .= "</td>\n";
            
            //Close row if needed, 5/5 = 0 --> So close the row
            if ($recordCount % $columns == 0)
            {
                $htmlOutput .= "</tr>\n";
            }
        }
        //Close last row if needed
        if ($recordCount % $columns != 0)
        {
            $htmlOutput .= "</tr>\n";
        }
        return $htmlOutput;
    }

    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_error($dbc));
    
    //Run query
    $tutor_id = mysqli_real_escape_string($dbc, $_GET['tutor_id']);
    $query = "SELECT tl.level_id, tl.level_name,
                     ts.subject_id, ts.subject_name, tsl.subject_level_id, 
                     IF(tosl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              INNER JOIN tutor_subject_level AS tsl
                  USING (level_id)
              INNER JOIN tutor_subject AS ts
                  USING (subject_id)
              LEFT JOIN tutor_overall_level_subject AS tosl
                  ON tosl.subject_level_id = tsl.subject_level_id
                  AND tosl.tutor_id = '{$tutor_id}'
              ORDER BY tl.level_id, ts.subject_name";
    $sql = mysqli_query($dbc, $query) or die(mysqli_error($dbc));

$query1 = "SELECT tl.level_id, tl.level_name,
                     IF(tslvl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              LEFT JOIN tutor_selected_level AS tslvl
                  ON tslvl.level_id = tl.level_id
                  AND tslvl.tutor_id='{$tutor_id}'
              ORDER BY tl.level_id, tl.level_name";
    $sql1 = mysqli_query($dbc, $query1) or die(mysqli_error($dbc));


//Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false; //Flag to check when records change level
//$level_data = array(); //Temp array to hold all the records for a level
// Line above is not actually not needed since it is defined in the IF statement below on the first record
    while($data = mysqli_fetch_array($sql)) //Iterate throug the DB results
    {
        if($current_level_id != $data['level_id']) //Determine if this level is different from the last
        {
		print_r ($data);
            $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $current_level_id = $data['level_id'];
            $subject_data = array();
        }
        //Add the current record to the $level_data array
        $subject_data[] = $data;
    }
//$checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);

while($data1 = mysqli_fetch_array($sql1))
{
		print_r ($data1);
		$checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $level_data = array();
		$level_data[] = $data1;
}

    //Call the createLevelCheckboxes() function to generate the HTML for the LAST level records
    $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
?>

[attachment deleted by admin]

Psycho · February 14, 2011

OK, after playing around with this I am going to make a suggestino that you probably won't like. I think you *should* change your database structure. Trying to JOIN the tutor_overall_level_subject table on the rest of the tables is killing my system since you have to JOIN it only where the tutor_id is a specific value. I've tried the queries several different ways and it takes way to long to run.

I also found a problem with your database, in the tutor_overall_level_subject table you have two of the ID fields (foreign keys) set as varchars instead of ints. This caused a problem when trying to compare.

However, I will give you a solution that works with what you have. It will require two queries. I will run one query to get all the subject/levels for the tutor. Then dump those restults into an array. Then I'll run a second query to get ALL the possible subject/level combinations. Then when processing those results for the HTML ouptu I'll check each subject/level to see if it exists in the tutors list from the first query to determine if the checkbox should be checked or not.

I have tested this code against the database you provided and it seems to work as expected. It creates a page with all the levels/subjects available as checkboxes and the ones that correspond to the records in the tutor_overall_level_subject for the given tutor are checked.

There is a block of code at the top (and some int he HTML output) for testing purposes only so I could select a tutor. Also, I added the code to update the changes according to the checkboxes selected. However, the "Level" checkboxesd are NOT used in the updating process. You can use JavaScript to handle the checking/unchecking of the child records. Plus, you can add code to the processing to add all the child records if the parent is selected.

<?php
     $dbc = mysqli_connect('localhost', 'root', '', 'XXX') or die(mysqli_error($dbc));

    //THIS SECTION FOR TESTING PURPOSES ONLY
    if(!isset($_GET['tutor_id']) && !isset($_GET['tutor_id']))
    {
        $query = "SELECT tutor_id
                  FROM tutor_login";
        $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));

        $tutor_options = '';
        while($row = mysqli_fetch_assoc($result))
        {
            $tutor_options .= "<option value=\"{$row['tutor_id']}\">{$row['tutor_id']}</option>\n";
        }
        echo "<html>\n";
        echo "<body>\n";
        echo "<form action=\"\" method=\"GET\">\n";
        echo "Select a tutor:";
        echo "<Select name=\"tutor_id\">\n";
        echo $tutor_options;
        echo "</select>\n";
        echo "<button type=\"submit\">Submit</button>\n";
        echo "</form>\n";
        echo "</html>\n";
        exit();
    }

    $tutor_id = (isset($_POST['tutor_id'])) ? $_POST['tutor_id'] : $_GET['tutor_id'];
    $tutor_idSQL = mysqli_real_escape_string($dbc, $tutor_id);
    if(isset($_POST['tutor_id']))
    {
        //User submitted form process the changes
        //Delete current level/subjects for tutor
        $query = "DELETE FROM tutor_overall_level_subject
                  WHERE tutor_id = '$tutor_idSQL'";
        $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
        //Add new level/subjects for tutor
        //Generate VALUE(s) from POST data for the INSERT query
        $values = array();
        foreach($_POST['subject_level'] as $subjectLevel)
        {
            $subjectLevel = (int) $subjectLevel;
            $values[] = "('$tutor_idSQL', $subjectLevel)";
        }
        //Create and run query with all the values to be added
        $query = "INSERT INTO tutor_overall_level_subject
                      (tutor_id, subject_level_id)
                  VALUES " . implode(', ', $values);
        $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc)."\n<br>$query");
    }

    function createLevelCheckboxes($levelArray, $columns)
    {
        if(count($levelArray)==0) { return false; }
        $htmlOutput = '';
        //Display level header row
        $levelID   = $levelArray[0]['level_id'];
        $levelName = $levelArray[0]['level_name'];
        $htmlOutput .= "<tr>\n";
        $htmlOutput .= "<th colspan=\"{$columns}\" style=\"text-align:left;padding-top:15px;\">";
        $htmlOutput .= "<input name=\"level[]\" type=\"checkbox\" id=\"level_{$levelID}\" value=\"{$levelID}\">";
        $htmlOutput .= "<span class=\"zone_text_enlarge\"><label for=\"level_{$levelID}\">{$levelName}</label></span>";
        $htmlOutput .= "</th>\n";
        $htmlOutput .= "</tr>\n";
        //Display each subject
        $recordCount = 0;
        foreach($levelArray as $data)
        {
            //Increment counter
            $recordCount++;
  
            //Start new row if needed
            if ($recordCount % $columns == 1)
            {
                $htmlOutput .= "<tr>\n";
            }
             
            //Display the record
            $subjectID   = $data['subject_level_id'];
            $subjectName = $data['subject_name'];
            $checked = ($data['checked']) ? '  checked="checked"' : '';
            $htmlOutput .= "  <td>";
            $htmlOutput .= "    <input name=\"subject_level[]\" class=\"subject_a\" type=\"checkbox\" {$checked}";
            $htmlOutput .= " id=\"subject_level_{$subjectID}\" value=\"{$subjectID}\"/>\n";
            $htmlOutput .= "    <label for=\"subject_level_{$subjectID}\" class=\"subject_1\">{$subjectName}</label>\n";
            $htmlOutput .= "</td>\n";
            
            //Close row if needed
            if ($recordCount % $columns == 0)
            {
                $htmlOutput .= "</tr>\n";
            }
        }
        //Close last row if needed
        if ($recordCount % $columns != 0)
        {
            $htmlOutput .= "</tr>\n";
        }
        return $htmlOutput;
    }
  
    //Run query to get subjects/levels of the selected tutor
    $query = "SELECT subject_level_id
              FROM tutor_overall_level_subject AS tols
              WHERE tols.tutor_id = '$tutor_idSQL'";
    $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
    //Put results into an array
    $tutor_subject_levels = array();
    while($row = mysqli_fetch_assoc($result))
    {
        array_push($tutor_subject_levels, $row['subject_level_id']);
    }
    //Run query to get ALL subject/level checkboxes
    $query = "SELECT tsl.subject_level_id,
                     tl.level_id, tl.level_name,
                     ts.subject_id, ts.subject_name
              FROM tutor_subject_level AS tsl
              INNER JOIN tutor_level AS tl
                  USING (level_id)
              INNER JOIN tutor_subject AS ts
                  USING (subject_id)
              ORDER BY tl.level_id, ts.subject_name";
    $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
    
    //Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false;
    $level_data = array();
    while($data = mysqli_fetch_assoc($result))
    {
        //Set checked parameter if tutor has this subject/level
        $data['checked'] = (in_array((int)$data['subject_level_id'], $tutor_subject_levels));
        if($current_level_id != $data['level_id'])
        {
            $checkboxes .= createLevelCheckboxes($level_data, 5);
            $current_level_id = $data['level_id'];
            $level_data = array();
        }
        $level_data[] = $data;
    }
    $checkboxes .= createLevelCheckboxes($level_data, 5);
    
?>
<html>
<body>
<b>Level/Subject selections for <?php echo $_GET['tutor_id']; ?></b>
<br />
<a href="<?php echo $_SERVER['PHP_SELF']; ?>">Select a different tutor</a>
<form action="" method="POST">
<input type="hidden" name="tutor_id" value="<?php echo $_GET['tutor_id']; ?>" />
<table>
<?php echo $checkboxes; ?>
</table>
<br/>
<button type="submit">Submit</button>
</form>
</body>
</html>

genzedu777 · February 15, 2011

Hi mjdamato,

Really appreciate your patience and commitment in analyzing my database structure and working out the codes for me so professionally. Many thanks!!

However I realised after executing the codes, the result is similar to what was given before.

In fact, there is one missing table which was not added in the $query, that is tutor_selected_level.

I am almost done in working through your previous given codes. Perhaps to minimize the overall hassles, could we still work on your previous given code?

1) I have managed to get what the users had selected in the parent boxes (Levels) checked. ie. Pre-School, Lower Pri, Upper Pri and Lower Secondary

2) Managed to list out all the children boxes (subjects) and get it checked in accordance to the user selections

3) One issue, all the subjects listed in the children boxes belongs to one parent level 'Others'. Strange

The subjects in the levels are all the same. They are repeated subjects taken from 'Others' level. Makes me wonder what had happened. All the subjects belong to other levels are not shown. See example11.jpg

Perhaps we could work around the below codes, just to retrieve out the correct subjects for the levels. Currently I am using 3 parameters in the function. I have used '2 while loops', not too sure have I done it correctly. Thanks

   function createLevelCheckboxes($subjectArray, $levelArray, $columns)
    {
        if(count($levelArray)==0) { return false; }
        $htmlOutput = '';
	foreach($levelArray as $data1)
        {
        //Display level header row
        $levelID   = $levelArray[0]['level_id'];
        $levelName = $levelArray[0]['level_name'];
	$checked = ($data1['checked'] == 1) ? '  checked="checked"' : '';
        $htmlOutput .= "<tr>\n";
        $htmlOutput .= "<td colspan=\"{$columns}\">";
        $htmlOutput .= "<input name=\"level[]\" type=\"checkbox\" id=\"level_{$levelID}\" type=\"checkbox\" {$checked} value=\"{$levelID}\">";
        $htmlOutput .= "<span class=\"zone_text_enlarge\"><label for=\"level_{$levelID}\">{$levelName}</label></span>";
        $htmlOutput .= "</td>\n";
        $htmlOutput .= "</tr>\n";
	}

        //Display each subject
        $recordCount = 0;
        foreach($subjectArray as $data)
        {
            //Increment counter
            $recordCount++;

            //Start new row if needed, 1/5 = R1 --> So create a new row
            if ($recordCount % $columns == 1)
            {
                $htmlOutput .= "<tr>\n";
            }
            
            //Display the record
            $subjectID   = $data['subject_level_id'];
            $subjectName = $data['subject_name'];
            $checked = ($data['checked'] == 1) ? '  checked="checked"' : '';
            $htmlOutput .= "  <td>\n";
            $htmlOutput .= "    <input name=\"subject_level[]\" class=\"subject_a\" type=\"checkbox\" {$checked}";
            $htmlOutput .= " id=\"subject_level_{$subjectID}\" value=\"{$subjectID}\"/>\n";
            $htmlOutput .= "    <label for=\"subject_level_{$subjectID}\" class=\"subject_1\">{$subjectName}</label>\n";
            $htmlOutput .= "</td>\n";
            
            //Close row if needed, 5/5 = 0 --> So close the row
            if ($recordCount % $columns == 0)
            {
                $htmlOutput .= "</tr>\n";
            }
        }
        //Close last row if needed
        if ($recordCount % $columns != 0)
        {
            $htmlOutput .= "</tr>\n";
        }
        return $htmlOutput;
    }

    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_error($dbc));
    
    //Run query
    $tutor_id = mysqli_real_escape_string($dbc, $_GET['tutor_id']);
    $query = "SELECT tl.level_id, tl.level_name,
                     ts.subject_id, ts.subject_name, tsl.subject_level_id, 
                     IF(tosl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              INNER JOIN tutor_subject_level AS tsl
                  USING (level_id)
              INNER JOIN tutor_subject AS ts
                  USING (subject_id)
              LEFT JOIN tutor_overall_level_subject AS tosl
                  ON tosl.subject_level_id = tsl.subject_level_id
                  AND tosl.tutor_id = '{$tutor_id}'
              ORDER BY tl.level_id, ts.subject_name";
    $sql = mysqli_query($dbc, $query) or die(mysqli_error($dbc));

$query1 = "SELECT tl.level_id, tl.level_name,
                     IF(tslvl.tutor_id='{$tutor_id}', 1, 0) as checked
              FROM tutor_level AS tl
              LEFT JOIN tutor_selected_level AS tslvl
                  ON tslvl.level_id = tl.level_id
                  AND tslvl.tutor_id='{$tutor_id}'
              ORDER BY tl.level_id, tl.level_name";
    $sql1 = mysqli_query($dbc, $query1) or die(mysqli_error($dbc));


//Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false; //Flag to check when records change level
//$level_data = array(); //Temp array to hold all the records for a level
// Line above is not actually not needed since it is defined in the IF statement below on the first record
    while($data = mysqli_fetch_array($sql)) //Iterate throug the DB results
    {
        if($current_level_id != $data['level_id']) //Determine if this level is different from the last
        {
		print_r ($data);
            $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $current_level_id = $data['level_id'];
            $subject_data = array();
        }
        //Add the current record to the $level_data array
        $subject_data[] = $data;
    }
//$checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);

while($data1 = mysqli_fetch_array($sql1))
{
		print_r ($data1);
		$checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);
            $level_data = array();
		$level_data[] = $data1;
}

    //Call the createLevelCheckboxes() function to generate the HTML for the LAST level records
    $checkboxes .= createLevelCheckboxes($subject_data, $level_data, 5);

[attachment deleted by admin]

genzedu777 · February 15, 2011

Hi,

I dont quite understand you.

$query1 is generating 'levels' values, not 'subjects' values.

Just want to understand where does the duplicated subjects come from...

Psycho · February 15, 2011

Let's work from the code I last posted. I have it working with the db you provided. The results I was getting with the test data was what I expected - 1) All the levels and their associated levels were displayed with checkboxes and 2) the level/subject combinations where a tutor had a matching record were checked. Further, I also implemented code to update the selections when the page was submitted.

I must have missed where you explained the purpose of the tutor_selected_level table. I *think* you are using it to select all subjects for a particular level for a particular tutor. If so, that table is not needed since you can determine if all the subjects in a level are selected for the tutor or not using the data that exists in the other tables. Also - a side note - not every table needs to have an auto-incrementing ID field. For tables that merely associated data between two tables an ID field would typically have no purpose. Your database is way too complex as it is - no need to make it more so.

Anyway, after posting the code yesterday I realized there was a way to get all the data in one query. So, I have made that change in the code below. I have also updated the code to check the parent checkbox if all the subjects in the level are checked - without needing the tutor_selected_level table). Int he processing code I parse all the subjects to create their checkboxes THEN I create the level checkbox. That way I can programatically determine if all the subjects were selected.

I have no idea what you are referring to here

3) One issue, all the subjects listed in the children boxes belongs to one parent level 'Others'. Strange

The code I posted yesterday (and below) does not do that. It appears to list the appropriate subjects for each level.

NOTE: Although the code below will auto-populate the level checkbox if all the subjects for the level are selected in the DB, the code to process the form submission DOES NOT process the level header checkbox. The code could be chnged to process both the level header and subject checkboxes. But, an easier solution is to simply implement some javascript to select/unselect all subjects when a level checkbox is selected. You would want to do this anyway since it would be possible to check the level header and none of the subjects. That would pe a problematic scenario as you wouldn't know the intention of the user. So, I have also added that functionality as well. So, there may not be any need for the tutor_subject_level table.

Please use this code and ask any questions/comments regarding this. As I said I got this working using the db records you provided and it will take me too long to try and step back.

<?php
     $dbc = mysqli_connect('localhost', 'root', '', 'XXX') or die(mysqli_error($dbc));

    //THIS SECTION FOR TESTING PURPOSES ONLY
    if(!isset($_GET['tutor_id']) && !isset($_GET['tutor_id']))
    {
        $query = "SELECT tutor_id
                  FROM tutor_login";
        $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));

        $tutor_options = '';
        while($row = mysqli_fetch_assoc($result))
        {
            $tutor_options .= "<option value=\"{$row['tutor_id']}\">{$row['tutor_id']}</option>\n";
        }
        echo "<html>\n";
        echo "<body>\n";
        echo "<form action=\"\" method=\"GET\">\n";
        echo "Select a tutor:";
        echo "<Select name=\"tutor_id\">\n";
        echo $tutor_options;
        echo "</select>\n";
        echo "<button type=\"submit\">Submit</button>\n";
        echo "</form>\n";
        echo "</html>\n";
        exit();
    }

    $tutor_id = (isset($_POST['tutor_id'])) ? $_POST['tutor_id'] : $_GET['tutor_id'];

    if(isset($_POST['tutor_id']))
    {
        $tutor_idSQL = mysqli_real_escape_string($dbc, $tutor_id);
        //User submitted form process the changes
        //Delete current level/subjects for tutor
        $query = "DELETE FROM tutor_overall_level_subject
                  WHERE tutor_id = '$tutor_idSQL'";
        $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
        //Add new level/subjects for tutor
        //Generate VALUE(s) from POST data for the INSERT query
        if(isset($_POST['subject_level']) && is_array($_POST['subject_level']))
        {
            $values = array();
            foreach($_POST['subject_level'] as $subjectLevel)
            {
                $subjectLevel = (int) $subjectLevel;
                $values[] = "('$tutor_idSQL', $subjectLevel)";
            }
            //Create and run query with all the values to be added
            $query = "INSERT INTO tutor_overall_level_subject
                          (tutor_id, subject_level_id)
                      VALUES " . implode(', ', $values);
            $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc)."\n<br>$query");
        }
    }

    function createLevelCheckboxes($levelArray, $columns)
    {
        if(count($levelArray)==0) { return false; }
        $subjectsHTML = '';
        //Create output for each subject
        $subjectIDs  = array();
        $allChecked = true;
        foreach($levelArray as $data)
        {
            //Get vars
            $subjectID   = $data['subject_level_id'];
            $subjectName = $data['subject_name'];
            $recordCount = array_push($subjectIDs, $subjectID);
            
            //Start new row if needed
            if ($recordCount % $columns == 1)
            {
                $subjectsHTML .= "<tr>\n";
            }
    
            //Display the record
            if($data['checked']=='1')
            {
                $checked = ' checked="checked"';
            }
            else
            {
                $checked = '';
                $allChecked = false;
            }
            $subjectsHTML .= "  <td>";
            $subjectsHTML .= "    <input name=\"subject_level[]\" class=\"subject_a\" type=\"checkbox\"{$checked}";
            $subjectsHTML .= " id=\"subject_level_{$subjectID}\" value=\"{$subjectID}\"/>\n";
            $subjectsHTML .= "    <label for=\"subject_level_{$subjectID}\" class=\"subject_1\">{$subjectName}</label>\n";
            $subjectsHTML .= "</td>\n";
            
            //Close row if needed
            if ($recordCount % $columns == 0)
            {
                $subjectsHTML .= "</tr>\n";
            }
        }
        //Close last row if needed
        if ($recordCount % $columns != 0)
        {
            $subjectsHTML .= "</tr>\n";
        }
        
        //Create level header checkbox
        $levelHTML = '';
        $levelID   = $levelArray[0]['level_id'];
        $levelName = $levelArray[0]['level_name'];
        $checked = ($allChecked) ? '  checked="checked"' : '';
        $levelHTML .= "<tr>\n";
        $levelHTML .= "<th colspan=\"{$columns}\" style=\"text-align:left;padding-top:15px;\">";
        $levelHTML .= "<input name=\"level[]\" type=\"checkbox\" id=\"level_{$levelID}\" value=\"{$levelID}\" {$checked} ";
        $levelHTML .= " onclick=\"checkAll(this, '" . implode(',', $subjectIDs) . "');\" \>";
        $levelHTML .= "<span class=\"zone_text_enlarge\"><label for=\"level_{$levelID}\">{$levelName}</label></span>";
        $levelHTML .= "</th>\n";
        $levelHTML .= "</tr>\n";
        //Return the output
        return $levelHTML . $subjectsHTML;
    }

    //Run query to get ALL subject/level checkboxes
    $tutor_idSQL = mysqli_real_escape_string($dbc, $tutor_id);
    $query = "SELECT tsl.subject_level_id,
                     tl.level_id, tl.level_name,
                     ts.subject_id, ts.subject_name,
                     tols.checked
              FROM tutor_subject_level AS tsl
              INNER JOIN tutor_level AS tl
                  USING (level_id)
              INNER JOIN tutor_subject AS ts
                  USING (subject_id)
              LEFT JOIN (SELECT subject_level_id, 1 as checked
                         FROM tutor_overall_level_subject
                         WHERE tutor_id = '{$tutor_idSQL}') AS tols
                  USING (subject_level_id)
              ORDER BY tl.level_id, ts.subject_name";
    $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
    
    //Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false;
    $level_data = array();
    while($data = mysqli_fetch_assoc($result))
    {
        //Check if level changed from last
        if($current_level_id != $data['level_id'])
        {
            $checkboxes .= createLevelCheckboxes($level_data, 5);
            $current_level_id = $data['level_id'];
            $level_data = array();
        }
        $level_data[] = $data;
    }
    $checkboxes .= createLevelCheckboxes($level_data, 5);
    
?>
<html>
<head>
<script type="text/javascript">
function checkAll(levelObj, idList)
{
    var subIDary = idList.split(',');
    var idx = 0;
    for(idx=0, idCount=subIDary.length; idx<idCount; idx++)
    {
        document.getElementById('subject_level_'+subIDary[idx]).checked = levelObj.checked;
    }
    return;
}
</script>
</head>
<body>
<b>Level/Subject selections for <?php echo $_GET['tutor_id']; ?></b>
<br />
<a href="<?php echo $_SERVER['PHP_SELF']; ?>">Select a different tutor</a>
<form action="" method="POST">
<button type="submit">Submit</button>
<input type="hidden" name="tutor_id" value="<?php echo $_GET['tutor_id']; ?>" />
<table>
<?php echo $checkboxes; ?>
</table>
<br/>
<button type="submit">Submit</button>
</form>
</body>
</html>

genzedu777 · February 17, 2011

Hi mjdamato,

Thanks for the detailed codes. However, if the user checks on Pre-School, it should not check all the subejcts in Pre-School.

Pre-School checkbox will only be checked, and when it's checked, it doesnt affect the subjects under its level.

Perhaps, that is the reason why I have arranged an additional table called 'tutor_selected_level' to faciliate this logic.

Do you know how I should work it in your given codes?

I am still currently figuring what your codes are...Thanks

Psycho · February 17, 2011

Thanks for the detailed codes. However, if the user checks on Pre-School, it should not check all the subejcts in Pre-School. Pre-School checkbox will only be checked, and when it's checked, it doesnt affect the subjects under its level.

OK, I guess I misunderstood the intent of the header level checkboxes. I still don't understand the purpose. Let me ask you, would there ever be a situation where you would want the "Preschool" checkbox selected and the none of the subjects under preschool to be checked? In other words, is that checkbox supposed to represent that the tutor teaches one or more subjects for that level? If so, then that checkbox (and subsequent table) are still not needed since you can ascertain that information according to what subjects they are associated with.

Do you know how I should work it in your given codes?

Not without understanding the purpose of the level checkboxes. But, if it truely is independant from the subjects, then you would just handle them separately from the code I provided. However, you would need to add an additional section to the SELECT query to get that data. Thenin the update code you would need two additional queries just as I did for subjects. Except in this case you would have one to delete all the current level records for the tutor and then one to insert all the selected levels.

genzedu777 · February 18, 2011

Hi mjdamato,

Please allow me to explain. There is a purpose for it when we conduct a search throughout our database, as there are at times companies would approach us for 'Pre-School' tutors irregardless of the teaching subjects.

Hence, to have the levels' values checked is important to us.

I have dropped you an enquiry in another thread to ask you about the functionality of using 2 while loops. It will be of great help if you can shed some light in that thread. I have been stucked for almost a week already. Thank you very much

Psycho · February 18, 2011

There is a purpose for it when we conduct a search throughout our database, as there are at times companies would approach us for 'Pre-School' tutors irregardless of the teaching subjects.

Hence, to have the levels' values checked is important to us.

Again, I will ask the same question. Is there a scenario where a tutor would need to have the "Pre-School" level selected BUT not have any of the pre-school subjects selected???

If that is not a valid scenario then, as I have stated multiple times before, you can logically determine the "levels" that a tutor is qualified for based upon the subjects you ahve assigned.

genzedu777 · February 19, 2011

Again, I will ask the same question. Is there a scenario where a tutor would need to have the "Pre-School" level selected BUT not have any of the pre-school subjects selected???

Hi mjdamato,

The answer is yes.

Appreciate your next advice. Perhaps you can take a look at our registration form, with that you might have a better understanding.

http://www.championtutor.com/tutor_registration3.php

Thanks

Sign In

Duplicated names (Need help!)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information