populating a MUTIPLE SELECT form element using data from a mysql table

saqib

Hi All,
Is there a easy way to prepopulate a MULTIPLE SELECT form element, using data from mySQL table?

Thanks
Saqib Ali

eoghain

Easy? Probably not, but a standard way is this:

// Get a result set
$multi_select = "<SELECT id=\"myselect\">";

// Loop through result set
while ($row = mysql_fetch_array($result)) {
   $multi_select .= "<OPTION value = " . $row["id"] . ">" . $row["name"] . "</OPTION>";
}

$multi_select .= "</SELECT>";
echo $multi_select;

That is the basic structure.
1. get your result set.
2. setup your select HTML
3. create an option line for each result
4. close your select HTML
5. display

BLottman

Doesn't your code assume that the entire result set has been stored using all of the available values in the option select. Lets say there are ten options and only five ever get stored in the table.

Your code would only produce the five stored values - without the remaining option values of the entire set of available options. The select that's displayed will not produce the entire set only the stored set of saved options.

I'm searching for a way to display a form containing various selects that contain not only the options stored but the remaining set of options that haven't been selected as well. This is necessary if the user wishes to choose an option not already stored in the result set.

Am I off base here?

Weedpacket

Originally posted by BLottman
Am I off base here?

Possibly - if you've got ten options why don't you store all ten in the database? Why store only five and have to get the other five from somewhere else? After all, you're wanting to list the options the user can select from, not just the options they've already selected.

You might have one or two special options that don't correspond to a real option (a blank, say) - in which case you'd just put that in by hand in the appropriate place before or after the loop.

BLottman

I'm not sure I understand the suggestion of storing all values in the database. Are you noting that I should have a separate table in the database that contains all available values for the one select query and then use another select query for the table that contains the user's stored values. Then combine the two to form the entire set?

Or is there a better way?

I've read elsewhere in the forum to query the tables once and put the values in an array. This sounds plausible.

eoghain

I, and probably Weedpack also, was assuming that you were storing your values in a table that was being used as a lookup.

Here is an example of what I mean. To build a US address you need these things:
1. An address line
2. An optional second address line (suite #, apt #, etc...)
3. A city
4. A state
5. A zip code

Now most of this needs to be free form entry so that you can get the widest range of answers. But the State field doesn't have to be free form (as I'm sure you've seen on the web). So to code for the state I'd build a table that looks something like this:

States

state_id int not null
state_code char(2) not null
state_name varchar(50)

Now I'd fill this table with things like:
1, AZ, Arizona
2, CA, California
etc...

Finally I'd use the data in this table to build my select as I showed in my first reply, and then the user could use this to select the state they live in instead of typing it in. This is the standard way of using a select that is driven from a database.

Now when the user has completed entering the address it is stored in a second table that only stores the referance to the state, by using the state_id, and I've saved the space that would be used by the user typing in California hundreds of times. And I know that California will always be spelled the same and if I needed to change it for any reason I could change it for all users with a single database change. This is a standard way of building a relational database.

BLottman

eoghain:

Thanks for clarifying. I know what I need to do now.

Appreciate it much.

Bill. 🙂