MySQL form dropdown box problem

[elite311]

Hello,

 

I have been trying to figure this for a while now and reading the tutorials are not helping, I think I'm a little over my head on this one and was hoping someone could help me out with this issue.

 

I am making a User Edit page and would like to have the access level part of the form show the users access current access level thats set in the database when the page loads, and if it needs to be changed you can press the dropdown box and select a new access level. I can't figure out how to show the current access level as default and populate the drop down box with the other access levels in my table.

 

My Tables look like this

 

Users table (users):

 

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

| id   |   username   |   password   |   flag   |   realfirst   |   reallast   |   dept   |

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

 

  1          loderd                                  9           test             guy        Service

 

 

Auth Table (auth):

 

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

|   id   |   auth_level   |   descrip   |

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

 

    1              1          Service Tech

    2              2           Office Staff

    3              9          Super Admin

 

My SQL Query looks like this

$users = $db->fetch_all_array("SELECT users.id, users.username, users.password, users.realfirst, users.reallast, users.dept, users.flag, auth.auth_level, auth.descrip, auth.id 
							   FROM users LEFT JOIN auth
							   ON users.flag = auth.auth_level
							   WHERE users.id = '".$_GET['id']."'");

I can't seem to figure out how I can do this for the Access Level dropdown box.

                <tr>
                    <td width="19%" align="right" valign="top">Access Level :</td>
                    <td width="1%" align="left"> </td>
                    <td width="80%" align="left" valign="top">
						  <?php 
						  echo "<select name='flag' id='flag'>";
						  foreach ($users as $row){
						  if($row[auth_level]==$row[auth_level]){
						  echo "<option value=$row[auth_level] selected>$row[auth_level] - $row[descrip]</option>"; 
						  }else{
						  echo "<option value=$row[auth_level]>$row[auth_level] - $row[descrip]</option>"; 
						  }
						  }
						  echo "</select>";                          ?>
                    </td>
				</tr>

Any help would be greatly appreciated

 

[Barand]

Easiest is to use a function to which you pass the users current level

function auth_level_options ($db, $user_level)
{
    $sql = "SELECT auth_level
            , descrip
            FROM Auth
            ORDER BY auth_level";
    $res = $db->query($sql);
    $opts = "<select name='flag>"
    while (list($level,$desc) = $res->fetch_row()) {
        $sel = $level == $user_level ? 'selected="selected"' : '';
        $opts .= "<option value='$level' $sel>$desc</option>";
    }
    $opts .= "</select>\n";
    return $opts;
}

then where you want the dropdown

echo auth_level_options($dbconn, $current_user_level);

[elite311]

Thanks so much for the help!

 

I'm getting the following error on this line though, am I right that the error is caused because the $desc variable hasn't been defined?

 

Fatal error: Call to a member function fetch_row() on a non-object

while (list($level,$desc) = $res->fetch_row()) {

Sorry still trying to learn php and mysql.

[Barand]

That means the query has failed.

Check db connection and also field and table names in the query.

 

Changing

$res = $db->query($sql);

to

$res = $db->query($sql) or die($db->error);

should help to isolate the error

[elite311]

Thank you for all the help so far, I have been reading tutorials and manuals all afternoon to try and figure out I'm doing wrong. I am really stuck on this one.

 

I added in the

$res = $db->query($sql) or die($db->error);

Like you had suggested but I still get an error, looks basically the same as before

 

Fatal error: Call to a member function query() on a non-object

 

This is what my code looks like, am I doing something wrong with me $db connection to the database? everything else is working but just not the drop down box, on the drop down box line is where I'm getting the fatal error showing up.

<?php

session_start();

if($_SESSION['loggedin'] == TRUE)
if($_SESSION['auth_lvl'] >  { //loggedin already

}else{ //not logged in yet

header("Location: index.php");

}

include('admin/includes/config.php');
include('admin/includes/database.class.php');
include('admin/includes/functions.php');

// Configure new database object
$db = new Database($db_host, $db_username, $db_password, $db_database, $db_table_prefix);
$db -> connect();

$menuresult = $db->fetch_all_array("SELECT * FROM menu ORDER BY disporder ASC");

$userinforesult = $db->fetch_all_array("SELECT realfirst, reallast FROM users WHERE username  = '".$_GET['id']."' LIMIT 1");

$users = $db->fetch_all_array("SELECT users.id, users.username, users.password, users.realfirst, users.reallast, users.dept, users.flag, auth.auth_level, auth.descrip, auth.id 
							   FROM users LEFT JOIN auth
							   ON users.flag = auth.auth_level
							   WHERE users.id = '".$_GET['id']."'");
							   
function auth_level_options ($db, $user_level)
{
    $sql = "SELECT auth_level, descrip
            FROM auth
            ORDER BY auth_level";
    $res = $db->query($sql) or die($db->error);
    $opts = "<select name='flag>";
    while (list($level,$desc) = $res->fetch_row()) {
        $sel = $level == $user_level ? 'selected="selected"' : '';
        $opts .= "<option value='$level' $sel>$desc</option>";
    }
    $opts .= "</select>\n";
    return $opts;
}

if(isset($_POST['updateit']))
{
	// Protect against injection
	$username = mysql_real_escape_string($_POST[username]);
	$password = md5(mysql_real_escape_string($_POST['password']));
	$flag = mysql_real_escape_string($_POST[flag]);
	$realfirst = mysql_real_escape_string($_POST[realfirst]);
	$reallast = mysql_real_escape_string($_POST[reallast]);
	$dept = mysql_real_escape_string($_POST[dept]);
	
	$db->query("UPDATE users SET username = '$username', password = '$password', flag = '$flag', realfirst = '$realfirst', reallast = '$reallast', dept = '$dept' WHERE id = '".$_POST['id']."'");
	header("Location: admin.php?updated=1");
	exit();
}

?>

And I have added this to the form where I want the dropdown box

                <tr>
                    <td width="19%" align="right" valign="top">Access Level :</td>
                    <td width="1%" align="left"> </td>
                    <td width="80%" align="left" valign="top">
						  <?php echo auth_level_options($dbconn, $current_user_level);?>
                    </td>
				</tr>