EDIT table with SELECT MENU | Lookup Table

[dottomm]

hello and thank you in advance to anyone who can help me with this problem.
I've scoured the internetz for a while trying to find a solution to what must be a common question,
but I can't find anything that relates to what I am trying to do.

I have an EDIT form so users can edit their profile. One of the fields is a select menu populated with
the contents of a second table.

Example. A users wants to change their color preference.

USERS

user_id \ name \ color_id
--------------------------
1 | Bob | 1
2 | Steve | 2
3 | Roger | 3
4 | Mike | 4
--------------------------

COLORS

color_id \ color
----------------
1 | Red
2 | Yellow
3 | Green
4 | White
----------------

I would like to

select user_id, name, color_id, color 
from USERS inner join colors on users.color_id=colors.color_id where user_id=1
and select * from colors 
so that the select menu would display the users choice by default 
then populate the select menu with the rest of the available options.

Is this even possible? I'm starting to get desperate looking for a solution.
I know how to do this with a embeddded select menu, just not one with a second table. In real life the 'color' table is approx 400 rows.

Again, any help with this would be greatly appreciated!

[diafol]

OK, so you've got a rough handle on what you want to do and have given it a bash. I'll give you a little help:

1. Get the user details from the DB and the colours details (2 separate DB queries)

(I suggest that the user details are kept in a session variable - to avoid calling the DB for the same data all the time). Anyway - I'll carry on without session vars.

2. The color value from the users table will be used to identify the user's current selection in the dropdown.

In the select 'while loop'

if($usercolor == $row['color_id']){
   $sel =' selected = "selected"'
}else{
  $sel = '';
}
$options .= "<option value=\"{$row['color_id']}\"{$sel}>{$row['color']}</option>";

The code above will select the user's current colour in your dropdown.

3. Echo the $options variable within the tags.

Hey presto.

If you run into a wall, come back. I've purposely only given a skeletal job.

[dottomm]

Thanks for the reply Ardav. I am still having some problems. I am only getting the users selection back and not all the color options.

If you have time, could you look at my example I set up at:
http://dottomm.com/edit_help/color_edit.php

The source code is available there as .txt.

thanks again!

[diafol]

Hey nice look - I like it. OK, looking at the edit page, you've tried to use the join query to populate the select field. Nooooo.

Your $_GET var from the u_id value will give you the user id. You've got that.

Now get the user details (all data) for this user id (NO JOIN).

Extract the $color_id (e.g. from $row['color_id'] or whatever you're using).

Next run a new query for all the colours:

<?php
...
$r = mysql_query("SELECT color_id, color FROM colors");
$options = "";
while($d = mysql_fetch_array($r)){
  if($color_id == $d['color_id']){
     $sel =' selected = "selected"'
  }else{
    $sel = '';
  }
  $options .= "<option value=\"{$d['color_id']}\"{$sel}>{$d['color']}</option>";
}

//this can all be done before the doctype declaration
...
?>

<select id="colors">
   <?php echo $options;?>
</select>

[dottomm]

Oh Wow! It's working. Thank you so much. I understand why it needed to different queries, but I had no idea how to write it. It's beautiful. Nice job!

Marking this as solved and a whole heap of reputation to be stowed upon you. Thanks!

[diafol]

Great! Thanks for the rep. Anyway, perhaps you should change the name of your pages as you've pasted an url to them in one of your posts, so now anyone can mess with your data. It could save you a lot of head scratching!

[dottomm]

Yes. Good Idea. Thanks!