Complex dynamic dropdown menu

[serge12]

I have 2 tables: "agent" and "securitylevel" The primary key of "securitylevel" is represented as a foreign key in the "agent" table (each agent is assigned a security level).

here is how the tables look like:

CREATE TABLE agents (
ag_id int(4) NOT NULL auto_increment,
username varchar(12) binary NOT NULL default '',
userpassword varchar(20) binary NOT NULL default '',
fullname varchar(50) NOT NULL default '',
seclevel_id smallint(6) NOT NULL default '0',
PRIMARY KEY (ag_id),
KEY username (username)
) TYPE=MyISAM;

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

#

CREATE TABLE securitylevel (
seclevel_id int(11) NOT NULL default '0',
seclevel_name varchar(15) NOT NULL default '',
seclevel_desc varchar(50) NOT NULL default '',
PRIMARY KEY (secle_id)
) TYPE=MyISAM;

I am creating an "edit" page that would display all the agents information including a dropdown menu with his/her corresponding security level as the "selected" option.

I am having trouble figuring out how to query MYSQL to display all the security levels in the dropdown menu and at the same time look on the "agent" table to see which securityID is the agent assigned to, in order to display it as the SELECTED option. Can someone suggest a solution for this??

I will truly appreciete it.
Thanks

[bastien]

1. call the agent data
2. call the sec level data

show the results

[gigowiz]

...select the agent data
...select the securitylevel data

Here's the psuedo code. While you're looping through the securitylevel seclevel_id:

if (agent.seclevel_id == securitylevel.seclevel_id) {
printf("<option value=\"%s\" selected>%s\n", securitylevel.seclevel_id,
securitylevel.seclevel_id);
}
else {
printf("<option value=\"%s\">%s\n", securitylevel.seclevel_id,
securitylevel.seclevel_id);
}

Again. This IS NOT syntactically correct. The basic idea is that you have the agent's security level already. Then when you're creating the drop down list of the security levels you compare each one with the agent's. When they match you add the "selected" part of the option tag.

Hank

[irishprogrammin]

This should give you just about everything you need, save removing a few things such as the $optionID which you may not need. But it is a pretty complete layout of how to do what you are looking for. Just replace the table / field names throughout and you should be up and runnin.

print "<SELECT name='createdby_memberid' value=$createdby_member>\n";
$sql = "SELECT userid FROM project_members where projectid=$projectid";
$query = mysql_query($sql) or die("Failed Querying Project Members. Error: " . mysql_error());
$num_rows = mysql_num_rows($query);
while ( $row = mysql_fetch_array($query) )
{
$qryuserid = $row["userid"];
if ( $qryuserid )
{
$sql2 = "SELECT id, username FROM users where id=$qryuserid";
$query2 = mysql_query($sql2) or die("Failed Querying Users. Error: " . mysql_error());
while ( $row2 = mysql_fetch_array($query2) )
{
$optionid = $row2["id"];
$optionname = $row2["username"];
if ( $optionid == $createbyid )
{
print "<OPTION selected>\n";
}
else
{
print "<OPTION>\n";
}
print "$optionname -[$optionid]-";
print "</OPTION>\n";
}
}
}
print "</SELECT>\n";