How to store several HTML form fields in one mysql table field?

Question

Hi,

I want to store several fields from a html form in one table field. Here is the source code for the form. This is only part of the form, the form tags are there. Attached is a screenshort from this part of the form.

<table border=0 cellpadding=0 cellspacing=0>
    <tr>
        <td><input class="field checkbox" type="checkbox" value="Automotive" /><label class="choice">Automotive</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="rem_app_1" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" value="Backlights"/><label class="choice">Backlights</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="rem_app_2" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" value="Signage/Traffic lights"/><label class="choice">Signage/Traffic lights</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="rem_app_3" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" value="IR" /><label class="choice">IR</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="rem_app_4" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" value="LED lights"/><label class="choice">LED lights</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="rem_app_5" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" value="Mobile devices"/><label class="choice">Mobile devices</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="rem_app_6" type="text" size="50" value=""/></td>
    </tr>
</table>

The way it is stored in mysql should be like this, depending on what checkbox the user clicks,e.g.:

Automotive, Remark: blablabla
Backlights, Remark: blubblubblub
Mobile applications, Remark: skdfjasldfkj

thanks for your help!

Answer 1

Start with adding a name field to those checkboxes. What language are you using for form processing? PHP?

Answer 2

What backend language are you using on your server? Mysql is not a server language itself, but a means to communicate with a database. You need a server language to handle server requests. Check out PHP, ASP.NET, and Django. Arguably, PHP is the easiest to pick up, install, and set up.

Once you have a backend language, you can use that language's Mysql bindings to communicate with your database.

Answer 3

Alright, so first I'd modify your form so that the checkboxes and remarks use the same naming standard, and give the checkboxes a value if they're checked:

<table border=0 cellpadding=0 cellspacing=0>
    <tr>
        <td><input class="field checkbox" type="checkbox" name="automotive" value="1" /><label class="choice">Automotive</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="automotive_rem" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" name="backlights" value="1" /><label class="choice">Backlights</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="backlights_rem" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" name="signage" value="1" /><label class="choice">Signage/Traffic lights</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="signage_rem" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" name="ir" value="1" /><label class="choice">IR</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="ir_rem" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" name="led" value="1" /><label class="choice">LED lights</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="led_rem" type="text" size="50" value=""/></td>
    </tr>
    <tr>
        <td><input class="field checkbox" type="checkbox" name="mobile" value="1"/><label class="choice">Mobile devices</label></td>
        <td>&nbsp;&nbsp;&nbsp;&nbsp;Remark:<input name="mobile_rem" type="text" size="50" value=""/></td>
    </tr>
</table>

You don't have to do the above, it just helps in the PHP processing for collecting the data. If you stick with the app_1 format, then I'd suggest app_1_chk for the checkbox, and app_1_rem for the remark.

Next I'd create a class in PHP for holding these pairs of data (check and remark):

class node {
    public $name,
        $checked,
        $remark;
    function __construct($name) {   
        $this->name=$name;
    }
}

Then I'd iterate through inputs collecting the check status and the remark for each node:

$input_list=array("automotive","backlights","signage","ir","led","mobile");
$list=null;
foreach ($input_list as $item) {
    $n=new node($item);
    $n->checked=($_REQUEST[$item]=="1");
    $n->remark=$_REQUEST[$item."_rem"];
    $list[]=$n;
}

And finally I'd serialize that array of objects into... something serializable. PHP has native commands (serialize,unserialize), JSON commands (json_encode,json_decode), and a few other options. I'd lean towards JSON, but you can use anything you choose.

$sql="INSERT INTO mytable (mycolumn) VALUES ('".mysqli_real_escape_string(json_encode($list))."')";
//... your SQL processing.

To turn that data back into a usable list:

$sql="SELECT mycolumn FROM mytalbe WHERE /*... your where statement*/";
// ... execute SQL
$list=json_decode($row[0]);
foreach ($list as $node) {
    //Your node processing eg:
    echo $node->name."=".($node->checked?"yes":"no").", ".$node->remark."</br />";
}