Update MySQL column from Multiple Select Form List

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Update MySQL column from Multiple Select Form List

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Post 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));
}

?>
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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?
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
?>
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
theoph
Forum Commoner
Posts: 47
Joined: Wed Jul 30, 2003 5:26 pm
Location: Lexington, KY USA

Post 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.
Post Reply