Issues with Arranging Ticked Boxes

Question

I am doing a PHP form that requires fields to be filled up - and there is a section on editing the fields in an application form by the surfer after the form has been submitted...

The skill sets are ticked in the original page but when it comes to editing the skill set, the code I typed up shows the checked boxes arranged in a different order compared to the original page.

Here is the original application page with the checkboxes immediately after they are checked but BEFORE submission and the potential editing of the form:

And here is the application page after the fields have been submitted and processed by the server into the database if the applicant wants to edit the fields. They are in a complete disorder!

I would like the second image to produce a checkbox list just like the first when the application was being done.

The fields are selected or retrieved from a database table named skillset and compared with the values inserted from that table into another table named emprecords. By running a for loop in the emprecords table, I am able to echo or print out the skills set list (after imploding the string that separates each skill by a comma in the emprecords database) that a particular applicant has inserted but I am not able to print the selected checkboxes in the correct order for the lists of skills that were in the Array. I hope the images above will help. Here is the PHP code for the page that edits the fields for an applicant on a website:

<br><br><H2 align="center">SKILLS SET</H2>
<br>
<label for="skills" size="3">Pick Your Skill(s): </label>
<br><br>
<tr>

<table border='1' cellspacing='0'>
    <colgroup>
        <col span='1'>
    </colgroup>
    <tr>
        <td>Engineering Services</td>
        <td>Information Technologies</td>
    <tr>
        <td valign="top">
        <?php

        $id = $_GET["id"];

        $query2 = "SELECT * FROM emprecords WHERE id ='$id'";
        $record_set2 = $dbs->prepare($query2);
        $record_set2 -> execute();
        $row2 = $record_set2->fetch(PDO::FETCH_ASSOC);
        $sk = $row2['skills'];
        $skills1 = explode(",", $sk);
        for ($i=0; $i< count($skills1); $i++) {
            echo "<input type='checkbox' id='skills' name='skills[]' value='$skills1'checked>$skills1[$i]<br>"; 
        }

        $list = "
        SELECT *
        FROM skillsset
        WHERE category='Engineering'
        ORDER BY skills ASC";
        $listAHI = $dbs ->prepare($list);
        $listAHI -> execute();

        if(!isset($_POST['submitd'])) {
            while($row = $listAHI ->fetch(PDO::FETCH_ASSOC))
            {
                $skills = $row["skills"];
                echo "
                <form action='' method='post'>
                <input type='checkbox' id='skills' name='skills[]' value='$skills'>  $skills<br> ";
            }
        }
        else {
            while($row = $listAHI ->fetch(PDO::FETCH_ASSOC)) {
                $skills = $row["skills"]; 
                if(strlen($skills)>0){
                    if(isset($_POST['skills']) and in_array($skills, $_POST['skills'])) {
                        echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' checked>$skills<br>";
                    }
                    if(isset($_POST['skills']) and !in_array($skills, $_POST['skills'])){
                        echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' unchecked>$skills<br>";
                    } else {
                        if(!in_array($skills, $_POST['skills'])) {
                            echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' unchecked>$skills<br>";
                        } 
                    }
                }
                echo "</form>";
            }
        }
        ?>
        </td>
        <td valign="top">
        <?php
        $list = "
        SELECT *
        FROM skillsset
        WHERE category='Information'
        ORDER BY skills ASC";
        $listAHI = $dbs ->prepare($list);
        $listAHI -> execute();


        if(!isset($_POST['submitd'])){
            while($row = $listAHI ->fetch(PDO::FETCH_ASSOC))
            {
                $skills = $row["skills"];
                echo "
                <form action='' method='post'>
                <input type='checkbox' id='skills' name='skills[]' value='$skills'>  $skills<br> ";

            }
        }
        else {
            while($row = $listAHI ->fetch(PDO::FETCH_ASSOC)) {
                $skills = $row["skills"];
                if(strlen($skills)>0) { 
                    if(isset($_POST['skills']) and in_array($skills, $_POST['skills'])) {

                    echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' checked>$skills<br>";
                    }
                    if(isset($_POST['skills']) and !in_array($skills, $_POST['skills'])) {

                    echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' unchecked>$skills<br>";
                    } else {
                        if(!in_array($skills, $_POST['skills'])){
                            echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' unchecked>$skills<br>";
                        }
                    }
                }
                echo "</form>";
            }
        }
        ?>
        </td>
    </tr>
</table>

Please try and help me solve this puzzle.

Willing and able! You'll notice that it's not appearing out of order so much as it is appearing twice... Does `emprecords` store the *skills* belonging to an id? Answer that if you want an answer. Also, your form is disturbingly messed up. Why do you print so many forms but never close them? Do you want just 1 form? You probably want just 1 form... — , Dec 01 '15 at 03:31
yes, emprecords stores the skills belonging to an id which it retrieves from the skillsset table and inserts it into the emprecords table... — Salman Hossain, Dec 01 '15 at 11:51
yes I want just 1 form...still gotta figure out how to print it just once...thnx — Salman Hossain, Dec 01 '15 at 11:55
Does emprecords keep all of a person's skills in a single row or does it have a single row per skill? (So for the example in the picture, is returning 3 rows or 1 row?) And move the form echo outside of the while loop, outside of the if statements and just stick around the table. — , Dec 01 '15 at 12:59
In any case, when you loop through the results of emprecords, store all the skills that $id has in a 1 dimensional array and then as you loop through the results of `skillset` table, you should be checking if the `$skills` is `in_array($skill, $arrayContainingEmprecordsResults)`. And do that every time, regardless if post submitd. Longer answer to come at the end of the day (it's early morning here) — , Dec 01 '15 at 13:05
it is returning 1 row ...not 3 rows the "Engineering Services" is of one table and the "Information Technology" is of another table ...they are all returning 1 row each...thnx — Salman Hossain, Dec 01 '15 at 13:08
okay no worries...do u need the mysql database records as well? — Salman Hossain, Dec 01 '15 at 13:09
here's my sql database tables as well in case u want it - http://pastebin.com/uyL7DCDh — Salman Hossain, Dec 01 '15 at 13:16

score 0 · Answer 1 · edited May 23 '17 at 11:46

Obligatory link(s): Your code is open to SQL injection

Let's start small.

You may want to change Skills Set to Skill Set (see? starting small:)

You're missing a closing </tr> after <td>Information Technologies</td>

You're doing this:

if(!isset($_POST['submitd'])) {
    while($row = $listAHI ->fetch(PDO::FETCH_ASSOC))
    {
        $skills = $row["skills"];
        echo "
        <form action='' method='post'>
        <input type='checkbox' id='skills' name='skills[]' value='$skills'>  $skills<br> ";
    }
}
else ...

Where you echo out a new <form> for each input but you only close the form in the else. Just forget that and stick the <form> around the entire table.

<form method='POST' action=''>
<table border='1' cellspacing='0'>
    <colgroup>
        <col span='1'>
    </colgroup>
    <tr>
        <td>Engineering Services</td>
        <td>Information Technologies</td>
    </tr>
    <tr>
        <td valign="top">
        ...
</table>
</form>

Also, it was unclear what the code that follows did and it took me a few reads to get it. I'm guilty of doing that myself but I'd like to advise you to try and comment the intent of the code when it is complicated.

if(isset($_POST['skills']) and in_array($skills, $_POST['skills'])) {
    echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' checked>$skills<br>";
}
if(isset($_POST['skills']) and !in_array($skills, $_POST['skills'])) {
    echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' unchecked>$skills<br>";
} else if(!in_array($skills, $_POST['skills'])) {
        echo "<input type='checkbox' id='skills' name='skills[]' value='$skills' unchecked>$skills<br>";
    } 
}

Pay attention, the actual problem asked about is described ahead

The following code is the problem

$id = $_GET["id"];

$query2 = "SELECT * FROM emprecords WHERE id ='$id'";
$record_set2 = $dbs->prepare($query2);
$record_set2 -> execute();
$row2 = $record_set2->fetch(PDO::FETCH_ASSOC);
$sk = $row2['skills'];
$skills1 = explode(",", $sk);
for ($i=0; $i< count($skills1); $i++) {
    echo "<input type='checkbox' id='skills' name='skills[]' value='$skills1'checked>$skills1[$i]<br>"; 
}

Since you echo out <input>s with the values of the selected skills, you display the checkboxes twice.

If you want to check the boxes of the skills that the employee(guessing that it's employee from the name emprecords) has previously selected than you should stick the skills in an array and check that array when echoing out checkboxes.

The code below does a lot more than just that in an attempt to simplify your code. You should be able to use it replace all the code you posted. Also know that there are several ways to do what you're trying to do. I leave it to you find the optimal solution.

<br><br><H2 align="center">SKILL SET</H2>
<br>
<label>Pick Your Skill(s):</label>
<br><br>

<table border='1' cellspacing='0'>
    <colgroup>
        <col span='1'>
    </colgroup>
    <tr>
        <td>Engineering Services</td>
        <td>Information Technologies</td>
    </tr>
    <tr>
<?php
    $empSkills = array();
    if(isset($_GET['id'])) {

        $id = $_GET["id"];

        // use this try catch to catch potential errors
        try {
            // note how $query2 has :id at the end. Using ->prepare() and ->execute(with array parameter) is one good way to protect yourself from SQL injection attacks
            // also, only pull the columns that you're going to actually use
            $query2 = "SELECT skills FROM emprecords WHERE id =:id";
            $record_set2 = $dbs->prepare($query2);
            $record_set2 -> execute(array(':id'=>$id));
            $row2 = $record_set2->fetch(PDO::FETCH_ASSOC);

            $sk = $row2['skills'];
            $empSkills = explode(",", $sk);

            // always perform clean-up
            $record_set2->closeCursor();
        } catch (PDOException $e) { // always perform error checking on PDO
            // print whatever error messages you feel appropriate
            print "Error!: " . $e->getMessage() . "<br/>";
            die(); // stop executing the script on error (up to you)
        }
    }
    // CHAR_LENGTH() is a MySQL function that returns the number of characters in the string passed to it
    try {
        $list = "
        SELECT skills
        FROM skillsset
        WHERE CHAR_LENGTH(skills) > 0 AND category='Engineering'
        ORDER BY skills ASC";
        $listAHI = $dbs ->prepare($list);
        $listAHI -> execute();
        // this is a function. it is defined below
        printSkillsTd($listAHI, $empSkills);
        $listAHI->closeCursor();
    } catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }

    try {
        $list = "
        SELECT skills
        FROM skillsset
        WHERE CHAR_LENGTH(skills) > 0 AND category='Information'
        ORDER BY skills ASC";
        $listAHI = $dbs ->prepare($list);
        $listAHI -> execute();

        printSkillsTd($listAHI, $empSkills);
        $listAHI->closeCursor();
    } catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
    }

/**
 * This function prints out the all skills in the PDOStatement $listAHI as checkboxes. It "checks" the checkbox if the skill is in $empSkills
 */
function printSkillsTd($listAHI,$empSkills) {
    echo '
            <td valign="top">';

    while($row = $listAHI ->fetch(PDO::FETCH_ASSOC)) {
        $skill = $row['skills'];

        // note how i left out the 'id' attribute. The id attribute of an element must be unique on the entire page. You could make the `id` something like `skill_$skill` but i don't see why you would need an `id` at all from the posted code
        echo "
                <label><input type='checkbox' name='skills[]' value='$skill'";
        if(in_array($skill,$empSkills))
            echo " checked";
        echo ">$skill</label><br>";
    }

    echo '
            </td>';
}
        ?>
    </tr>
</table>

Thnx a mill bud! i got it to work! I usually don't use try/catch clauses but i guess its good usage when preventing protecting from SQL injection - PDO errors must be caught. — Salman Hossain, Dec 02 '15 at 12:24
Nice! If this didn't quite answer your question, post your code for others! — , Dec 03 '15 at 01:04
it did work just fine...the code u gave me is what i'm using...although I can simplify it quite a bit...but its good enough for now i guess — Salman Hossain, Dec 03 '15 at 14:19

Issues with Arranging Ticked Boxes

1 Answers1