php mysql multiple checkbox insert

jonofat
jonofat used Ask the Experts™
on
Hi, I have 2 fields where the user can choose from a-d via checkboxes. Some jobs require all, some don't, etc. What would be the best way to capture this data in a database bearing in mind that it needs to be evaluated at a later stage by job per field. So, if I choose A and B which would be a better way of capturing the data? Either one row with a field showing something like A,B or have two rows added because 2 choices were made? I find it pretty confusing. Anyway, here is the form code.

<form id="form1" name="form1" method="post" action="">
  <p>Job no: 
    <label for="textfield"></label>
  <input type="text" name="textfield" id="textfield" />
  </p>
  <p>Weld : A
    <input type="checkbox" name="checkbox" id="checkbox" />
    <label for="checkbox"></label>
  B
  <input type="checkbox" name="checkbox2" id="checkbox2" />
  <label for="checkbox2"></label> 
  C
  <input type="checkbox" name="checkbox3" id="checkbox3" />
  <label for="checkbox3"></label>
  D
  <input type="checkbox" name="checkbox4" id="checkbox4" />
  <label for="checkbox4"></label>
  <br />
  </p>
  <p>Seal: A
    <input type="checkbox" name="checkbox5" id="checkbox5" />
    <label for="checkbox5"></label>
    B 
    <input type="checkbox" name="checkbox6" id="checkbox6" />
    <label for="checkbox6"></label>
  C
  <input type="checkbox" name="checkbox7" id="checkbox7" />
  <label for="checkbox7"></label>
  D 
  <input type="checkbox" name="checkbox8" id="checkbox8" />
  <label for="checkbox8"></label>
  </p>
  <p>
    <input type="submit" name="button" id="button" value="Submit" />
  </p>
</form>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014
Commented:
All your checkboxes need values or they wont' be submitted.  In addition, I would make it somewhat 'semantic' in that the names and values have some meaning rather than just using 'checkbox1' thru 'checkbox7'.

<form id="form1" name="form1" method="post" action="">
  <p>Job no: 
    <label for="textfield"></label>
  <input type="text" name="textfield" id="textfield" />
  </p>
  <p>Weld : 
   <label for="checkboxWA">A</label>
   <input type="checkbox" name="checkboxWA" id="checkboxWA" value="WA" />
  <label for="checkboxWB">B</label> 
  <input type="checkbox" name="checkboxWB" id="checkboxWB" value="WB" />
  <label for="checkboxWC">C</label>
  <input type="checkbox" name="checkboxWC" id="checkboxWC" value="WC" />
  <label for="checkboxWD">D</label>
  <input type="checkbox" name="checkboxWD" id="checkboxWD" value="WD" />
  <br />
  </p>
  <p>Seal: 
    <label for="checkboxSA">A</label>
    <input type="checkbox" name="checkboxSA" id="checkboxSA" value="SA" />
    <label for="checkboxSB">B</label>
    <input type="checkbox" name="checkboxSB" id="checkboxSB" value="SB" />
  <label for="checkboxSC">C</label>
  <input type="checkbox" name="checkboxSC" id="checkboxSC" value="SC" />
  <label for="checkboxSD">D</label>
  <input type="checkbox" name="checkboxSD" id="checkboxSD" value="SD" />
  </p>
  <p>
    <input type="submit" name="button" id="button" value="Submit" />
  </p>
</form>

Open in new window


Then each item must be received by PHP.  Since checkboxes are not submitted if they are not checked, you need to set a default value for each of the elements.

<?php 
$textfield = "";
$WA = "";
$WB = "";
$WC = "";
$WD = "";
$SA = "";
$SB = "";
$SC = "";
$SD = "";
// then assign the POST values that were actually submitted
if(isset($_POST['textfield'])) $textfield = $_POST['textfield']);
if(isset($_POST['checkboxWA'])) $WA = $_POST['checkboxWA']);
if(isset($_POST['checkboxWB'])) $WB = $_POST['checkboxWB']);
if(isset($_POST['checkboxWC'])) $WC = $_POST['checkboxWC']);
if(isset($_POST['checkboxWD'])) $WD = $_POST['checkboxWD']);
if(isset($_POST['checkboxSA'])) $SA = $_POST['checkboxSA']);
if(isset($_POST['checkboxSB'])) $SB = $_POST['checkboxSB']);
if(isset($_POST['checkboxSC'])) $SC = $_POST['checkboxSC']);
if(isset($_POST['checkboxSD'])) $SD = $_POST['checkboxSD']);
// And I would put them all in the same row in the database because they are all related to the same job.
$SQL = "INSERT INTO joblist(textfield,checkboxWA,checkboxWB,checkboxWC,checkboxWD,checkboxSA,checkboxSB,checkboxSC,checkboxSD) VALUES ('$textfield', '$WA', '$WB', '$WC', '$WD', '$SA', '$SB', '$SC', '$SD')";
// followed by your query
 ?>

Open in new window

Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
If it were me and the number of jobs was relatively small - I would use a bit field in the database i.e.

job1 = 0x1; //00000001
job2 = 0x2; //00000010
job3 = 0x4; //00000100
..
Etc

You can still call the values 1-8 and use the bit shift operator (<<) to get the flag i.e job3 has a value 3 so if $x=3 then

$flag = 1 << $x

You can then do a query where you want multiple jobs

// these you set based on your criteria

$job1 = 1;
$job5 = 1 << 5;

select * from table where mask & $job1 and mask & job2;

Setting and clearing valus can be used with | (bitwise OR) or ^ (bitwise XOR)
Most Valuable Expert 2011
Top Expert 2016
Commented:
This article shows some of the ways to handle checkboxes.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_5450-Common-Sense-Examples-Using-Checkboxes-with-HTML-JavaScript-and-PHP.html

With regard to the table, I might want to have one column for each checkbox.  My sense from the question is that each row represents a job and that the checkboxes represent sub-elements of the job.  Storage is so cheap that I would not recommend any technique like bit-mapping the values - that may obscure the data and the financial savings are hard to find in a world where hundreds of megabytes of storage can be bought for the price of a few sheets of toilet paper.  My sense about this sort of thing is to make it easy to read and then it will be easy to implement and debug.

If there is any likelihood that the sub-elements will change, you might want to consider normalizing the tables to create a separate table for the checkboxes and a junction table that marries the jobs with the checkboxes.  Should you do that, the part of the form that contains the checkboxes would be built dynamically, using a query to get the names of the columns from the table.  Adding a sub-element would be as simple as adding a column in the table that lists the sub-elements.

HTH, ~Ray
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
@Ray - the problem with a single column per checkbox is that if you need more columns it is a database structural change that is required - whereas bitmapped fields do not require a change to the database structure.

Searching is also a lot easier

select * from table where mask & searchmask = searchmask

will allow you to quickly find records that have a given number of checkboxes checked as opposed to

select * from table where col1=1 and col5=1 and col6=1 etc.

Creating child tables to manage checkboxes in my view is overkill and will over complicate the means of accessing and storing the data. I agree space is not a consideration my preference is from ease of coding (accessing and storing data) and adding a join into the mix to me results in more complicated code.

However, both methods will work and it is a matter of preference - I prefer the bit mapped approach in this case.

Author

Commented:
Thanks for the comments guys. Ray, I am using MYSQL and when I create a new database I usually just choose the default as I haven't had to store so much data before. which should I choose for large storage, ie. Myisam etc?
Most Valuable Expert 2011
Top Expert 2016

Commented:
What do you define as "large" storage?  How many tables, rows, bytes, etc?  What kind of query profile (mostly INSERT vs mostly SELECT)?  I just kind of use INNODB reflexively, but there may be a better choice if we can know the details.

Author

Commented:
Hmm. Not sure how many bytes. Maybe 50 rows a day?

I have another question related to this. Can I ask in this thread or must I ask a new question? Basically, I changed the code as I want to pull from a database. However, only the first line repeats horizontally, the second doesn't show anything.

<form id="form1" name="form1" method="post" action="">
  <table width="45%" border="0" cellspacing="0" cellpadding="0">
    <tr>
      <td>Job number:</td>
      <td><label for="textfield"></label>
      <input type="text" name="textfield" id="textfield" /></td>
    </tr>
    <tr>
      <td>Runs</td>
      <td><label for="runs[]">
        <table >
          <tr>
            <?php
$Recordset1_endRow = 0;
$Recordset1_columns = 3; // number of columns
$Recordset1_hloopRow1 = 0; // first row flag
do {
    if($Recordset1_endRow == 0  && $Recordset1_hloopRow1++ != 0) echo "<tr>";
   ?>
            <td><?php echo $row_Recordset1['defectcode']; ?>
              <input name="runs[]" type="checkbox" id="runs[]" /></td>
            <?php  $Recordset1_endRow++;
if($Recordset1_endRow >= $Recordset1_columns) {
  ?>
          </tr>
          <?php
 $Recordset1_endRow = 0;
  }
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
if($Recordset1_endRow != 0) {
while ($Recordset1_endRow < $Recordset1_columns) {
    echo("<td>&nbsp;</td>");
    $Recordset1_endRow++;
}
echo("</tr>");
}?>
        </table>
      </label></td>
    </tr>
    <tr>
      <td>Misses</td>
      <td><table >
        <tr>
          <?php
$Recordset1_endRow = 0;
$Recordset1_columns = 3; // number of columns
$Recordset1_hloopRow1 = 0; // first row flag
do {
    if($Recordset1_endRow == 0  && $Recordset1_hloopRow1++ != 0) echo "<tr>";
   ?>
          <td><?php echo $row_Recordset1['defectcode']; ?>
            <input name="misses[]" type="checkbox" id="misses[]" /></td>
          <?php  $Recordset1_endRow++;
if($Recordset1_endRow >= $Recordset1_columns) {
  ?>
        </tr>
        <?php
 $Recordset1_endRow = 0;
  }
} while ($row_Recordset1 = mysql_fetch_assoc($Recordset1));
if($Recordset1_endRow != 0) {
while ($Recordset1_endRow < $Recordset1_columns) {
    echo("<td>&nbsp;</td>");
    $Recordset1_endRow++;
}
echo("</tr>");
}?>
      </table></td>
    </tr>
    </table>
</form>

Open in new window

Author

Commented:
This is what I wanted to achieve initially but not sure if it is the most practical or easiest to draw info from. table after entering data
Most Valuable Expert 2011
Top Expert 2016

Commented:
In data base terms, 50 of anything is no big deal.  It would not matter which engine you chose.

I cannot understand the code posted in the most recent snippet.  I could not find the query, so I do not know what might be in the results set.  All the back-and-forth between PHP and HTML is confusing, there are no comments, and the control structures are not lined up.  It almost looks like the programmer was trying to learn PHP by copying DreamWeaver code.  DreamWeaver produces some of the most horribly written PHP that I have ever seen, and it's not really usable as a learning tool or a starting platform for developing a data base driven web site.  

A better learning tool would be this book.  Very readable, with structured explanations and great examples -- very much worth investing a couple of weeks immersed in the learning process.
http://www.sitepoint.com/books/phpmysql5/

Best of luck with your project, ~Ray
Most Valuable Expert 2011
Top Expert 2016

Commented:
Regarding the table after entering data What are the rules that would put A,B,C in the RUNS column and C,F,G in the MISSES column?  Is C supposed to be in both columns?  Would D and E be in play?  If not what rules would we have to tell us that?

Author

Commented:
Sorry Ray, I am poor at explaining. Here is a simple view of the tables. Maybe this will shed some light as to what I am actually trying to do!

Table : defect_areas

AreaID       Area                  Description
   1                A              left hand under-side
   2                B               left hand top side
   3                C               right hand under-side

Table : defects

DefectID     DefectName
    1                    Runs
    2                    Misses
    3                    Sag

Table : Audit

AuditID       Date           Job no.     DefectID      DefectArea
    1          2012-01-01      8351           1                    A,B,C
    2          2012-01-01      8351           2                    A,C

OR should it be?

AuditID       Date           Job no.     DefectID      DefectArea
    1          2012-01-01      8351           1                    A
    2          2012-01-01      8351           1                    B
    3          2012-01-01      8351           1                    C

Author

Commented:
And then I would like the form to look like this pic. But how to insert that data is the seriously confusing part.Form
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This is where I think bit fields would help.

If you defined your form variables as

<input type="text" name="data[date]" />
<input type="text" name="data[jobno]" />
<input type="text" name ="runs[]" value="1" />
<input type="text" name ="runs[]" value="2" />
<input type="text" name ="runs[]" value="4" />
<input type="text" name ="misses[]" value="1" />
<input type="text" name ="runs[]" value="2" />
<input type="text" name ="runs[]" value="4" />

In your code you could then do as follows

$data = $_REQUEST['data']; // For simplicity; normally don't access request arrays directly
$runs = $_REQUEST['runs'];
$misses = $_REQUEST['misses'];

$data['runs'] = 0;
foreach($runs as $r) $data['runs'] |= $r;

$data['misses'] = 0;
foreach($misses as $m) $data['misses'] |= $m;

$data now holds all the fields you want to insert into your database

Define your table like so

CREATE TABLE __whatever (
  id int(11) NOT NULL auto_increment,
  date date default NULL,
  jobno int(11) default NULL,
  runs bit(8) default NULL,
  misses bit(8) default NULL,
  PRIMARY KEY  (id)
)

$fields = $values = '';
foreach($data as $k => $v) {
      $fields .= (empty($fields)?'':',') . $k;
      $values .= (empty($values)?'':',') . "'$v'";
}
$query = sprintf("INSERT INTO __whatever (%s) VALUES (%s)", $fields, $values);
mysql_query($query);
Most Valuable Expert 2011
Top Expert 2016

Commented:
Regarding the post at ID: 38014853, I think I like the second setup for the Audit table.

See if this makes any sense... In real life you would want some additional sanity checks on the input data. but hopefully the design will stimulate some creative thinking ;-)

<?php // RAY_temp_jonofat.php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
date_default_timezone_set('America/Chicago');

// IF THERE IS A POST REQUEST
if (!empty($_POST))
{
    // VISUALIZE THE POST REQUEST
    echo "<pre>";
    var_dump($_POST);

    // SET DEFAULT VALUES FOR POTENTIALLY EMPTY FIELDS
    $myRuns = (isset($_POST['myRuns']) && is_array($_POST['myRuns'])) ? $_POST['myRuns'] : array();
    $myMiss = (isset($_POST['myMiss']) && is_array($_POST['myMiss'])) ? $_POST['myMiss'] : array();

    // CONVERT THE DATE
    $myDate = date('c', strtotime($_POST['myDate']));

    // DO NOT USE THIS, USE THE FOLLOWING ESCAPE SEQUENCE INSTEAD
    $myJobn = $_POST['myJobn'];
    // $myJobn = mysql_real_escape_string($_POST['myJobn']);

    // CONSTRUCT THE INSERT QUERIES FOR RUNS
    foreach ($myRuns as $myRun)
    {
        $sql = "INSERT INTO Audit (myDate, myJobn, myRuns) VALUES ('$myDate', '$myJobn', '$myRun')";
        echo PHP_EOL . $sql;
    }

    // CONSTRUCT THE INSERT QUERIES FOR MISSES
    foreach ($myMiss as $myMis)
    {
        $sql = "INSERT INTO Audit (myDate, myJobn, myMiss) VALUES ('$myDate', '$myJobn', '$myMis')";
        echo PHP_EOL . $sql;
    }

    // RETURN TO NORMAL FORMATTING
    echo "</pre>";
}

// CREATE THE FORM
$form = <<<ENDFORM
<form method="post">
DATE: <input name="myDate" /><br/>
JOB#: <input name="myJobn" /><br/>
RUNS:
<input type="checkbox" name="myRuns[]" value="A" />A &nbsp;
<input type="checkbox" name="myRuns[]" value="B" />B &nbsp;
<input type="checkbox" name="myRuns[]" value="C" />C &nbsp;
<br/>
MISS:
<input type="checkbox" name="myMiss[]" value="A" />A &nbsp;
<input type="checkbox" name="myMiss[]" value="B" />B &nbsp;
<input type="checkbox" name="myMiss[]" value="C" />C &nbsp;
<br/>
<input type="submit" />
</form>
ENDFORM;

echo $form;

Open in new window

Author

Commented:
Thanks guys, I am going to give you both equal points for your earlier posts with suggestions on how to do it. I have gone with Ray's way simply because I am able to wrap my head around that where I am not at all familiar with bit fields. Thanks for the help, hopefully I have this figured out now, now that I have physically drawn out the database structure and I know exactly what I want to happen. Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial