Skip to main content
G
goldbecca
Participating Frequently
October 10, 2011
Answered

Populating a drop down form with SET values from MySQL database

  • October 10, 2011
  • 2 replies
  • 3824 views

I'm creating a simple CMS which will allow staff to add new items to their (furniture sales) website.

I want them to be able to choose from the SET list of values for certain fields to make sure the items are displayed on the website correctly (ie to avoid typos etc).

I've spent a long time trawling forums and have so far got the below code.  When it worked I could only get the drop down options from data held in the database, as opposed to the list of SET values I'd originally put in my database.

Eg under the Field called 'Type', I have assigned these values:  set('table','chair','sideboard','dresser','winerack','bookcase','mirror','drawers','chests','display','tvunit','bedside','bed','wardrobe','dressingtab','blanketbox','desk','filingcab','workstation')

These are what I want displayed in the drop down, not just the five values of items already in there. 

However my CMS page is now not displaying anything in the drop downs and I can't figure out why not!

So my question is, firstly any ideas what's wrong with my code?  And secondly, how to I populate the drop downs with the SET values?

Many thanks in advance for your help!

I didn't want to bore you, so the code below is just the bit that's relevant to my question, rather than all the connection info and $POST php etc.

                //This is in my php code at the top of my page

                

 $sql = mysql_query("SELECT DISTINCT id, supplier, room, type, material, finish, bespoke, image FROM furniture ORDER BY ASC");
                    while($row = mysql_fetch_assoc($sql))
                    {
                $supp .= "<option value=\"{$row['supplier']}\">{$row ['supplier']}</option>";
                $room .= "<option value=\"{$row['room']}\">{$row['room']}</option>";
                $type .= "<option value=\"{$row['type']}\">{$row['type']}</option>";
                $material .= "<option value=\"{$row['material']}\">{$row['material']}</option>";
                $finish .= "<option value=\"{$row['finish']}\">{$row['finish']}</option>";
                $bespoke .= "<option value=\"{$row['bespoke']}\">{$row['bespoke']}</option>";
                    }

                //This is within my html

                

<form action="<?php echo $editFormAction; ?>" method="POST" enctype="multipart/form-data" name="form1"  id="form1">
                <label for="supplier">Manufacturer</label>
                <select name="supplier" id="supplier" title="<?php echo $row_add['supplier']; ?>" >
                  <? echo $supp; ?>
                 </select>
       
                 <label for="room">Room</label>
                <select name="room" id="room" title="<?php echo $row_add['room']; ?>" >
                  <? echo $room; ?>
                 </select>
                       <label for="type">Type</label>
                      <select name="type" id="type" title="<?php echo $row_add['type']; ?>">
                       <? echo $type; ?>
                      </select>
   
                      <label for="material">Material</label>
                      <select name="material" id="material" title="<?php echo $row_add['material']; ?>">
                      <? echo $material; ?>
                          </select>
       
                          <label for="finish">Finish</label>
                      <select name="finish" id="finish" title="<?php echo $row_add['finish']; ?>">
                        <? echo $finish; ?>
                          </select>
   
                       <label for="bespoke">Can be made-to-measure?</label>
                       <select name="bespoke" id="bespoke" title="<?php echo $row_add['bespoke']; ?>">
                      <? echo $bespoke; ?>
                      </select>
                              <input type="Submit" name="Add" id="add" value="Add" />
                      <input name="date" type="hidden" id="hiddenField" value="<?php echo $todayis['date']; ?>">
                      <input type="hidden" name="MM_insert" value="form1" />
                      </form>
This topic has been closed for replies.
Correct answer bregent

Thanks, bregent.  I try to be good when I work with these things, and that just seemed bad.

So, would you agree with my alternate approach?


>So, would you agree with my alternate approach?

Yep, that's how it should be done.

Note the OP also mentioned they are populated 6 dropdowns:

>The 6 fields I want to post, via drop downs within my one form are: supplier, room, type, material, finish and bespoke.

So you could create a separate table for each...but what I would probably do is store all in a single table (depends on whether you need to store additional details or if this is just a pick list:

optionsTbl

ID     category     value

------------------------------

1     supplier     abc

2     supplier     xyz   

3     room         living

4     room         dining

Grab the values using a multiple query recordset:   Select value from optionsTbl WHERE category = 'suppplier' ; Select value from optionsTbl WHERE category = 'room' ; etc

Then populate the drop-downs by looping through each query in the recorset and use next_result() to loop through the next set.

2 replies

G
goldbeccaAuthor
Participating Frequently
October 11, 2011

Thanks very much for all of your help!  It had been baffling me for days and what you've all said makes perfect sense.

I think I will create one table for all the six fields as they are purely to make sure staff don't mispell the options using a text field.

This is my first post ever on a forum and I'm so impressed!

Thanks

Becca  

MurraySummers
MurraySummers
Inspiring
October 10, 2011

I'm a little confused by what is in the field called 'type'.  Can you show me?  It looks like you are expecting that field to have multiple values?!

Also, your code above is specifying 6 different select fields - is that correct?

G
goldbeccaAuthor
Participating Frequently
October 10, 2011

Hi Murray,

Thanks for your questions. 

You're correct - my database has 6 different select fields.  They don't need to be fancy and are not dependent upon each other.  It also has other fields that are straightforward text, but I removed them to try and simplify what I was asking. 

The 6 fields I want to post, via drop downs within my one form are: supplier, room, type, material, finish and bespoke.

In my database under the field called 'type', I have given it a SET type and the values of all the types of furniture they sell (hence my 'set' values in my example above: 'table','chair','sideboard','dresser','winerack','bookcase','mirror','drawers','chests','display','tvunit','bedside','bed','wardrobe','dressingtab','blanketbox','desk','filingcab','workstation')

For each of the six, I had originally set up the form in HTML like this:

      <label for="type">Type</label>
      <select name="type" id="type" title="<?php echo $row_add['type']; ?>">
        <option>bed</option>
        <option>bedside</option>
        <option>blanket box</option>
        <option>bookcase</option>
        <option>chair</option>
        <option>desk</option>
        <option>display unit</option>
        <option>drawers</option>
        <option>dresser</option>
        <option>dressing table</option>
        <option>filing cabinet</option>
        <option>mirror</option>
        <option>sideboard</option>
        <option>table</option>
        <option>tv unit</option>
        <option>wardrobe</option>
        <option>winerack</option>
        <option>workstation</option>
      </select>

Which did work but that's obviously going to cause problems if they decide to start selling chests or something!  So I figured it would be best to pull the info from the database fields (although not necessary the records themselves).

So what I want my drop downs to do is display the value options I've specified for each of the six fields so the staff can simply select one when adding a new item to the database.  The code I've used above doesn't show anything in the drop-down.

I hope this makes sense and many thanks for your help so far!

MurraySummers
MurraySummers
Inspiring
October 10, 2011

Are the other select tags also loading their data from a SET field?

I was not familiar with the SET data type.  But I see that it can be troublesome, especially when you are adding or deleting new values to the field.  Normally I would do this with a separate table called "type" which would contain 19 records, one each for each of the values you show above.  The page would then load two recordsets, one for the rest of the items and one for the type records.  Then I would have a loop to load the option tags for that select field -

<select name="type" id="type" title="Room Type"> (note the missing double quote at the end of this line in your code)

<?php do { ?>

<option value="<?php echo $row_rsType['type']; ?>"><?php echo $row_rsType['type']; ?></option>

<?php } while ($row_rsType = mysql_fetch_assoc($rsType));  ?>

</select>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices