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

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>

Select allOpen in new window

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.

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.

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.

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");

Select allOpen in new window

I got the exact same error message as the one I posted last time.

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;

Select allOpen in new window

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

Thanks

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.

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

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.

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

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.  

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.

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.