How do I create a method to specify field name and a PK from a DB table?

Question

I have a series of checkboxes that interact with the DB in 3 possible ways; INSERT, DELETE, no action.

I will have a total of 8-10 checkboxes, however each checkboxes requires 32 lines of code (inc 3 if statements) to work. Rather than have 320 lines of code for 10 checkboxes is it possible to to use a php function to define the markup for these elements?

PHP Code:

//If the Java checkbox is checked it will check to see if java is already a skill assigned to the user. If so it will ignore, if not it will add.   
if (isset($_POST['java'])) {
    if (empty($_POST['javaexp'])) {
        //header('Location: UpdateAccount.php');
        //die();                    
    }
    else {
        $javaexp = $_POST['javaexp'];
    }

    $sql = $con->query("SELECT count(UserID) as total FROM userskills WHERE UserID = $User AND SkillID = 1")
        or die(mysqli_error($con));

    $row = mysqli_fetch_assoc($sql);

    if ($row ['total'] == "0") {

        $sql = $con->query("INSERT INTO userskills ( UserID, SkillID, Experience) VALUES  ($User, 1, $javaexp)");

        //If the Java checkbox is not checked it will check to see if java is already a skill assigned to the user. If they are it will delete it. If not it will ignore.   
    }
}
else {
    $sql = $con->query("SELECT count(UserID) as total FROM userskills WHERE UserID = $User AND SkillID = 1")
        or die(mysqli_error($con));

    $row = mysqli_fetch_assoc($sql);

    if ($row ['total'] == "1") {

        $sql = $con->query("DELETE FROM userskills 
                WHERE UserID = $User AND SkillID = 1");
    }
}

$con = sql connection on another php file.

HTML Code:

<p>
    <label>
        <input type="checkbox" name="java" value="checkbox" id="CheckboxGroup1_0" class="skillselect">
        Java</label> <input type="number" name="javaexp" class="expnumber" placeholder="Enter Experience in years.">
    <br>
    <br>
    <label>
        <input type="checkbox" name="iOS" value="checkbox" id="CheckboxGroup1_1" class="skillselect">
        Checkbox</label> <input type="number" name="" class="expnumber" placeholder="Enter Experience in years.">
    <br>
    <br>
    <label>
        <input type="checkbox" name="PHP" value="checkbox" id="CheckboxGroup1_2" class="skillselect">
        Checkbox</label> <input type="number" name="" class="expnumber" placeholder="Enter Experience in years.">
    <br>
    <br>
</p>
<div class="FormElement">
    <input name="Update" type="submit" class="button" id="Update" value="Submit Changes">
</div>

score 0 · Answer 1 · edited May 23 '17 at 10:28

You certainly can turn it into a function:

function updateSkillFromPost($skill, $skillId, $User, $con) {
    if (isset($_POST[$skill])) {
        if (empty($_POST["{$skill}exp"])) {
            //header('Location: UpdateAccount.php');
            //die();                    
        }
        else {
            $exp = $_POST["{$skill}exp"];
        }

        $sql = $con->query("SELECT count(UserID) as total FROM userskills WHERE UserID = $User AND SkillID = $skillId")
            or die(mysqli_error($con));

        $row = mysqli_fetch_assoc($sql);

        if ($row ['total'] == "0") {

            $sql = $con->query("INSERT INTO userskills ( UserID, SkillID, Experience) VALUES  ($User, $skillId, $exp)");

            //If the Java checkbox is not checked it will check to see if java is already a skill assigned to the user. If they are it will delete it. If not it will ignore.   
        }
    }
    else {
        $sql = $con->query("SELECT count(UserID) as total FROM userskills WHERE UserID = $User AND SkillID = $skillId")
            or die(mysqli_error($con));

        $row = mysqli_fetch_assoc($sql);

        if ($row ['total'] == "1") {

            $sql = $con->query("DELETE FROM userskills 
                    WHERE UserID = $User AND SkillID = $skillId");
        }
    }
}

and then call it like

updateSkillFromPost('java', 1, $User, $con);
updateSkillFromPost('php', 2, $User, $con);
updateSkillFromPost('xml', 3, $User, $con);
... and so on ...

or even better, given an array of $skills in the form $id => $name:

foreach($skills as $skillId => $skill) {
  updateSkillFromPost($skill, $skillId, $User, $con);
}

please notice that the code you used, and which I turned into a function, si vulnerable to SQL Injection though.

How do I create a method to specify field name and a PK from a DB table?

1 Answers1

Linked