CODEWITHSUNDEEP
phpmysqlselect
Dodinas
Dodinas

Reputation: 6805

MySQL Dynamic Select Box

I have two tables in MySQL, (and using PHP) which look similar to below:

------------------------       
|                      |
|        sizes         |   <--- This table is what populates the Select Box
|                      |
------------------------
|    id     |   name   |
|           |          |           
|     1     |   Small  |
|     2     |   Medium |
|     3     |   Large  |
------------------------

----------------------------------------       
|                                      |
|            user_entries              | <--- This table relates to above by "size_id"
|                                      |
----------------------------------------
|    id     |   user_id  |   size_id   |
|           |            |             |
|     1     |     25     |      2      |
|     2     |     12     |      3      |
|     3     |     15     |      3      |
----------------------------------------

My question is: How can I write my SELECT statement to both populate a select box with all the size options (Small, Med, Large) AND pre-select the option based on the user's preference.

For example, for the user with user_id=25, the HTML would look like:

<select>
   <option>Small</option>
   <option selected="selected">Medium</option>
   <option>Large</option>
</select>

Upvotes: 1

Views: 463

Answers (2)

sel
sel

Reputation: 4957

// get size id from user entries table based on user id
$result2= mysql_query("SELECT size_id from user_entries where user_id='$user_id'");
$row2 = mysql_fetch_assoc($result2);
$sel_size_id =$row2['size_id'];

//dropdownlist query
$query="SELECT name,id FROM sizes";
/* You can add order by clause to the sql statement if the names are to be displayed in alphabetical order */
$result = mysql_query ($query);

//populate the dropdownlist
while($row = mysql_fetch_assoc($result)) { 
      //determine if the size id is selected
      $selected = ($row['size_id '] == $sel_size_id ) ? ' selected="selected" ' : NULL;
      echo  '<option value="' . $row['size_id']. '"' . $selected . '>'.$row['name'].'</option>\n';
}

Upvotes: 1

Robbie
Robbie

Reputation: 17710

SQL is:

SELECT s.id, s.name, ue.id as UserSelected
  FROM sizes s
    LEFT JOIN user_entries ue ON s.id=ue.size_id AND ue.user_id=XXX
  ORDER BY s.id

(Set the order as you want)

The, when you run through the results, if "UserSelected" is not NULL (i.e. has a value) then you have the selected entry.

SQL tips: try to keep column names the same between tables, so that "size_id" is the name of the id field in the size table as well as the foreign key in user_entries table; this way, you wouldn't have two "id" columns in this query that would cause problems.

You also probably don't need the "id" column in the user_entries table as you'll always search/update based on user_id? Just one less column/index to handle if you don't need it.

Upvotes: 2

Related Questions