Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with multiple form choice and inserting into mysql

Posted on 2010-08-17
4
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

661 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