Problem with multiple form choice and inserting into mysql

Hi,

Pleae bare with me as my php and mysql is pretty basic and i use most of the tools available to me in Dreamweaver to work with databases.

I am trying to do a simple task but can't work out how to do it. Part of a form i am making has a list of countries. I have made this list a dropdown box with multiple options selectable. How do i then go about inserting these multiple options into my database though as at the moment, the last one picked is only inserting into my database.

My code is as follows:

<?php require_once('Connections/bookedy.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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 test (countries) VALUES (%s)",
                       GetSQLValueString($_POST['select'], "text"));

  mysql_select_db($database_bookedy, $bookedy);
  $Result1 = mysql_query($insertSQL, $bookedy) or die(mysql_error());

  $insertGoTo = "test.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>
<form action="<?php echo $editFormAction; ?>" method="POST" name="form1" id="form1">
        <label></label>
            <label>
            <select name="select" size="1" multiple="multiple">
              <option value="Country A">Country A</option>
              <option value="Country B">Country B</option>
              <option value="Country C">Country C</option>
            </select>
            <br />
            <br />
            <br />
<input type="submit" name="Submit" value="Submit" />
          </label>
            <input type="hidden" name="MM_insert" value="form1">
            </form>

Open in new window

bookedyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

merwetta1Commented:
The first part of the solution is to add brackets to the select name in line 51 so it looks like this:

<select name="select[]" size="1" multiple="multiple">

Then the multiple values will be passed in an array $_POST['select']

How do you want to save the multiple country values to the database? As a joined string (i.e. "Country A,CountryB")? It would probably be best to have a database table with countries mapped to users, and then you would insert each country choice separately:


foreach($_POST['select'] as $country)
{
      $country = mysql_real_escape_string($country);      // protection against malicious input
      $query = "insert into country_choices(user, country) values ($user_id, '$country');
}

Let me know if that helps. Cheers!
0
sscottiCommented:
Not sure what you wanted to do with the values.  You can pass the multiselect values as an array and then Parse them when you POST the form.  I modified the code so it does that for you and prints out the submitted item values.

Not sure how you wanted to handle the values.  Are you putting those into a single field in a database row or some other way.  If you know MySQL you should be able to figure out how to insert them in the database once you have the passed values.
<?php require_once('Connections/bookedy.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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")) {
    $select = $_POST['select'];
    if (count($select)>0) {

    foreach ($select as $country) {
    echo $country."<br>\n";
    //Print out the selection
    }

  $insertSQL = sprintf("INSERT INTO test (countries) VALUES (%s)",
                       GetSQLValueString($_POST['select'], "text"));

  mysql_select_db($database_bookedy, $bookedy);
  $Result1 = mysql_query($insertSQL, $bookedy) or die(mysql_error());

  $insertGoTo = "test.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
  }
}
?>
<form action="<?php echo $editFormAction; ?>" method="POST" name="form1" id="form1">
        <label></label>
            <label>
            <select name="select[]" size="1" multiple="multiple" style="height:100px">
              <option value="Country A">Country A</option>
              <option value="Country B">Country B</option>
              <option value="Country C">Country C</option>
            </select>
            <br />
            <br />
            <br />
<input type="submit" name="Submit" value="Submit" />
          </label>
            <input type="hidden" name="MM_insert" value="form1">
            </form>

Open in new window

0
bookedyAuthor Commented:
Hi,

Thanks for your responses.

I would like to enter it into the same table that i am entering everything else. Therefore, i would like it to be inserted as "countrya, countryb, countryc" etc.

Can you show me how I can create that output?

Many thanks!
0
sscottiCommented:
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
    $select = $_POST['select'];
    if (count($select)>0) {
    $test = implode(",", $select );
...
...
}

That would give you text ($test)  that would be a comma delimited list of the countries.  You could use that if you want to put those in a single column in the database.  Is that what you wanted to do?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.