save all html select box contents to sql server table

Question

EDIT : PLEASE FIND MY SOLUTION BELLOW

I have two select boxes in a FORM that i populate from two different tables. SELECT1 displays client specific options. SELECT2 displays all available options except those already on SELECT1

Everything works fine populating them, moving items from one to the other etc.

What I don't have a clue is how to, after submit of the FORM, I store those items of SELECT1 back to the database. For each item on that SELECT box I need to check if it already exists on MY_TABLE_2 and if it does not, store it.

When I check var_dump(SELECT1) I only see the value of the last selected item but it normally contains more than one item in SELECT1.

For now I'm calling a stored procedure for all the other fields that will go on MY_TABLE_1 and that is OK. I would need to do some transaction that updates the two tables or rolls back if i get an error. That is working if i store only one item.

But how I send to the stored procedure ALL the items.

Any help or clues?

Here's a print-screen if it can help understand:

The code to generate the form:

echo '<select name="ed_clilabelcodes" id="ed_clilabelcodes" style="width: 200px" multiple>';
while($row = sqlsrv_fetch_array($dvclilablist)){
  echo '<option value="'.$row[0].'">'.$row[0].' - '.$row[1].'</option>';
}
echo '</select>';

----------- MY SOLUTION -----------

All items on any of those select boxes exist in a table (dbo.MyItems) identified by the field OptCode. What I really needed was to store witch of those items were selected by a particular client. I was thinking of having a second table where I would store those items but I was in reality duplicating information and I didn't find it to be the best solution.

Since I really only needed those selected codes attached to the client file I did the following:

I have one javascript function inspired from HERE that allows me to move items between the two select boxes and, by the way, from HERE to create a function that sorts the items in the boxes. Each time I add or remove an item from the client's list box I immediately call the sort function, so I just update a hidden input value with all item values (codes) present in the select box immediately calling this javascript function :

function RecheckCodes() {
  var vlist = document.getElementById("ed_clilabelcodes");
  var vlabcodes = "";
  var i;
  for (i = 0; i < vlist.length; i++) {
    if (i == 0) {
      vlabcodes = vlabcodes + "\'" + vlist.options[i].value + "\'";
    }
    else {
      vlabcodes = vlabcodes + ",\'" + vlist.options[i].value + "\'";
    }
  }
  document.getElementById("v_clilabcodes").value = vlabcodes;
}

On FORM Post i just save that string in a client field.

Latter i can repopulate the boxes for edit using following 2 stored procedures to get the items (first SQL will get the string with the codes and is called from the 2nd one that gets the item description from items table).

ALTER PROCEDURE [dbo].[DavGetOptCodeByClient] 
  @vclicode nvarchar(12),
  @vopttype nvarchar(12),
  @voptcodes nvarchar(300) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  SELECT @voptcodes = optcodes
  FROM   dbo.dav_client_options
  WHERE  clinumber = @vclicode AND opttype = @vopttype
END



ALTER PROCEDURE [dbo].[DavGetOptDetailByClient] 
  @vclicode nvarchar(12),
  @vopttype nvarchar(12)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @v2optcodes nvarchar(1000)
  DECLARE @vsql nvarchar(1000)
  exec dbo.DavGetOptCodeByClient @vclicode,@vopttype, @v2optcodes OUTPUT
  SET @vsql = 'SELECT optcode, optdetail ' +
            'FROM dbo.dav_global_options ' +
            'WHERE ("optcode" IN ('+@v2optcodes+')) AND (opttype = '''+@vopttype+''')'
  EXEC sp_executesql @vsql
END

So everything is working fine for what I needed even if I'm sure some improvement can be made on the code.

Answer 1

to be able to send all the elements the simplest option is to select them, it is not enough that they are in the list but they have to be selected, for that it must be activated the "multiple" option of the select, once done that by pressing the key "control" or "shift" you can make multiple select in your list.

<form action="#" method="post">
        <select name="Color[]" multiple> <!--the option multiple must be active and be array -->
            <option value="Red">Red</option>
            <option value="Green">Green</option>
            <option value="Blue">Blue</option>
            <option value="Pink">Pink</option>
            <option value="Yellow">Yellow</option>
        </select>
        <input type="submit"  value="Get Selected Values" />
    </form>

Another option is to add this small javascript (jQuery) in an html so you do not have to press or control or shift when you select several options, just click and you will be selected

$('option').mousedown(function(e) {
    e.preventDefault();
    $(this).prop('selected', !$(this).prop('selected'));
    return false;
});

Source :How to avoid the need for ctrl-click in a multi-select box using Javascript?

And at the end you have your data with a simple php

<?php
$data=$_POST;
if($data) {
    foreach ($data['Color'] as $option) {
        echo $option;
    }
    var_dump($data);
}
?>