Page 1 of 1

Update MySQL column from Multiple Select Form List

Posted: Fri Oct 03, 2003 11:29 am
by theoph
I understand that when I have a Multiple Select Form List, I'm suppose to change the name of the selected list . . .

from:

Code: Select all

<select name="s_cell" multiple="yes">
to:

Code: Select all

<select name="s_cell&#1111;]" multiple="yes">
. . . so that the multiple selected items can be put into an array.

See http://uk.php.net/manual/en/faq.html.ph ... t-multiple

However when I do so, an empty value is inserted into my MySQL column for the record I'm updating.

Below is the information being passed:

Code: Select all

http://ekk-lez.org/select.php?ID=032908&p_cell=Andover+Hill&last=McGowan&s_cell%5B%5D=Georgetown&s_cell%5B%5D=Smokey+Mtn.&first=David&Submit=Submit&MM_update=Highlight_Test
Note: the "%5B%5D" what is this?

Any help would be greatly appreciated! :D

Posted: Fri Oct 03, 2003 12:45 pm
by scorphus
The %5B and %5D are the hex code for [ and ] respectively.

It should be working. Post the way you are inserting data to MySQL.

Try using POST method instead of GET:

<form name="form1" action="select.php" method="POST">...</form>

Then in select.php, all form information will be available in the $_POST predefined variable.

Cheers,
Scorphus

Posted: Fri Oct 03, 2003 1:23 pm
by theoph
POST didn't work either.

Here is my "update" script. Is there anything in here that would cause the problem?

Code: Select all

<?phpfunction 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_update"])) && ($_POST["MM_update"] == "Highlight_Test")) {
  $updateSQL = sprintf("UPDATE laos SET p_cell=%s, s_cell=%s WHERE id=%s",
                       GetSQLValueString($_POST['p_cell'], "text"),
                       GetSQLValueString($_POST['s_cell[]'], "text"),
                       GetSQLValueString($_POST['ID'], "int"));

  mysql_select_db($database_Ekklesia, $Ekklesia);
  $Result1 = mysql_query($updateSQL, $Ekklesia) or die(mysql_error());

  $updateGoTo = "select.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}

?>

Posted: Fri Oct 03, 2003 1:48 pm
by scorphus
$_GET['s_cell[]'] is an array. So you will have to walk through it to 'mount' query string. But how will you SET s_cell to more than one value? Will it be a sum or something?

Posted: Fri Oct 03, 2003 4:24 pm
by theoph
The array is coming from a list box where the client can choose up to 11 selectable items, which are words. I want to put those words, which are selected by the client, into a MySQL column for that record.

The array is produced just fine, however, I having difficulty passing it to the MySQL database.

It would seem, I need to put the array in some sort of string, but knowing how to do that and where to put it in the script is at the moment beyond my expertise.

Posted: Fri Oct 03, 2003 8:17 pm
by JAM
Perhaps this will give you some hints...

Code: Select all

<pre>
<?php
$array = array(
     'A' => 'foo',
     'B' => 'bar',
     '1' => 'two',
     '2' => 1,
     '3' => array(1,'six',7,2)
);
  print_r($array);
  $variable = base64_encode(serialize($array)); // before INSERT
  echo $variable.'<br>'; 
  print_r(unserialize(base64_decode($variable))); // after SELECT
?>

Posted: Sat Oct 04, 2003 10:53 am
by theoph
Came across the suggestion else where that I probably need to serialize the array. I just wish there was a tutorial showing how to do all of this. I can't believe there's so little information regarding php and multiple select menu boxes.

Posted: Sat Oct 04, 2003 2:15 pm
by JAM
Tutorial?
If you can explain further, we could whip one up. But as far as I know, my above idea would be sufficient.

serialize() alone is not good, as it produces " (double quotes) that wont work well with database queries. There are many solutions to remove them, but I think that adding base64_encode() is easy enough.

Posted: Tue Oct 07, 2003 9:28 am
by theoph
The following script worked for me and I didn't need to serialize.

Code: Select all

<?php if($Submit == "Submit") /* Activated by clicking the form button "Submit." */
{
	foreach ($scell as $groups)
	{
		$array_data .= $groups."," ; /* Puts the contents of the array into a variable. */
	}
	$array_data = substr($array_data,0,-1); /* Removes the last character in the variable "comma." */
	$db = mysql_select_db($database_Ekklesia, $Ekklesia);
	$query = "UPDATE laos SET scell='$array_data' WHERE id='$ID'";
	$result = mysql_query($query) or die ("Couldn't execute query.");
}?>
In regards to the tutorial, the problem is that many are having a problem with knowing what to do with multiple select menus in forms because Dreamweaver's automatic application of php scripts wont properly create a script that works. Even on the Dreamweaver forums, questions go unanswered because know one seems to know what to do. If some did write a tutorial I know many would benefit greatly.