• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1326
  • Last Modified:

Drop down list with multiple selections populated from DB - how to save selections in one variable

Hi, I have a drop down list that is populated by a call to a database that retrieves a list of Titles. I have set the list to allow for multiple selections. I want to save the items selected in one variable, named Title that is in a different table of a database. The only thing that is saved is the last item selected. I have seen information about using arrays, but everything I have tried produces an error message.

The code was mostly generated by Dreamweaver. I've attached relevant parts of the code.

Thanks for your help!
Here is the relevant part of the initial php code - gets Titles from DB table and inserts selections into another DB table:

mysql_select_db($database_connTalkingpages, $connTalkingpages);
$query_rsTitles = "SELECT Title FROM `Catalog` WHERE Status = 'Available' ORDER BY Title ASC";
$rsTitles = mysql_query($query_rsTitles, $connTalkingpages) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "checkout")) {
  $insertSQL = sprintf("INSERT INTO Checkout (email, password, lastname, textboxinfo, Title) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['password'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                        GetSQLValueString($_POST['textboxinfo'], "text"),
                       GetSQLValueString($_POST['Title'], "text"));

  mysql_select_db($database_connTalkingpages, $connTalkingpages);
  $Result1 = mysql_query($insertSQL, $connTalkingpages) or die(mysql_error());


The code for the form is:

<form action="<?php echo $editFormAction; ?>" id="checkout" name="checkout" method="POST">
      <p>
        <label>Enter information here:<br />
        <textarea class="tt2" name="textboxinfo" id="textboxinfo" cols="45" rows="7">&nbsp;</textarea>
        </label>
      </p>
      <h2>Selecting Books to Check Out</h2>
      <div class="content3" align="center">
      <select class="tt2" name="Title" size="15" multiple="multiple" id="Title">
          <option SELECTED value="null" <?php if (!(strcmp("null", $row_rsTitles['Title']))) {echo "selected=\"selected\"";} ?>>Please select one or more titles</option>          
          <?php
			do {  
			?>
          <option value="<?php echo $row_rsTitles['Title']?>"<?php if (!(strcmp($row_rsTitles['Title'], $row_rsTitles['Title'])))  ?>><?php echo $row_rsTitles['Title']?></option>
          <?php
} while ($row_rsTitles = mysql_fetch_assoc($rsTitles));
  $rows = mysql_num_rows($rsTitles);
  if($rows > 0) {
      mysql_data_seek($rsTitles, 0);
	  $row_rsTitles = mysql_fetch_assoc($rsTitles);
  }
?>
        </select></div>
      
    <input type="hidden" name="email" value="<? echo $_POST['email'];?>" />
    <input type="hidden" name="password" value="<? echo $_POST['password'];?>" />
    <input type="hidden" name="lastname" value="<? echo $_POST['lastname'];?>" />
    <input class="btn" name="Submit" type="submit" id="Submit" value="Submit"  />   
    <input class="btn" type="reset" name="reset" id="reset" value="Reset" /></p>
        
    <input type="hidden" name="MM_insert" value="checkout" />
    </form>

Open in new window

0
judypage4
Asked:
judypage4
  • 7
  • 3
  • 3
  • +1
1 Solution
 
Greg AlexanderLead DeveloperCommented:
Try this:

Here is the relevant part of the initial php code - gets Titles from DB table and inserts selections into another DB table:

mysql_select_db($database_connTalkingpages, $connTalkingpages);
$query_rsTitles = "SELECT Title FROM `Catalog` WHERE Status = 'Available' ORDER BY Title ASC";
$rsTitles = mysql_query($query_rsTitles, $connTalkingpages) or die(mysql_error());
$row_rsTitles = mysql_fetch_assoc($rsTitles);
$totalRows_rsTitles = mysql_num_rows($rsTitles);


if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "checkout")) {
	
	$title = '';
	foreach($_POST['Title'] as $key => $value){
		//below is separated by a space, you could use commas or whatever you want
		$title .= $value.' ';	
	}
	
  $insertSQL = sprintf("INSERT INTO Checkout (email, password, lastname, textboxinfo, Title) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['password'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                        GetSQLValueString($_POST['textboxinfo'], "text"),
                       $title);

  mysql_select_db($database_connTalkingpages, $connTalkingpages);
  $Result1 = mysql_query($insertSQL, $connTalkingpages) or die(mysql_error());

?>
The code for the form is:

<form action="<?php echo $editFormAction; ?>" id="checkout" name="checkout" method="POST">
      <p>
        <label>Enter information here:<br />
        <textarea class="tt2" name="textboxinfo" id="textboxinfo" cols="45" rows="7">&nbsp;</textarea>
        </label>
      </p>
      <h2>Selecting Books to Check Out</h2>
      <div class="content3" align="center">
      <select class="tt2" name="Title" size="15" multiple="multiple" id="Title">
          <option SELECTED value="null" <?php if (!(strcmp("null", $row_rsTitles['Title']))) {echo "selected=\"selected\"";} ?>>Please select one or more titles</option>          
          <?php
                        do {  
                        ?>
          <option value="<?php echo $row_rsTitles['Title']?>"<?php if (!(strcmp($row_rsTitles['Title'], $row_rsTitles['Title'])))  ?>><?php echo $row_rsTitles['Title']?></option>
          <?php
} while ($row_rsTitles = mysql_fetch_assoc($rsTitles));
  $rows = mysql_num_rows($rsTitles);
  if($rows > 0) {
      mysql_data_seek($rsTitles, 0);
          $row_rsTitles = mysql_fetch_assoc($rsTitles);
  }
?>
        </select></div>
      
    <input type="hidden" name="email" value="<? echo $_POST['email'];?>" />
    <input type="hidden" name="password" value="<? echo $_POST['password'];?>" />
    <input type="hidden" name="lastname" value="<? echo $_POST['lastname'];?>" />
    <input class="btn" name="Submit" type="submit" id="Submit" value="Submit"  />   
    <input class="btn" type="reset" name="reset" id="reset" value="Reset" /></p>
        
    <input type="hidden" name="MM_insert" value="checkout" />
    </form>

Open in new window

0
 
Ray PaseurCommented:
Dreamweaver is not really our friend when we want to create good PHP code.  Unfortunately.

The general design pattern would go something like this...

The client table and the titles table would get joined by a "junction" table.  Junction tables are little two-column tables that create a many-to-many relationship between other entities in a relational data base.  In this case the junction table would contain a key to the client and a key to the title.

The selected titles would arrive in the action script in an array that is a sub-array of $_POST.  You would find this array in $_POST["Title"] (the var names are case-sensitive in PHP).  You might use an iterator like foreach() to connect each of the selected titles with the client identifier.
0
 
judypage4Author Commented:
I actually have 3 tables: Results (which contains the "clients" or users of the site, with their email, password, name, etc); Catalog (which contains Title and other information about books); and Checkout (which receives the results of the form I posted where each entry is a request from a user for a set of books). Each row of checkout is like an order, and I want the one variable Title to contain all the titles requested as one entry. Can you suggest how to get the multiple selections from the drop down list to all be sent to Title in one record, along with the other information that is being posted? Thanks.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
judypage4Author Commented:
I tried the code suggested by galexander07.

When I preview the page in the browser, instead of getting the confirmation page, I get the following error message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Line 1 is just a typical php opening and isn't the problem. I have checked the new code and can't see any syntax error. The new code I inserted was copied and pasted from what was suggested. Any ideas? Thanks.
0
 
Greg AlexanderLead DeveloperCommented:
Try changing your query to this

$insertSQL = sprintf("INSERT INTO Checkout (email, password, lastname, textboxinfo, Title) VALUES (%s, %s, %s, %s, %s)", GetSQLValueString($_POST['email'], "text"),GetSQLValueString($_POST['password'], "text"),GetSQLValueString($_POST['lastname'], "text"),GetSQLValueString($_POST['textboxinfo'], "text"),"$title");

Open in new window

0
 
judypage4Author Commented:
I got the exact same error message as the one I posted last time.
0
 
Greg AlexanderLead DeveloperCommented:
Alright I used the functions that you are using on the other variables, I also echoed the variable that contains your query, the error is in the query somewhere

 $insertSQL = sprintf("INSERT INTO Checkout (email, password, lastname, textboxinfo, Title) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['password'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                       GetSQLValueString($_POST['textboxinfo'], "text"),
                       GetSQLValueString($title, "text"));
                       
echo $insertSQL;

Open in new window


Let me know if the error is still there and what the query looks like

Thanks
0
 
judypage4Author Commented:
Here is what was returned:
INSERT INTO Checkout (email, password, lastname, textboxinfo, Title) VALUES ('judypage@att.net', 'testcase', 'Page', 'Â ', NULL)Column 'Title' cannot be null

So it is not picking up any of the values selected for Title. BTW, I inserted a blank space in the field textboxinfo because I was getting a similar error message when there was nothing there (it is an optional field) - this shows up as the character before NULL in the message.
0
 
Ray PaseurCommented:
I'm looking at this line of code:

GetSQLValueString($_POST['Title'], "text")

And I'm wondering if maybe "text" is the wrong data type.  If you want to allow multiple title selections, you will not get a text string, you will get an array with from zero to several text strings.  That is what I was suggesting when I wrote your script would find this array in $_POST["Title"]

The whole concept of having more than one data element in a column of a data base is kind of off base.  That is what I was suggesting when I wrote about a junction table that would contain a key to the client and a key to the title.  The relationship established by a junction table is illustrative of how "relational data base" gets its name.

Dreamweaver is not really a good tool for this sort of work.  You might want to step back from this particular application and take some time to learn how PHP and MySQL work together.  To that end, this book would be a fairly good place to start.  
http://www.sitepoint.com/books/phpmysql4/

You would also want to learn about data base normalization.  The results from this search might help.
http://lmgtfy.com?q=Should+I+Normalize+My+Database

Have you considered getting a professional data base administrator to help you design the tables and queries?  It might save you a lot of time.  

I don't think a question at EE can really help you fix all of the issues here -- but if you want to break it down into simpler questions like, "How do I accept more than one input from an HTML form?" or "What software and learning resources would you recommend if I wanted to build a shopping cart application?" --  we might be better able to offer more constructive help.

Best of luck with your app, `Ray
0
 
judypage4Author Commented:
Ray_Paseur:

I appreciate that you took the time to respond to my inquiry; however, your response was not helpful. First, I have already developed most of the website using Dreamweaver and obviously have paid for Dreamweaver, which is a respected program. Your suggesting that Dreamweaver is not a good tool and that I should start learning PHP and MySQL does not help me, given that I am in the middle of this project.

Second, I understand what a relational data base is and don't need to be told how it got its name.

Third, this website is for a non-profit organization that does not pay any volunteers. We do not have a budget to "get a professional data base administrator."

Fourth, there are not a lot of issues that I have asked about. I have made a request to help with one specific issue, not a morass of problems. If you can't suggest how to fix the very specific problem I have posed, it does not help to suggest I should have asked a different. question.
0
 
Ray PaseurCommented:
judypage4:

Suit yourself and feel free to ignore my advice if you think it is not helpful.  

Dreamweaver "is a respected program" in the same way that a cement truck is a respected vehicle.  It has specific applications and there are other applications that don't really fit.  I don't mean to cast aspersions on Dreamweaver; it has its place in web development.  But I have seen an awful lot of really bad PHP code that came about because the programmers relied on Dreamweaver as a way to learn PHP.  That is why I recommend different learning resources.

I cannot recommend a way to do what you are trying to do; it's just not something that anyone would do in data base applications.  Suggest you leave the question open for a while and see if someone else can weigh in on it.

Also, if you have a local PHP User Group, you might want to ask if you could bring the problem to one of their meetings.  Perhaps you would find a willing volunteer who supports your cause and would share some pro-bono programming with your non-profit.  I've done that before for my church and for other causes that I support.

Good luck, ~Ray
0
 
Jason C. LevineNo oneCommented:
Hi Judy,

In order to do this, you need to change the form field in the HTML so that it stores multiple selections as an array, which you do by adding [] to the name attribute:

<select class=tt name=Title[] ...

Unfortunately, you do need to do some custom PHP coding to handle that form variable on submit to parse the array into readable text and store it in the database column.  DW will not handle this automatically when you do a multiple selection box.   I'm on an iPad right now and cannot access my code archives but I have a sample of this on my home machine and can give it to you later.

Ray is sort of correct in his assessment of DW. It is a tool meant mainly for wysiwyg design.  On top of that, it has some functions that allow non coders to perform a number of tasks that would otherwise require coding ability and as long as your intended actions fit within DW's limits, a non-coder can go very far.  But eventually everyone hits the program's limit and the only remedy from that point is to know how to manipulate the code by hand to get what you want.   Unless you restrict yourself to basic one field to one column forms, you will need to learn this stuff at some point or be prepared to hire a programmer on an ad hoc basis to get you over these little humps.

The code it produces is not all that much worse than what most humans produce.  The main beef people have is that DW produces a LOT of code for a single action and most of that code could be called via an include file, so it's generally considered bloated, inelegant, and hard to read.  But it works, and if it works, it works.

Hope that helps to clarify things.  I will be home a bit later tonight and will post that sample code when I have a minute to breathe.  
0
 
Jason C. LevineNo oneCommented:
Okay, as promised.  Here is the code to take an array of multiple selections on input and make it readable and insertable.

<?php
if (isset($_POST["formfieldname"])) {
      $Whatever = implode(",",$_POST['formfieldname']);
}
?>

As stated above, the trick to get this to work (at least in my environment) is to add [] to the form field name:

<select class="tt2" name="Title[]" size="15" multiple="multiple" id="Title">

So, the above becomes:

<?php
if (isset($_POST["Title"])) {
      $Whatever = implode(",",$_POST['Title']);
}
?>

and then you stick $Whatever into the insert query to get it into your database:

GetSQLValueString($Whatever, "text")

instead of just $title as you have it in the original query.

Here's a quick page to show you the thing in action:

<?php 
if (isset($_POST["testmulti"])) {
	$Type = implode(",",$_POST['testmulti']); 
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<form method="post" action="test.php">
<select name="testmulti[]" size="10" multiple>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
</select>
<input type="submit" name="Submit" id="Submit" value="Submit" />
</form>
<?php echo $Type; ?>
</body>
</html>

Open in new window

0
 
judypage4Author Commented:
Thanks very much for the suggestions. I just left today on vacation and will see whether I have time in the next day or two to try what you suggested.
0
 
judypage4Author Commented:
jason1178:

Thanks very much! This worked exactly as I wanted based on making the three changes you outlined in your response. I really appreciate the help you provided.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now