Creating a Drop Down Menu in PHP from MySQL?

Question

I'm sort of experienced with PHP and MySQL so I kind of get the hang of things, however I'm sort of trying to get something that may be over my level (not really sure the difficulty level on this). Basically, I am looking to create 2 drop down menus to remove access from a user within the MySQL table. So the first drop down menu will be for the list of users I have, and the the second drop down menu will be for the access they have. I'd like for it to when I select a user, the second drop down menu only shows the options that the user has access to such as;

    <form name="form" method="post" action="access.php">
    <select name='user'>
    <?php 
require "sqlconfig.php"; // My SQL Configuration
$conn=mysql_connect($host, $user, $pass); // The Connection
$selectdb=mysql_select_db("$db"); // The Database
    $userlist1 = mysql_query("SELECT `Username` FROM `accounts` ORDER BY Username ASC");
        while ($userlist = mysql_fetch_array($userlist1)) {
        print "<option value='username'>$userlist[Username]</option>";
        }
    ?>
    </select>
<select name='task'>
<option value="notask" <?php if($_GET['task'] == notask) { echo "selected"; } ?>>Select a Task</option>
<option value="admin" <?php if($_GET['task'] == admin) { echo "selected"; } ?>>Administrator</option>
<option value="user" <?php if($_GET['task'] == user) { echo "selected"; } ?>>user</option>
<option value="guest" <?php if($_GET['task'] == guest) { echo "selected"; } ?>>Guest</option>
</select>
<input type="submit" value="Remove" />
    </form>

I already have the permissions in a table, each value is as integer 0 for false, and 1 for true. So basically when the user is selected from the first menu, I want the second menu to refresh, and only show the values they have that are = to 1 (which means they have it), and then of course, when the form is submitted, it will change whichever task was selected from 1 to 0, meaning it removes itself.

Basically, when the user is selected (ex; Joe), the second menu will refresh with the tasks that Joe has (ex; Admin, User). Once I select a task then click remove, the script should post the username menu, and get whatever task that was selected before submit, then run the script to UPDATE the users field of like 'Admin' from 1(true) to 0(false)

Can anyone help me set it up with code, or at least give me the direction to set it up myself?

Answer 1

Create a div and give it a id="result" and put an empty select tag in that div.

Then using jQuery + Ajax, get the second dropdown using:

$('select[name="user"]').change(function(){
   var user = $(this).val();

   $.ajax({
      type:'post',
      url:'getSecondDropDown.php',
      data:'user='+user,
      success:function(result){
         $('div#result').html(result);
      }
   });

});

In your 'getSecondDropDown.php' file, include this:

<select name="task">
<?php
$user = $_POST['user'];
$q = mysql_query("SELECT task FROM tablename WHERE user=".$user.") or die();
while($r = mysql_fetch_array($q))
{ ?>
  <option value="<?php echo $r['task]; ?>"><?php echo $r['task]; ?></option>

<?php
}
?>
</select>

Answer 2

You'll need to use JavaScript to do it. Send an AJAX request to a page in the server which will fetch the details of a user for you, then return the results to JavaScript.

Once you have the data in your JavaScript, use DOM manipulation to populate the second <select> with the results.