Help with making checkbox survey save to MySQL

[ericcarver]

Hello.

I am tasked with making a small survey for internal use. I have the HTML made up where it asks for a incident number (6 digit number) and has 15 checkbox items.

Users here will goto the survey as needed, type in an incident number and check the box or boxes relevant to the incident. Ideally, this would save to a MySQL database.

I do a lot of IT things well, but this next step has me stumped. Can someone lead me in the right direction? I have full access to change the survey (form.php) and the database administration in PHPMyAdmin.

I will later come back to the project and setup some form of simple administration so the breakdowns of which of the 15 issues were associated to the incidents entered, but figure I would be happy if I can get the data entered first. :)

Thanks all. :/

[broj1]

You have to use a HTML form here. The action attribute of the form should point to the script that will process the data (validate it, sanitize it and store it into the database). The method attribute (get or post) will define how the form will be posted (post method is recommended).

Within the form you will have an input element that will enable users enter the incident number. Give it some meaningful name (such as incident_number).

Also within the form you will have all of your checkboxes. Again use meaningful names so you can refer to the values once processing the data. Each checkboxes label will describe the incident type.

At the end you add a submit button which will trigger the posting action and bring data over to the processing script.

Use CSS3 and HTML5 if you can to lay the elements out and shape everything.

Once you are done with the form post the code here for us to comment and help you go to the next step which is processing the data.

[ericcarver]

Hello,

I should have posted this earlier. here is the form.

<form id="form_919443" class="appnitro" method="post" action="">
<div class="form_description">
<center>
<h2>USI Customer Survey</h2>
<font style="font-family:arial; font-size:12px; font-weight:bold;">You are logged in as:</font> <font style="font-family:arial; font-size:12px; font-weight:bold; color:#000088;"><?php echo $UserName; ?>derp</font><br />
</center>
<!-- <p>This is your form description. Click here to edit.</p> -->
</div>
<ul >

        <li id="li_1" >
            <label class="description" for="element_1">Sales Order Number </label>
            <div>
                <input id="element_1" name="element_1" class="element text medium" type="text" maxlength="255" value=""/> 
            </div> 
        </li>        

        <li id="li_3" >
            <label class="description" for="element_3">Category 1 </label>
            <span>
                <input type="checkbox" name="failing" value="01">Issue 1<br>
                <input type="checkbox" name="failing" value="02">Issue2 <br>
                <input type="checkbox" name="failing" value="03" >Issue 3<br>
                <input type="checkbox" name="failing" value="04" >Issue 4<br>
                <input type="checkbox" name="failing" value="05" >Iccue 5<br>
                <input type="checkbox" name="failing" value="06" >Issue 6<br>
                <input type="checkbox" name="failing" value="07" >Issue 7<br>
            </span> 
        </li>
        <li id="li_4" >
            <label class="description" for="element_4">Category2 </label>
            <span>
                <input type="checkbox" name="failing" value="08">Issue 8<br>
                <input type="checkbox" name="failing" value="09" >Issue 9<br>
                <input type="checkbox" name="failing" value="10">Issue 10<br>
                <input type="checkbox" name="failing" value="11" >Issue 11<br>
                <input type="checkbox" name="failing" value="12">Issue 12<br>
                <input type="checkbox" name="failing" value="13">Issue 13<br>
            </span> 
        </li>
        <li id="li_5" >
            <label class="description" for="element_5">Category 3 </label>
            <span>
                <input type="checkbox" name="failing" value="14">Issue 14<br>
                <input type="checkbox" name="failing" value="15" >Issue 15<br>
            </span> 
        </li>

        <!--<form action="demo_form.asp" method="get">  -->

        <!--  <input type="submit" value="Submit">
        </form>  -->

        <li class="buttons">
        <input type="hidden" name="survey_answers" value="919443" />

        <input id="saveForm" class="button_text" type="submit" name="submit" value="Submit" />
        </li>
    </ul>
</form>  

[broj1]

I presume the above code is not the form you want but some example you found somewhere. Following your description this is how I would code it:

<?php 
// define checkbox data here
// (you might read it from the database)
$inicidentData = array(
    1 => 'Incident data 1',
    2 => 'Incident data 2',
    3 => 'Incident data 3',
    4 => 'Incident data 4',
    5 => 'Incident data 5',
    6 => 'Incident data 6',
    7 => 'Incident data 7',
    8 => 'Incident data 8',
    9 => 'Incident data 9',
    10 => 'Incident data 10',
    11 => 'Incident data 11',
    12 => 'Incident data 12',
    13 => 'Incident data 13',
    14 => 'Incident data 14',
    15 => 'Incident data 15'  
);

// check if user clicked the submit button
// you do all the processing on this same page (you could also do it on another page)
if(isset($_POST['submit'])) {
    // you would save this to a database but we will do that 
    // in next step once you are happy with the form
    // for now lets just display what was selected
    echo '<pre>';
    print_r($_POST);
    echo '</pre>';
    exit();
}
?>

<form id="form_919443" class="appnitro" method="post" action="#">
<div class="form_description">
<h2>Incident report</h2>

<div>You are logged in as: <?php echo $UserName; ?>derp</div>

<label class="description" for="incident-number">Incident number</label>
<input id="incident-number" name="incident-number" class="element text medium" type="text" maxlength="255" value=""> 

 <ul>
 <?php 
 // generate checkboxes using php
 foreach($inicidentData as $inKey => $inData) {
     echo '<li><input type="checkbox" name="incident-data[]" value="' . $inKey . '">' . $inData . '</li>';
 }
 ?>
 </ul>

 <input id="saveForm" class="button_text" type="submit" name="submit" value="Submit" />

</form>

First you have a php block defining the data for checkboxes. It would be wise to have this in a table in a database so it is scalable and you can build relations. Next you have a php code that is checking if the form was submited. If yes it just displays the user input (the incident number and the numbers of checkboxes checked) and exits. This is the data you will use to store into the database (after you have validated and sanitized it).

I have left out all the in-line styles. The styling should be done in external style sheet using element, class and id references (i.e. list item bullets removed). Keep the html code simple and structured.

I hope I am still in the same direction you want to go :-)

[ericcarver]

That looks amazing, I can't believe how efficient it is. You make it look so simple... I am trying to study how it would pass that arrayed data to SQL now.
Just WoW!

[ericcarver]

Does this look good for the SQL layout?

survey_data
Column Type Null Default Comments MIME 
survey_id  int(11) No        
order_id  int(11) No        
timestamp  timestamp No  CURRENT_TIMESTAMP      
incident_01  varchar(1) No  0      
incident_02  int(1) No  0      
incident_03  varchar(1) No  0      
incident_04  varchar(1) No  0      
incident_05  varchar(1) No  0      
incident_06  varchar(1) No  0      
incident_07  varchar(1) No  0      
incident_08  varchar(1) No  0      
incident_09  varchar(1) No  0      
incident_10  varchar(1) No  0      
incident_11  varchar(1) No  0      
incident_12  varchar(1) No  0      
incident_13  varchar(1) No  0      
incident_14  varchar(1) No  0      
incident_15  varchar(1) No  0      

Indexes: Documentation
Keyname Type Unique Packed Column Cardinality Collation Null Comment 
PRIMARY BTREE Yes No survey_id 0 A   

[broj1]

Edit: Ups, I did not see your post above when writing this.

Cool. My idea is that in the database you would have two tables: one for incident related categories (it will populate the checkboxes - the 15 values in the code above) and one main table for incidents (it will contain an incident numbers and al the checked categories for each incident).

Example of first table (let's call it incident_categories):

cat_id | category
------------------------
1      | Incident data 1
2      | Incident data 2
3      | Incident data 3
4      | Incident data 4
5      | Incident data 5
6      | Incident data 6
...
15     | Incident data 15

Example for second table (let's call it incidents):

incident_id | incident_categories
---------------------------------
1           | [2,8]
2           | [6]
3           | [5,7,14,15]
4           | 
5           | [2,9,11]
...

To save the data after the form was submited the code could look something like:

// do this only if the form was submited
if(isset($_POST['submit'])) {
    // initialize an array for errors
    $errors = array();

    // check if incident number has been input and is valid
    // (i.e. contains only numbers)
    if(isset($_POST['incident-number']) && is_numeric($_POST['incident-number'])) {
        // sanitize it and store it into a variable
        $incident_number = intval($_POST['incident-number']);
    } else {
        // if somethimg is not OK with the entered incident number
        // or if it has not been entered
        // set an error message
        $errors[] = "Error: invalid incident number.";
    }

    // check if any checkboxes have been checked
    // samitize them and store into an array
    if(isset($_POST['incident-data']) && !empty($_POST['incident-data'])) {
        $incident_data = array();
        foreach($_POST['incident-data'] as $data) {
            $incident_data[] = $data;
        }
    } else {
        // if no checkboxes were checked set an error message
        $errors[] = "Error: no checkboxes selected";
    }

    // if there were no errors save the data into database
    // otherwise display errors
    if(empty($errors)) {
        // do the insert stuff
        // use PDO - you can find many examples here on DW or elsewhere on the web
    } else {
        // display error messages and stop the script
        foreach($errors as $error) {
            echo $error . '<br>';
        }
        die();
    }
}

Above code is a concept of how to process the data, do some basic sanitization and handle possible errors. You still have to do the inserting into the database code. I strongly recommend using PDO to handle database related stuff. If you have troubles post questions here.

[broj1]

Seems like you posted while I was preparing my answer to the previous question. See my post if it is usefull and understandable.

[diafol]

There may be an easier solution yet. Are you likely to have less than 32 incident types? If there is a possibility that you will exceed 32, then the following solution will not work as it depends on integers being equal to or less than 32-bits (for 32-bit systems).

issuetypes
it_id | it_type | it_decbit | cat_id

1 Issue1 1 1
2 Issue2 2 1
3 Issue3 4 1
4 Issue4 8 1
5 Issue5 16 1
6 Issue6 32 1
7 Issue7 64 1
8 Issue8 128 2
9 Issue9 256 2
10 Issue10 512 2
11 Issue11 1024 2
12 Issue12 2048 2
13 Issue13 4096 2
14 Issue14 8192 3
15 Issue15 16384 3

categories
cat_id | catname
1 Category 1
2 Category 2
3 Category 3

incidents
inc_id | order_id | issues
1 23111 512 (512 only)
2 32371 39 (32 + 4 + 2 + 1)
3 37484 21 (16 + 4 + 1)
4 45738 68 (64 + 4)
5 67859 255 (128 + 64 + 32 + 16 + 8 + 4 + 2 + 1)
etc

You can now search easily for issue types with bitwise operators (e.g. &). You can also edit records easily within forms too using the bitwise operator. Although you can extract info from a comma separated list in a field.

To create your form...

SELECT i.it_type, i.it_decbit, c.cat_id, c.catname FROM issuetypes AS i INNER JOIN categories AS c ON i.cat_id = c.cat_id ORDER BY c.cat_id, i.it_id

Assume all records fetched to $data array.

$output = '';
$cat_id = 0;
$counter = 3;
foreach($data as $d)
{
    if($d['cat_id'] != $cat_id)
    {
    if($cat_id != 0) $output .= "</span></li>";
        $output .= "<li id='li_$counter'>
            <label class="description" for="element_$counter">{$d['catname']} </label><span>";
        $counter++;   
    }
    $output .= "<input type="checkbox" name="failing" value="{$d['it_decbit']}">{$d['it_type']}<br>";
    $cat_id = $d['cat_id'];
}
$output .= "</span></li>";

Just echo that out to your form.

Collecting the combined issues easy...

if(isset($_POST['failing']))
{
    $p = (array) $_POST['failing'];
    $sum = array_sum($p);
}

That's it. you now have the total of all failings in one integer to post into db.