Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem with multiple form choice and inserting into mysql

Posted on 2010-08-17
4
Medium Priority
?
230 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:bookedy
  • 2
4 Comments
 
LVL 6

Assisted Solution

by:merwetta1
merwetta1 earned 1000 total points
ID: 33460637
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
 
LVL 5

Expert Comment

by:sscotti
ID: 33460666
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
 

Author Comment

by:bookedy
ID: 33468905
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
 
LVL 5

Accepted Solution

by:
sscotti earned 1000 total points
ID: 33469640
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question