Insert value into a db where one form value is an array

Question

I have a form with a number of fields to fill in. As follows: The form

 <form action="<?php echo $editFormAction; ?>" id="form1" name="form1" method="POST">

        <input type="checkbox" name="Category[]" value="Casio Digital Pianos" id="Category_0" />
          Casio piano</td>
        <td><input type="checkbox" name="Category[]" value="Casio Keyboards" id="Category_2" />
          Casio Keyboard</td>
        <td><input type="checkbox" name="Category[]" value="Recording" id="Category_3" />
    Recording</td>
        <td><input type="checkbox" name="Category[]" value="Modules & Add-on s" id="Category_4" />
    Modules & Add-on's</td>
      </tr>
      <tr>
        <td> <input type="checkbox" name="Category[]" value="Kawai Digital Pianos" id="Category_5" />


      </label>
      </p>
      <p>
        <label>Manufacturer
          <select name="Manufacturer" id="Manufacturer">
            <option value="Casio">Casio</option>
            <option value="Kawai">Kawai</option>
            <option value="Korg">Korg</option>
            <option value="Roland">Roland</option>
            <option value="Yamaha">Yamaha</option>
            <option value="Ketron">Ketron</option>
            <option value="Boss">Boss</option>
            <option value="Samson">Samson</option>
            <option value="Orla">Orla</option>
            <option value="Technics">Technics</option>
            <option value="Ultimax">Ultimax</option>
          </select>
        </label>
      </p>
        <p>
        <label>Model
          <input type="text" name="Model" id="Model" />
        </label>
      </p>
      <p>
        <label>Color
          <input type="text" name="Color" id="Color" />
        </label>
      </p>
      <p>
        <label>


      <p><input name="submit" type="submit" value="submit" /></p>
      <input type="hidden" name="MM_insert" value="form1" />
    </form>

I would like for each value of the category a new row in the database

I am using DW to start with as follows: Insertion code:

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO products (Category, Manufacturer, Model, Color) VALUES (%s, %s, %s, %s)",
                       GetSQLValueString($_POST['Category'], "text"),
                       GetSQLValueString($_POST['Manufacturer'], "text"),
                       GetSQLValueString($_POST['Model'], "text"),
                       GetSQLValueString($_POST['Color'], "text");


  mysql_select_db($database_dconn, $dconn);
  $Result1 = mysql_query($insertSQL, $dconn) or die(mysql_error());
}

Which doesn't work because it somehow can't find the Category I presume this is because it is an array. Is there a way to make this work so it does insert the form for each Category when ticked into a new field. I have tried the following:

if (isset($_POST['Category'])) {
    foreach($_POST['Category'] as $value);

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
etc...

But that doesn't seem to work at all. Any help welcome

Answer 1

First, read up on PDO and stop using mysql() functions. Next, you were close with your foreach, but stop using isset - this will always return true for posted forms even if a field is empty (as the variable is initialised due to being posted, even if it's NULL - but don't try to check for NULL as it'll still pass as true on a value of ""), instead use if(!empty($_POST['field'])). In regards to your foreach against the category array, try the below.

Now, there are a few issues i've noticed. Are you looking to run multiple SQL inserts depending on the number of checked categories? Will these have identical values for color, manufacturer etc? If so, you're on the right track. If not, instead I would recommend you redo the entire form (E.G. if you need unique values for each checked category). Next, why are you passing a hidden field with your form names value? Is this to validate if the array is posted?

Last but not least, get_magic_quotes_gpc() - No. Just no. First, using PDO negates the need to sanitize strings on the way into a SQL query (Though kudos for understanding the dangers, if not winning points for execution). Further, on display all you really need then is htmlspecialchars() to sanitize any displayed output. This will do the trick and remove the need for your function.

Edit: Try the below (without any HTML formatting)

Could you not simply try:

if(!empty($_POST['submit'])) {

    foreach ($_POST['category'] AS $val) {
    # $val will hold the value of your checkbox
    # Run SQL insert
    }

} else {
# Display HTML form
}