Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

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!

share|improve this question
 
MySQL's boolean type looks like zeros and one. If the value is true, store a 1 in the column. If the value is false (or more simply not true), store a zero. –  editor Jun 20 '12 at 18:20
 
Your checkboxes on the HTML form all have the same value - I think you meant to have their values equal to the appropriate size? –  andrewsi Jun 20 '12 at 18:21
 
Actually, because of the way that MySQL handles booleans it is far better to use TINYINT. The boolean type can cause strange errors: if you're just want an up or down flag, use an integer with length 1. –  Nathaniel Ford Jun 20 '12 at 18:23
 
I've used TINYINT for the columns for different sizes for instance. so for each product in the table it can have either a 1 or a 0 in different columns for different size ranges. I'm interested in how I can get at the data in the array from the check boxes to fill those columns? –  Adam Ross Bowers Jun 20 '12 at 18:30
 
@AdamRossBowers Be sure to 'accept' an answer, if one of them gets close to the solution you need! –  Nathaniel Ford Jun 20 '12 at 19:39
add comment

2 Answers

up vote 5 down vote accepted

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.)

share|improve this answer
add comment
up vote 0 down vote

You need to place the insert statement in an 'else' statement after the 'if' which checks if the name already exists in the database.

I am not sure what exactly you are trying to do , since there is no statement that you are executing to put the 'checkbox' values in the database. You are echo-ing the sizes array to as an output for the administrator to see after he submits this form ,but you are not putting it in the database at all.

What will help is if you can share the database structure ( the column names) . If you don't have any columns for sizes 2-5 & other categories , add those & then change your insert statement to the following :

INSERT INTO names (Dress,Size2to5,Size5to10,Size10to15,Size15to20) VALUES('$dress_name','$size[0]','$size[1]','size[2]','size[3]')

Hope this helps. Do let us know if this works for you.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.