Duplicated names (Need help!)

[genzedu777]

Hi guys,

 

I am currently stuck in a situation.

 

I have duplicated names in my form. View duplicated.jpg for a reference.

 

What exactly went wrong? Any help will be greatly appreciate! How do I eliminate the duplicated names. Thanks!!

 

And my $count is currently not working as well, it is supposed to be 5 columns per row.

 

<?php
/***Pre-School Level***/
echo '<input name="level[]" type="checkbox" id="level_1" value="1">';
echo '<span class="zone_text_enlarge"><label for="level_1">Pre-School</label></span><br/>';

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
or die(mysqli_error($dbc));

	$query = "SELECT sl.subject_level_id, sl.level_id, sl.subject_id, tl.level_name AS level_name, ts.subject_name AS subject_name " .
        "FROM tutor_subject_level AS sl " .
        "INNER JOIN tutor_level AS tl USING (level_id) " .
        "INNER JOIN tutor_subject AS ts USING (subject_id) " .
	"ORDER BY subject_level_id ASC LIMIT 7";

    $sql = mysqli_query($dbc, $query)
or die(mysqli_error($dbc));


$query1 = "SELECT subject_level_id FROM tutor_overall_level_subject WHERE tutor_id = '" . $_GET['tutor_id'] . "'";

$sql1 = mysqli_query($dbc, $query1)
or die(mysqli_error($dbc));

$selected_subjects = array();
	while ($row = mysqli_fetch_array($sql1)) {
		array_push($selected_subjects, $row['subject_level_id']);
	}

echo'<table><tr>'; // Start your table outside the loop... and your first row
$count = 0; // Start your counter
	while($data = mysqli_fetch_array($sql)) {
		/* 	Check to see whether or not this is a *new* row
			If it is, then end the previous and start the next and restart the counter.
		*/
		if ($count % 5 == 0) { 
			echo "</tr><tr>"; $count = 0;
		}
		foreach($selected_subjects as $selected_subject) {
			if ($data['subject_level_id'] == $selected_subject) {
				echo '<td><input name="subject_level[]" class="subject_a" type="checkbox" checked="checked" id="subject_level_'.$data['subject_level_id'].'" value="'.$data['subject_level_id'].'"/>';
				echo '<label for="subject_level_'.$data['subject_level_id'].'" class="subject_1">'.$data['subject_name'].'</label></td>';
				$count++; //Increment the count
			}
			else {
				echo '<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_'.$data['subject_level_id'].'" value="'.$data['subject_level_id'].'"/>';
				echo '<label for="subject_level_'.$data['subject_level_id'].'" class="subject_1">'.$data['subject_name'].'</label></td>';
				$count++; //Increment the count
			}
		}

	} 
echo '</tr></table><br/>'; //Close your last row and your table, outside the loop
?>

 

This is the end result of the HTML code, through 'view page source' in web browser

<input name="level[]" type="checkbox" id="level_1" value="1">

<span class="zone_text_enlarge"><label for="level_1">Pre-School</label></span><br/>

<table><tr></tr>

<tr>
<td><input name="subject_level[]" class="subject_a" type="checkbox" checked="checked" id="subject_level_1" value="1"/>
<label for="subject_level_1" class="subject_1">Mathematics</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_1" value="1"/>
<label for="subject_level_1" class="subject_1">Mathematics</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_2" value="2"/>
<label for="subject_level_2" class="subject_1">English</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_2" value="2"/>
<label for="subject_level_2" class="subject_1">English</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_3" value="3"/>
<label for="subject_level_3" class="subject_1">Chinese</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_3" value="3"/>
<label for="subject_level_3" class="subject_1">Chinese</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_4" value="4"/>
<label for="subject_level_4" class="subject_1">Tamil</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_4" value="4"/>
<label for="subject_level_4" class="subject_1">Tamil</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_5" value="5"/>
<label for="subject_level_5" class="subject_1">Malay</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_5" value="5"/>
<label for="subject_level_5" class="subject_1">Malay</label></td>
</tr>

<tr>
<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_6" value="6"/>
<label for="subject_level_6" class="subject_1">Phonics</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" checked="checked" id="subject_level_6" value="6"/>
<label for="subject_level_6" class="subject_1">Phonics</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_7" value="7"/>
<label for="subject_level_7" class="subject_1">Creative Writing</label></td>

<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_7" value="7"/>
<label for="subject_level_7" class="subject_1">Creative Writing</label></td></tr>

</table><br/> 

 

[attachment deleted by admin]

[taquitosensei]

you probably have more than one level per subject.  Run your query in your mysql client and you'll see.

[genzedu777]

Hi taquitosensei,

 

What is mysql client?

 

Currently I am always uploading my file to ftp, and view it directly via internet web browser.

[genzedu777]

What I realised is also when I removed the {else} statement, I will only get the selected names shown. View non-duplicated.jpg for reference.

 

That is basically not I want to achieve, I will like to have the entire list of names shown, and the checkboxes to be checked if the user has selected the particular subject

 

 

foreach($selected_subjects as $selected_subject) {

if ($data['subject_level_id'] == $selected_subject) {

echo '<td><input name="subject_level[]" class="subject_a" type="checkbox" checked="checked" id="subject_level_'.$data['subject_level_id'].'" value="'.$data['subject_level_id'].'"/>';

echo '<label for="subject_level_'.$data['subject_level_id'].'" class="subject_1">'.$data['subject_name'].'</label></td>';

$count++; //Increment the count

}

else if ($data['subject_level_id'] !== $selected_subject){

echo '<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_'.$data['subject_level_id'].'" value="'.$data['subject_level_id'].'"/>';

echo '<label for="subject_level_'.$data['subject_level_id'].'" class="subject_1">'.$data['subject_name'].'</label></td>';

$count++; //Increment the count

}

}

 

 

 

[attachment deleted by admin]

[Psycho]

What is mysql client?

 

Currently I am always uploading my file to ftp, and view it directly via internet web browser.

 

What?! If this is your code then you do realize you are using a mysql database, right? That code doesn't make any sense, you have one SELECT query you are running ("$query"), but then you don't do anything with the results. So, that has no purpose. You are running $query1 to get the list of subjects. That query is generating duplicates. I can't tekll you if that is because ofpoor database design or not. But, a simple fix would be to add a GROUP BY clause. But, if there is a problem with the db design or current data you will want o fix that.

 

$query1 = "SELECT subject_level_id
           FROM tutor_overall_level_subject
           WHERE tutor_id = '{$_GET['tutor_id']}'
           GROUP BY subject_level_id";

[genzedu777]

Hi mjdamato,

 

Thanks for the advice. But I dont quite get you. Appreciate any advice

 

You mentioned I'm not doing anything with my $query, correct me if I am wrong, I do use $query for a purpose, to insert $query into $sql variable, and from there I executed my while loop,

 

$query is also used to retrieve results from the database to insert values (names) into this code

echo '<td><input name="subject_level[]" class="subject_a" type="checkbox" id="subject_level_'.$data['subject_level_id'].'" value="'.$data['subject_level_id'].'"/>';
				echo '<label for="subject_level_'.$data['subject_level_id'].'" class="subject_1">'.$data['subject_name'].'</label></td>';

 

echo'<table><tr>'; // Start your table outside the loop... and your first row
   $count = 0; // Start your counter
      while($data = mysqli_fetch_array($sql))

 

$query = "SELECT sl.subject_level_id, sl.level_id, sl.subject_id, tl.level_name AS level_name, ts.subject_name AS subject_name " .
        "FROM tutor_subject_level AS sl " .
        "INNER JOIN tutor_level AS tl USING (level_id) " .
        "INNER JOIN tutor_subject AS ts USING (subject_id) " .
      "ORDER BY subject_level_id ASC LIMIT 7";
   
    $sql = mysqli_query($dbc, $query)
   or die(mysqli_error($dbc));
[code]

 

Am I doing the wrong procedure? Thanks

[Psycho]

Thanks for the advice. But I dont quite get you. Appreciate any advice

 

You mentioned I'm not doing anything with my $query, correct me if I am wrong, I do use $query for a purpose, to insert $query into $sql variable, and from there I executed my while loop

 

Am I doing the wrong procedure? Thanks

 

After looking at your code a little closer I do see you are using that query - but the code is a little disorganized. If I am reading that code correctly you are getting a list of subjects int eh first query then doing a second query to get all the subjects associated with a particular tutor. Then you are taking the first results and displaying checkboxes associated with each tutor for each subject returned in the first query. Seems odd to me. Why not run one query to get the results you need? If I understood yur tables and their relationships I could propose a query.

 

Also, your code for creating the checkboxes is overly complex. The only difference is whether the checkbox is checked or not. So, instead of having two blocks of code, just create a variable to determine whether you will check the checkbox or not. It simplifies your code and ensure it willbe consistent. Example:

        foreach($selected_subjects as $selected_subject)
            {
                $subjectID = $data['subject_level_id'];
                $checked = ($subjectID == $selected_subject) ? '  checked="checked"' : '';
                
                echo "<td>";
                echo "<input name=\"subject_level[]\" class=\"subject_a\" type=\"checkbox\" {$checked}";
                echo " id=\"subject_level_{subjectID}\" value=\"{subjectID}\"/>";
                echo "<label for=\"subject_level_{subjectID}\" class=\"subject_1\">{$data['subject_name']}</label>";
                echo "</td>\n";
                $count++; //Increment the count
        }

[genzedu777]

Hi mjdamato,

 

Thanks for the detailed explanation.

 

I have tried, it didnt get any better, worse still, there are no selected checkboxes now.

 

Previously, Mathematics and Phonics were selected, now it is not.

 

Any advice? Thanks

 

	while($data = mysqli_fetch_array($sql)) {
		/* 	Check to see whether or not this is a *new* row
			If it is, then end the previous and start the next and restart the counter.
		*/
		if ($count % 5 == 0) { 
			echo "</tr><tr>"; $count = 0;
		}
		foreach($selected_subjects as $selected_subject) {
			$subjectID = $data['subject_level_id'];
			$checked = ($subjectID == $selected_subject) ? 'checked="checked"' : '';

			echo '<td><input name="subject_level[]" class="subject_a" type="checkbox" {$checked} id="subject_level_'.$data['subject_level_id'].'" value="'.$data['subject_level_id'].'"/>';
			echo '<label for="subject_level_'.$data['subject_level_id'].'" class="subject_1">'.$data['subject_name'].'</label></td>';
			$count++; //Increment the count


		}
	} 

 

[attachment deleted by admin]

[Psycho]

Forget about the checboxes for now. You need to first solve the problem with your queries. I have rewritten the entire code based upon some assumptions of how the tables area associated. Go ahead and try it - but I'm not guaranteeing it will work. Since I can't test it as I don't have your database there may be syntax errors.

 

<?php
/***Pre-School Level***/
    
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_error($dbc));
    
    //Run query
    $query = "SELECT sl.subject_level_id, ts.subject_name,
                     IF(tols.tutor_id='{$_GET['tutor_id']}', 1, 0) as checked
              FROM tutor_subject_level AS sl
              INNER JOIN tutor_level AS tl USING (level_id)
              INNER JOIN tutor_subject AS ts USING (subject_id)
              LEFT JOIN tutor_overall_level_subject AS tols
                  ON (tols.tutor_overall_level_subject=sl.subject_level_id)
              WHERE tols.tutor_id = '{$_GET['tutor_id']}'
              ORDER BY subject_level_id ASC LIMIT 7";
    $sql = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
    
    //Process the results
    $checkboxes = ''; //Create variable for output
    $recordCount = 0; //Initiate the counter
    while($data = mysqli_fetch_array($sql))
    {
        //Increment counter
        $recordCount++;
        
        //Start new row if needed
        if ($recordCount % 5 == 1)
        {
            echo "<tr>\n";
        }
        
        //Display the record
        $subjectID = $data['subject_level_id'];
        $checked = ($data['checked'] == 1) ? '  checked="checked"' : '';
        $checkboxes .= "  <td>";
        $checkboxes .= "    <input name=\"subject_level[]\" class=\"subject_a\" type=\"checkbox\" {$checked}";
        $checkboxes .= " id=\"subject_level_{$subjectID}\" value=\"{$subjectID}\"/>\n";
        $checkboxes .= "    <label for=\"subject_level_{$subjectID}\" class=\"subject_1\">{$data['subject_name']}</label>\n";
        $checkboxes .= "</td>\n";
        
        //Close row if needed
        if ($recordCount % 5 == 0)
        {
            echo "</tr>\n";
        }
    }
    //Close last row if needed
    if ($recordCount % 5 != 0)
    {
        echo "</tr>\n";
    }
    
?>
<input name="level[]" type="checkbox" id="level_1" value="1">
<span class="zone_text_enlarge"><label for="level_1">Pre-School</label></span>
<br/>
<table>
<?php echo $checkboxes; ?>
</table><br/>

 

If that doesn't work because of syntax errors, please fix them or post the errors. If it does run, but doesn't give the correct results, then we can work of fixing the query.

[genzedu777]

Hi mjdamato,

 

Appreciate the detailed sharing. I have added the codes. It didnt totally work.

 

But at least, we managed to get the correct values. Mathematics and Phonics checkboxes are checked, the rest of the subjects' checkboxes are still missing.

 

Attempted a few times in trying to get the rest of the subjects' checkboxes out, but failed. Any advice on the next step?

 

I have attached the screenshot of my database, the 4 tables

1) tols - Overall Tutor Level Subject

2) sl - Tutor Subject Level

3) tl - Tutor Level

4) ts - Tutor Subjects

 

<?php
/***Pre-School Level***/
    
    $dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_error($dbc));
    
    //Run query
    $query = "SELECT sl.subject_level_id, ts.subject_name,  
                     IF(tols.tutor_id='{$_GET['tutor_id']}', 1, 0) as checked
              FROM tutor_subject_level AS sl
              INNER JOIN tutor_level AS tl USING (level_id)
              INNER JOIN tutor_subject AS ts USING (subject_id)
              LEFT JOIN tutor_overall_level_subject AS tols
                  ON (tols.subject_level_id=sl.subject_level_id)
              WHERE tols.tutor_id = '{$_GET['tutor_id']}'
              ORDER BY subject_level_id ASC LIMIT 7";
    $sql = mysqli_query($dbc, $query) or die(mysqli_error($dbc));
    
    //Process the results
    $checkboxes = ''; //Create variable for output
    $recordCount = 0; //Initiate the counter
    while($data = mysqli_fetch_array($sql))
    {
        //Increment counter
        $recordCount++;
        
        //Start new row if needed
        if ($recordCount % 5 == 1)
        {
            echo "<tr>\n";
        }
        
        //Display the record
        $subjectID = $data['subject_level_id'];
        $checked = ($data['checked'] == 1) ? '  checked="checked"' : '';
        $checkboxes .= "  <td>";
        $checkboxes .= "    <input name=\"subject_level[]\" class=\"subject_a\" type=\"checkbox\" {$checked}";
        $checkboxes .= " id=\"subject_level_{$subjectID}\" value=\"{$subjectID}\"/>\n";
        $checkboxes .= "    <label for=\"subject_level_{$subjectID}\" class=\"subject_1\">{$data['subject_name']}</label>\n";
        $checkboxes .= "</td>\n";
        
        //Close row if needed
        if ($recordCount % 5 == 0)
        {
            echo "</tr>\n";
        }
    }
    //Close last row if needed
    if ($recordCount % 5 != 0)
    {
        echo "</tr>\n";
    }
    
?>

<input name="level[]" type="checkbox" id="level_1" value="1">
<span class="zone_text_enlarge"><label for="level_1">Pre-School</label></span>
<br/>
<table>
<?php echo $checkboxes; ?>
</table><br/>

 

[attachment deleted by admin]

[Psycho]

OK, let's see what we can do here. After reviewing your tables I think the db design can be improved on. You have a table of subjects and a table of levels. But. then you also have a table that associates subjects and levels. You then seem to associate tutors with those records in the tutor_subject_level table.

 

Is there a reason for the tutor_subject_level table? Are certain levels only applcable to some subject? If all levels can be applicable to all subjects then I would suggest replacing that table with one to directly associate tutors to subjects and levels. (i.e. tutor_id, subject_id, level_id).

 

Anyway, using the structure you have I think the following might work. I think the problem with the previous query was how I used the WHERE clause. Based upon your code/images I am making the assumption that you want to display all the levels and all the associated subjects for each level on this page (with only the ones associated with the tutor checked). So, I have rewritten this yet again which - assuming there are no syntax errors = should generate the entire page

 

<?php

    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 .= "<td colspan=\"{$columns}\">";
        $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 .= "</td>\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'] == 1) ? '  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;
    }

    $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,
                     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_subject_level 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));
    
    //Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false;
    $level_data = array();
    while($data = mysqli_fetch_array($sql))
    {
        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);
    
?>
<table>
<?php echo $checkboxes; ?>
</table><br/>

[genzedu777]

Hi mjdamato,

 

Omg! Everything appears, even for other levels like Lower, Upper Primary subjects...all appeared. Amazinng

Yes, certain subjects are applicable to certain level. Like Economics is only applicable to 'A' level and 'IB' etc. I would really like to know how you came out with the logic, I will study your code first, currently need to go out! Will be back to study your code.

 

Oh, lastly...Now I got all the checkboxes out, the next issue will be how to UPDATE.

 

For instance, James selected Mathematics and Phonics for Pre-School, and decided to change to MatheMatics, English and Chinese.

 

My question is, how do I DELETE the record 'Phonics' from the Tutor_OVerall_level_subject, and replace it with the 3 new records - Mathematics, Eng and Chinese.

 

This is my next roadblock in coding. I will keep you posted again after trying out, meanwhile appreciate any advice from you!

 

Thank you so much!!!!

 

 

Is there a reason for the tutor_subject_level table? Are certain levels only applcable to some subject? 

 

[attachment deleted by admin]

[genzedu777]

Hi mjdamato,

 

Thank you so much for your kind help. As I am still new in php, hope you dont mind to answer some questions which I have regarding your codes.

 

1) What is this (count($levelArray) == 0) for? To ensure that there is no values in the function 'createLevelCheckboxes($levelArray, $columns)'

 

2) You first emptied $htmlOutput by declaring ='', right?

 

3) I realised at the same time, you did not declare <table>, but have written <tr> immediately, I am quite surprise it works as well, why is it so?

<?php
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 .= "<td colspan=\"{$columns}\">";
        $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 .= "</td>\n";
        $htmlOutput .= "</tr>\n";
        //Display each subject
        $recordCount = 0;
?>

 

 

1) I'm kinda of confused in this part --> 'foreach($levelArray as $data)'

I thought $levelArray is currently known as $levelID and $levelName?

        $levelID  = $levelArray[0]['level_id'];

        $levelName = $levelArray[0]['level_name'];

 

2) if ($recordCount % $columns == 1) If I can guess correctly, $columns = 5? Because outside the function (), you have declared $checkboxes .= createLevelCheckboxes($level_data, 5);, amI correct?

 

3) What does this sign mean? .=

<?php
$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'] == 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";
?>

 

I do understand  'if ($recordCount % $columns == 1)'

Which means 'if ($1 divided by $5 gives you a remainder)' THEN continue to duplicate new lines of <td>

 

'if ($recordCount % $columns == 0)'

'if ($1 divided by $5 gives you a no remainder)' THEN stop duplicating <td> AND close the row </tr>

 

I don't quite understand this...

if ($recordCount % $columns != 0)

Isn't if ($recordCount % $columns != 0) SAME AS if ($recordCount % $columns == 1)

<?php
//Start new row if needed
            if ($recordCount % $columns == 1)
            {
                $htmlOutput .= "<tr>\n";
            }

//Close row if needed
            if ($recordCount % $columns == 0)
            {
                $htmlOutput .= "</tr>\n";
            }
        }

//Close last row if needed
        if ($recordCount % $columns != 0)
        {
            $htmlOutput .= "</tr>\n";
        }
?>

 

 

Why do you need a 'return $htmlOutput;'?

return $htmlOutput;

 

 

I didn't know we can add an IF statement in SQL query

 

IF(tosl.tutor_id='{$tutor_id}', 1, 0) as checked, may I know what is this for?

And what is 1,0 for?

<?php
//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,
                     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";
?>

 

 

1) if($current_level_id != $data['level_id']), what does this mean?

For example 

Pre School level has 7 subjects, after showing the 7th subject, system understands all subjects under Pre-School had been shown, AND system will move on to create a new set of Level with the subjects --> Lower Primary. Is that what it mean?

 

 

2) You have placed $level_data  into createLevelCheckboxes($level_data, 5). However I thought previously you have declared $levelArray instead of $level_data? createLevelCheckboxes($levelArray, $columns)

I guess I am totally lost in this code, so that is the reason why I do not really understand

 

3) $level_data is used to store the name of the subjects for individual levels?

 

4) At the last line, why is $checkboxes .= createLevelCheckboxes($level_data, 5); declared again?

 

I am quite lost in this code

<?php
//Process the results
    $checkboxes = ''; //Create variable for output
    $current_level_id = false;
    $level_data = array();
    while($data = mysqli_fetch_array($sql))
    {
        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);
?>

 

Thank you so much for spending time in answering my questions, really and truly appreciate your help, and with the answers, it will definitely help me to understand better and to code well.

[genzedu777]

One more thing,

 

I have realised subject_level_'blank', it supposed to have numbers in it, like subject_level_1, subject_level_2 etc.

 

What would have caused the problem?

 

<table>

<tr>

  <td>

    <input name="subject_level[]" class="subject_a" type="checkbox"  id="subject_level_" value=""/>

    <label for="subject_level_" class="subject_1">Chinese</label>

</td>

  <td>

    <input name="subject_level[]" class="subject_a" type="checkbox"  id="subject_level_" value=""/>

    <label for="subject_level_" class="subject_1">Creative Writing</label>

</td>

 

[taquitosensei]

just an FYI judging from screenshots. phpmyadmin is your MySQL client.  You can always test your queries there to see if the output is what you're after. You can then adjust the query and test it before even touching php.

[genzedu777]

Hi taquitosensei,

 

Thank you so much for the information. But how do I test out my queries?

 

Thru the SQL tab?

 

[attachment deleted by admin]

[taquitosensei]

yes.  Just start typing and try it. You'll either get an error or it will work. If the output doesn't look like you want. Adjust the query.

[genzedu777]

Hi mjdamato, I have figured on this post which I have posted. Realized 'tsl.subject_level_id' was not in the $query, just added it, and it works.

 

One more thing,

 

I have realised subject_level_'blank', it supposed to have numbers in it, like subject_level_1, subject_level_2 etc.

 

What would have caused the problem?

 

<table>

<tr>

  <td>

    <input name="subject_level[]" class="subject_a" type="checkbox"  id="subject_level_" value=""/>

    <label for="subject_level_" class="subject_1">Chinese</label>

</td>

  <td>

    <input name="subject_level[]" class="subject_a" type="checkbox"  id="subject_level_" value=""/>

    <label for="subject_level_" class="subject_1">Creative Writing</label>

</td>

 

Hi taquitosensei,

 

Thank you so much for the advice. Will definitely bear that in mind

[genzedu777]

Hi mjdamato,

 

Moving forward, could you also guide me the methods to UPDATE?

 

Not too sure if my code for UPDATE is entirely correct

<?php
if (isset($_POST['submit'])) {
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$subjects = mysqli_real_escape_string($dbc, trim($_POST['subject_level']);

foreach($subjects as $subject) {
$query1 = "UPDATE tutor_overall_level_subject SET subject_level_id = $subject' WHERE tutor_id = '$tutor_id' ";

mysqli_query($dbc, $query1)
	or die(mysqli_error($dbc));
  }
mysqli_close($dbc);
}
?>

 

 

I have some questions.

Currently take for example, James has previously selected Mathematics (id = 1) and Phonics (id = 2). Therefore in the tutor_overall_level_subject.sql, it will reflect as

 

tutor id | subject_level_id

  1                1

  1                2

 

AND if James reselect his subjects again by logging in, this time round, perhaps

English (id = 3), Phonics (id = 2) and Sci (id = 4), how can I programme it in a way where the system knows it should DELETE id = 1 (Mathematics) and REMAIN (id = 2) Phonics, and ADD (id = 3), (id = 4)

 

 

tutor id | subject_level_id

  1                2

  1                3

  1                4

[Psycho]

1) What is this (count($levelArray) == 0) for?

In the loop that processes the records, this function is called each time a change is detected in the $current_level_id . That include the very first record since $current_level_id is first set to false. So, the array passed to the function will have no records. That line is to simply exit the function without returning anything.

 

2) You first emptied $htmlOutput by declaring ='', right?

No, not emptied. I just defined it as an empty string. In the loop I am appending additional text using .=. On the very first instance of that there would be a "notice" exception if $htmlOutput was not already defined because it would be trying to append a value to a variable that doesn't exist. In most PHP environments the notice would be ignored. But, it is just good programming practice.

 

3) I realised at the same time, you did not declare <table>, but have written <tr> immediately, I am quite surprise it works as well, why is it so?

The dynamic part of the code is only generating the table contents. The TABLE tag is in the HTML code that comes after the PHP code. This is standard practice of separating your logic (the PHP dynamic code) from the presentation (the HTML layout).

<table>
<?php echo $checkboxes; ?>
</table>

 

1) I'm kinda of confused in this part --> 'foreach($levelArray as $data)'

I thought $levelArray is currently known as $levelID and $levelName?

      $levelID  = $levelArray[0]['level_id'];

        $levelName = $levelArray[0]['level_name'];

In the loop that processes the data I build an array (called $level_data) of the data associated with each level. Then when I have all the records for a level I pass the variable to the function createLevelCheckboxes(). The function uses the variable $levelArray. I could call that variable anything I want - even use the same variable name of $level_data. But, because of variable scope it is a separate variable within the function.

 

 

2) if ($recordCount % $columns == 1) If I can guess correctly, $columns = 5? Because outside the function (), you have declared $checkboxes .= createLevelCheckboxes($level_data, 5);, amI correct?

Your terminology is not quite correct. I pass the value of 5 to the function as the second parameter. The second parameter in the function is $columns so it is set to the 2nd value passed. So, you could then change those function calls to dynamically change how many columns of checkboxes will fit in a row.

 

3) What does this sign mean? .=

That is to append a value to a string. Example:

$foo = "bar";
$foo .= "1"; //Append a value using .=
echo $foo; //Output: bar1
$foo = "2"; //Set/replace a value using  just =
echo $foo; //Output: 2

 

I do understand  'if ($recordCount % $columns == 1)'

Which means 'if ($1 divided by $5 gives you a remainder)' THEN continue to duplicate new lines of <td>

 

'if ($recordCount % $columns == 0)'

'if ($1 divided by $5 gives you a no remainder)' THEN stop duplicating <td> AND close the row </tr>

 

I don't quite understand this...

if ($recordCount % $columns != 0)

Isn't if ($recordCount % $columns != 0) SAME AS if ($recordCount % $columns == 1)

You are not understanding the modulus operator (%). It's not whether it gives you a remainder - it gives you THE remainder.

So, (assuming $columns is 5) the modulus will be 1 on the 1st, 6th, 11th, 16th, etc. records. In other words the remainder will be 1 on ever record that should start a new row. The modulus will be 0 when the modulus ($recordCount / $columns) has no remander (i.e. is perfectly divisable). That will occur on records 5, 10, 15, 20, etc. In other words, on each record that should end a row.

 

Why do you need a 'return $htmlOutput;'?

return $htmlOutput;

I am calling the function using

$checkboxes .= createLevelCheckboxes($level_data, 5);

So, I am appending the output of the function to the variable that is later used to display the output in the table (at the botton in the HTML code). If I don't "return" the code then nothing will be appended to $checkboxes.

 

I didn't know we can add an IF statement in SQL query

 

IF(tosl.tutor_id='{$tutor_id}', 1, 0) as checked, may I know what is this for?

And what is 1,0 for?

I am doing a LEFT JOIN of the tutor_overall_subject_level table. I use a LEFT JOIN because there is not a match for the tutor to every record. The LEFT JOIN ensure all the records from the LEFT table will exist even when there is no record in the right table to join to. The "value" in those instances will be null. So, I know from this query that any record that has a value in the tutor_id field is one where that tutor is associated with that level/subject. For ones where the tutor is not associate the value would be NULL. The IF statement is used to test if the value in that column is the same as the tutor id (as opposed to null). If true the value of that field (checked) in the result set will be 1, else it will be 0. I then use that field in the processing code to determine whether the checkbox should be checked or not.

 

1) if($current_level_id != $data['level_id']), what does this mean?

For example 

Pre School level has 7 subjects, after showing the 7th subject, system understands all subjects under Pre-School had been shown, AND system will move on to create a new set of Level with the subjects --> Lower Primary. Is that what it mean?

While processing the DB results I add each record to an array. I use $current_level_id as a flag/trigger to determine when the current record is a different level than the last. When that happens I call the functino to generate the output for that level and all the applicable subjects. I then clear the array so the new level records can be added and reset the flag to the current level so when the next level is detected the process can start over.

 

2) You have placed $level_data  into createLevelCheckboxes($level_data, 5). However I thought previously you have declared $levelArray instead of $level_data? createLevelCheckboxes($levelArray, $columns)

I guess I am totally lost in this code, so that is the reason why I do not really understand

You need to read up on functions. The value you pass to a function can be directly (as I do with the number 5 as the second parameter) or as a variable (as I do with $level_data), or other manners as well. When the values are passed to the function they are used to populate the parameter variables in the function. In this case I used $levelArray to accept the first parameter input. A function has no "knowledge" of variables created outside the function and the name of variables used as parameters has no correlation to any values/variables used to pass data as parameters to the function. THere are some "tricks" you can employ here which are beyond this explanation.

 

3) $level_data is used to store the name of the subjects for individual levels?

I use $level_data to add the records to until the trigger detects that the previous level was complete. It then uses that variable to send the data to the function to generate the output.

 

4) At the last line, why is $checkboxes .= createLevelCheckboxes($level_data, 5); declared again?

It is not "declared" again, it is "called" again. The reason is that the LAST level will not trigger the

if($current_level_id != $data['level_id'])

to generate the output. So, you need to add this call after the loop completes to generate the output for the last level and its subjects. As an example, suppose you only had two levels (level 1 and 2). When the first record is processed the flag $current_level_id is set to 1 and the record is added to the array $level_data. As additional records for level 1 are processed there is no change in the $current_level_id so the records are just added to the $level_data array. Now, whenthe first record for level 2 is processed, the IF statemetn detemines that there is a change in the $current_level_id and the function createLevelCheckboxes() is called to generate the output for the level 1 records. Then the $level_data array is reset to an empty array and the $current_level_id is set to 2. The remainder of the level 2 records are processed and added to the $level_data array. Since the last record will be level 2 there is no change to $current_level_id to trigger the code to generate the output. That is why we need to call createLevelCheckboxes() after all records are processed to generate the output for the LAST level.

 

Here is the code with comments

<?php
//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
        {
            //This code block is run whenever a change in level is detected to generate the HTML code
            // for that level and its subjects.
            //NOTE: This code block will be triggered on the very first record - but
            // no output will be generated by the createLevelCheckboxes() function since
            // $level_data will not have any records yet
            $checkboxes .= createLevelCheckboxes($level_data, 5);
            //Set the $current_level_id trigger so this code block is not triggered
            // until the next record with a different level id
            $current_level_id = $data['level_id'];
            //Create/reset the $level_data for the new level
            $level_data = array();
        }
        //Add the current record to the $level_data array
        $level_data[] = $data;
    }
    //Call the createLevelCheckboxes() function to generate the HTML for the LAST level records
    $checkboxes .= createLevelCheckboxes($level_data, 5);
?>

[Psycho]

Regarding the UPDATE process. There is a finite number of "subject_level_id" values that a tutor can be assigned to. AND when an "update" is made you are passing all the "checked" values. You don't directly "know" what values were unchecked that were previously checked.

 

The easiest thing to do in this instance is to DELETE all records for the tutor in the tutor_overall_level_subject table and then run a single INSERT query to add all the checked values.

 

Sample code

$tutorID = mysqli_real_escape_string($dbc, trim($_POST['tutor_id']));

//Delete all current records for tutor
$query = "DELETE FROM tutor_overall_level_subject
          WHERE tutor id = $tutorID";
mysqli_query($dbc, $query) or die(mysqli_error($dbc));

//Generate VALUE(s) from POST data for the INSERT query
$values = array();
foreach($_POST['subject_level'] as $subjectLevel)
{
    $subjectLevel = (int) $subjectLevel;
    $values[] = "($tutorID, $subjectLevel)";
}
//Create query with all the values to be added
$query = "INSERT INTO tutor_overall_level_subject
            (tutor_id, subject_level_id)
          VALUES " . implode(', ', $values);
mysqli_query($dbc, $query) or die(mysqli_error($dbc));

 

Note: if you needed to retain the originally checked records (for example you need to retain the date added) you can add just the new records and delete only the removed records. But that would require some additional steps. And, since you are not storing anything such as a date which would require that, this approach of deleting everything and only added the checked values makes more sense.

[genzedu777]

Thanks mjdamato,

 

It took me some time to understand the codes. But I got it now. Really appreciate your help!

[genzedu777]

Hi mjdamato,

 

I just realised the checkboxes in the levels are not checked, my bad. I have missed out the tutor_selected_level.sql table, therefore I have added this new table, however I received some weird results.

 

If you look into checkboxes.jpg, you will realize Pre-School, Lower Primary, Upper Primary and Lower Secondary appear, because these are the checkboxes which the user had selected.

 

The rest of the levels like, A levels, IB, Uni etc are all missing.

 

Another thing, the checkboxes are in fact not checked, but it has a comment of "type="checkbox"

 

Any advice is greatly appreciated? Thanks

 

 

I have added tslvl.level_id , INNER JOIN tutor_selected_level AS tslvl USING (level_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, tslvl.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)
		  INNER JOIN tutor_selected_level AS tslvl
                  USING (level_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));

 

 

I have added foreach($levelArray as $data1),  $checked = ($data1['checked'] == 1) ? '  checked="checked"' : '';

function createLevelCheckboxes($levelArray, $columns)
    {
        if(count($levelArray)==0) { return false; }
	foreach($levelArray as $data1) 
	{
        $htmlOutput = '';
        //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}\" value=\"{$levelID}\"> type=\"checkbox\" {$checked}";
        $htmlOutput .= "<span class=\"zone_text_enlarge\"><label for=\"level_{$levelID}\">{$levelName}</label></span>";
        $htmlOutput .= "</td>\n";
        $htmlOutput .= "</tr>\n";
        }

 

[attachment deleted by admin]

[Psycho]

To work on your problem takes a great deal of my time because I have to re-learn your data structure and work without a copy of your database to test against. I will gladly help, but I need you to provide a backup of the database to work with.

[genzedu777]

Hi mjdamato,

 

I will be more than willing to provide you a backup copy, but how do I send the entire database to you?

 

By the way, just curious. Do you provide freelance programming? haha...