PHP - To Insert data from checkbox forms into MYSQL

Question

This is my first website that makes use of server-side technology. And it's also my first delving into PHP working along side MYSQL. I've been working and learning as I go.

I've created a basic management page for a database of product entries for a small Prom Dress business.

I've been working on the functionality of a form that the administrator can use to upload new entries to the database.

Some of these entries include:

Dress Name [text input]

Colour Availability [check boxes]

I've run into trouble getting data from multiple check boxes from a HTML form into the database.

The choices in the check boxes should represent a boolean value (yes/no - 1/0)

Here's the form structure:

    <form action="inventory_list.php" enctype="multipart/form-data" name="myForm" id="myform" method="post">
    <table width="90%" border="0" cellspacing="0" cellpadding="6">
  <tr>
    <td width="20%" align="right">Dress Name</td>

    <td width="80%"><label>
    <input name="Dress_name" type="text" id="Dress_name" size="64" />
    </label>
    </td>
  </tr>

  <tr>
    <td align="right">Collections</td>
    <td>
    <label>
      <select name="collections" id="collections">
            <option value="other">Other</option>
            <option value="hermione">Hermione</option>
            <option value="jora">Jora</option>
            <option value="manon">Manon</option>
      </select>
    </label></td>
  </tr>
  <tr>
    <td align="right">Available Sizes</td>
    <td>
        <input type="checkbox" name="sizes[]" value="1" /> Sizes 2-6<br />
        <input type="checkbox" name="sizes[]" value="1" />  Sizes 6-10 <br />
        <input type="checkbox" name="sizes[]" value="1" /> Sizes 10-14<br />
        <input type="checkbox" name="sizes[]" value="1" />  Sizes 14-18 <br />
        <input type="checkbox" name="sizes[]" value="1" /> Sizes 18-22<br />
        <input type="checkbox" name="sizes[]" value="1" />  Sizes 22-26 <br />

   </td>
  </tr>
  <tr>
    <td align="right">Dress Colours</td>
    <td>
        <input type="checkbox" name="colours[]" value="1" /> Pinks/Reds <br />
        <input type="checkbox" name="colours[]" value="1" />  Blues/Greens <br />
        <input type="checkbox" name="colours[]" value="1" /> Violet/Purple<br />
        <input type="checkbox" name="colours[]" value="1" />  Yellow/Orange/Brown/Gold <br />
        <input type="checkbox" name="colours[]" value="1" /> Black/White<br />

   </td>
  </tr>
  <tr>
    <td align="right">Product Image</td>
    <td><label>
      <input type="file" name="fileField" id="fileField" />
    </label></td>
  </tr>      
  <tr>
    <td>&nbsp;</td>
    <td><label>
      <input type="submit" name="button" id="button" value="Add This Item Now" />
    </label></td>
  </tr>
</table>
</form>

And here is the PHP that I've built to deal with the form submitting data to the Database.

<?php 
if (isset($_POST['Dress_name'])) {

$dress_name = mysql_real_escape_string($_POST['Dress_name']);
$collections = mysql_real_escape_string($_POST['collections']);
$sizesArray = $_POST['sizes'];
$coloursArray = $_POST['colours'];

foreach ($sizesArray as $key => $value)
{
    echo "Key = $key || Value = $value<br />";
}

foreach ($coloursArray as $ckey => $cvalue)
{
    echo "Key = $ckey || Value = $cvalue<br />";
}

//See if that product name is an identical match to another product in the system
$sql = mysql_query("SELECT ID FROM names WHERE Dress='$dress_name' LIMIT 1");
$productMatch = mysql_num_rows($sql); // count the output amount
if ($productMatch > 0) {
    echo 'Sorry you tried to place a duplicate "Product Name" into the system, <a href="inventory_list.php">click here</a>';
    exit();
}


 //Add this product into the database now
$sql = mysql_query("INSERT INTO names (Dress) 
    VALUES('$dress_name')") or die (mysql_error());
 $pid = mysql_insert_id();



 //Place image in the folder 
$newname = "$pid.jpg";
move_uploaded_file( $_FILES['fileField']['tmp_name'], "../inventory_images/$newname");
header("location: inventory_list.php"); 
exit();
}
?>

As you can see, I've got as far as putting the data into an array. And now I've hit a bit of a brick wall, I can't seem to find an answer that makes sense anywhere.

How do I get the boolean values into correct columns in the database table?

Appreciate your time!

Answer 1

The first thing to know is that HTML checkboxes will return a result if checked, and will return nothing if unchecked. Therefore, it is important in your html to have your values increment:

<td align="right">Available Sizes</td>
<td>
  <input type="checkbox" name="sizes[]" value="1" /> Sizes 2-6<br />
  <input type="checkbox" name="sizes[]" value="2" />  Sizes 6-10 <br />
  <input type="checkbox" name="sizes[]" value="3" /> Sizes 10-14<br />
  <input type="checkbox" name="sizes[]" value="4" />  Sizes 14-18 <br />
  <input type="checkbox" name="sizes[]" value="5" /> Sizes 18-22<br />
  <input type="checkbox" name="sizes[]" value="6" />  Sizes 22-26 <br />
</td>

A fully normalized database schema would look something like this:

//Note that 'names' and 'collections are overloaded
  //words and should be avoided
table BRANDS 
  id INT AUTOINCREMENT
  collectionname VARCHAR

table SIZES
  id INT AUTOINCREMENT
  sizecategory VARCHAR //ie 'Sizes 18-22'

table COLOURS
  id INT AUTOINCREMENT
  colourname VARCHAR 

table INVENTORY
  id INT AUTOINCREMENT
  brandid INT FOREIGN KEY (BRAND)
  imageurl VARCHAR

table INVENTORY_SIZES
  inventoryid INT FOREIGN KEY
  sizeid FOREIGN KEY

table INVENTORY_COLOURS
  inventoryid INT FOREIGN KEY
  colourid FOREIGN KEY

Your BRAND, COLOURS and SIZES tables would need to be properly populated with your available options, and you could in theory dynamically load your page with data from those tables. The ids of each of the rows in those tables should end up as the values in your checkbox arrays.

//get your inputs
$query_values = array();
$query_values[':brandid'] = $dress_name = $_POST['Dress_name'];//note that you should change the value in your options here to the unique ids in your database.
$query_values[':sizeid'] = getSize($_POST[]);
$query_values[':colourid'] = getColour($_POST[]);
$query_values[':quantity'] = $_POST['quantity'];
$query_values[':imageurl'] = $_POST['imageurl'];


//Prepare and execute your sql
  //Note that PDO will handle escaping problematic inputs.
$sql = "INSERT INTO INVENTORY ('brandid', 'imageurl') VALUES (:brandid, :sizeid, :colourid, :quantity, :imageurl)";
executeInsert($sql, $query_values);
$inventoryid = mysql_insert_id();

saveSizes($_POST['sizes'], $inventoryid);
saveColours($_POST['colours'], $inventoryid);

function saveSizes($size_array, $inventoryid) {
  $sql = "INSERT INTO INVENTORY_SIZES ('inventoryid', 'sizeid') VALUES (:inventoryid, :sizeid)";
  foreach ($size_array as $sizeid) {
    $query_values = array();
    $query_values[':inventoryid'] = $inventoryid;
    $query_values[':sizeid'] = $sizeid;
    executeInsert($sql, $query_values);
  }
}

function saveColours($colour_array) {
  $sql = "INSERT INTO INVENTORY_COLOURS ('inventoryid', 'colourid') VALUES (:inventoryid, :colourid)";
  foreach ($size_array as $sizeid) {
    $query_values = array();
    $query_values[':inventoryid'] = $inventoryid;
    $query_values[':colourid'] = $colourid;
    executeInsert($sql, $query_values);
  }
}

function executeInsert($sql, $query_values) {
  $query = $conn->prepare($sql);
  $query->execute($query_values);
}

Be sure to use PHP PDO to guard against security issues. I'm fairly sure some of that could be abstracted out/cleaned up better, but this should do the job. (Though I'm also fairly sure there are little bugs as I'm doing this cold and without testing.)