Multi-Line List Field with Values, PHP, and mySQL

[dwdata]

I have a form which allows for the user to pick ZERO or MANY items. I was hoping when I SUBMIT the form, all the values would update in the field.

Here is the form code:

<tr>
      <td><div align="right">Instruments:</div></td>
      <td width="116" align="center" valign="middle">
        <div align="left">
          <select name="Instrument" size="5" multiple="multiple" id="Instrument">
            <option>Select from the list below...</option>
            <option value="Leader">Leader</option>
            <option value="Singer">Singer</option>
            <option value="Piano">Piano</option>
            <option value="Synth">Synth</option>
            <option value="Acoustic Guitar">Acoustic Guitar</option>
            <option value="Electric Guitar">Electric Guitar</option>
            <option value="Bass Guitar">Bass Guitar</option>
            <option value="Drums">Drums</option>
            <option value="Percussion">Percussion</option>
            <option value="Sax/Horn">Sax/Horn</option>
            <option value="Flute">Flute</option>
          </select>
        </div></td>
      <td width="277" align="center" valign="middle"><div align="left"><span class="style5">To select multiple, hold down your <br />
      Command Key (MAC) or ALT (PC)</span></div></td>
    </tr>

The form action post to another page which does the PHP Add code:

<?php
// -- Add a record -- 
ServerConnect();
DB_Connect();

// Get values from form.
$First_Name=$_POST['First_Name'];
$Last_Name=$_POST['Last_Name'];
$Email=$_POST['Email']; 
$Contact_Phone=$_POST['Contact_Phone'];
$Address=$_POST['Address'];
$City=$_POST['City']; 
$State=$_POST['State'];
$Zip=$_POST['Zip'];
$Username=$_POST['Username']; 
$Password=$_POST['Password']; 
$ADMIN=$_POST['ADMIN'];
$Instrument=$_POST['Instrument'];
$Notes=$_POST['Notes']; 

$sql="INSERT INTO Volunteers (First_Name, Last_Name, Email, Contact_Phone, Address, City, State, Zip, Username, Password, ADMIN, Instrument, Notes) VALUES ('$First_Name','$Last_Name','$Email','$Contact_Phone','$Address','$City','$State','$Zip','$Username','$Password','$ADMIN','$Instrument','$Notes')";
$result = mysql_query($sql) or die(mysql_error());
echo "<span class=\"style2\">Volunteer Added...</span>";
?>

But when I select 4 instruments, then SUBMIT, I check my DB Table and it only has one of the values.

Maybe I just don't understand how to use this type of field format with PHP/mySQL. I am just starting out.

Once I get this answered, the next question will be "When I setup my UPDATE page, how do I get those values to be selected?" I will save that for another thread if it it is to complex to deal with in this one.

Thanks for any assistance ;-)

[emarshah]

Hello dwdata,

Hope you are doing fine, your first question, you should consider your table, it is not NORMALIZED (this table contains multi value dependency), so first make another table in which only username and instrument name are the columns, the username will be Foreign key from this Volunteers table. Then first run the sql query for user's information and then run multiple sql INSERT queries for each entry in that new table having only Username and Instrument.

Now when you want to update the record, you can retrieve all the instruments according to the Username in this New table. So, now you can make multiple insertion and updates using UPDATE queries. I hope this will help you, if it did, add it to my reputation and mark thread as solved. Thankyou.