Updating a table from a drop down

[ggrewe]

I am having a problem updating a table with the correct value from a drop down combo. The code below lists the correct value associated with the AccessGroupName, but the value for the AccessGroupID (which is an 'int') is not correctly passed to the SQL update string. The update string acutally works and updates everything, except the value for UserAccess. If someone could tell what I am missing here, I would appreciate it greatly. Should I just remove the strcomp?

Select Code:

            <select name="AccessGroup" id="AccessGroup">
              <?php
do {  
?>
              <option value="<?php echo $row_AccessGroups['AccessGroupID'];?>"
				<?php if (!(strcmp($row_AccessGroups['AccessGroupID'], $row_Users['UserAccess']))) {echo "SELECTED";} ?>>
				<?php echo $row_AccessGroups['AccessGroupName']?></option>
              <?php
} while ($row_AccessGroups = mysql_fetch_assoc($AccessGroups));
  $rows = mysql_num_rows($AccessGroups);
  if($rows > 0) {
      mysql_data_seek($AccessGroups, 0);
	  $row_AccessGroups = mysql_fetch_assoc($AccessGroups);
  }
?>

Update Code:

  $updateSQL = sprintf("UPDATE users SET UserAccess=%s, UserFirstName=%s, UserLastName=%s, UserName=%s, UserPassword=%s, UserEmail=%s WHERE UserID=%s",
                       GetSQLValueString($_POST['AccessGroup'], "int"),
                       GetSQLValueString($_POST['FirstName'], "text"),
                       GetSQLValueString($_POST['LastName'], "text"),
                       GetSQLValueString($_POST['User_Name'], "text"),
                       GetSQLValueString($_POST['User_Password'], "text"),
                       GetSQLValueString($_POST['EmailAddress'], "text"),
                       GetSQLValueString($_POST['UserID'], "int"));

Greg Grewe
West Chester, Ohio

 

[13sio]

Is the field UserAccess integer or string? For your code

$updateSQL = sprintf("UPDATE users SET UserAccess=[b]%s[/b], UserFirstName=%s, UserLastName=%s, UserName=%s, UserPassword=%s, UserEmail=%s WHERE UserID=%s",
GetSQLValueString($_POST['AccessGroup'], "[b]int[/b]"),
//...

Of course, i don't know the detail of GetSQLValueString(), just raise the question.

 

[ggrewe]

In the database, UserAccess is an Integer. The GetSQLValueString() method takes the value passed to it and converts it to the required format, in this case "int" is an integer. It appears that the drop down does not seem to return the value associated with the GroupName; ie_

Administrator = 1
Manager = 2
etc.





Greg Grewe
West Chester, Ohio

 

[13sio]

if GetSQLValueString($_POST['AccessGroup'], "int") returns a integer, your update code should be

$updateSQL = sprintf("UPDATE users SET UserAccess=[b]%d[/b], UserFirstName=%s,//...

 

[ggrewe]

Ok, thanks. I will try that and get back with you!



Greg Grewe
West Chester, Ohio

 

[ggrewe]

That helped alot, at least the value is now an integer instead of a NULL, but the value is always zero, regardless what I select from the drop down.



Greg Grewe
West Chester, Ohio

 

[13sio]

Modify the select box into

<select name="AccessGroup" id="AccessGroup" [b]onchange="alert(this.value);"[/b]>

once the select box is changed, its value will be prompted. If the problem is not due to this point, try to consider GetSQLValueString() method.

 

[ggrewe]

Thanks for this tip. When I change values, the alert displays no data, so it appears that the code below may be a problem;

              <option value="<?php echo $row_AccessGroups['AccessGroupID'];?>"
				<?php if (!(strcmp($row_AccessGroups['AccessGroupID'], 4))) {echo "SELECTED";} ?>
				>
				<?php echo $row_AccessGroups['AccessGroupName']?></option>

Greg Grewe
West Chester, Ohio

 

[13sio]

The problem seems due to $row_AccessGroups['AccessGroupID']

 

[ggrewe]

I agree and confused. The table ("Access Groups") only has two fields; AccessGroupId and AccessGroupName. The code to poplulate $row_AccessGroups is:

$query_AccessGroups = "SELECT * FROM accessgroups";
$AccessGroups = mysql_query($query_AccessGroups, $connIVC) or die(mysql_error());
$row_AccessGroups = mysql_fetch_assoc($AccessGroups);
$totalRows_AccessGroups = mysql_num_rows($AccessGroups);

and in the option value

} while ($row_AccessGroups = mysql_fetch_assoc($AccessGroups));
  $rows = mysql_num_rows($AccessGroups);
  if($rows > 0) {
      mysql_data_seek($AccessGroups, 0);
      $row_AccessGroups = mysql_fetch_assoc($AccessGroups);
  }
?>

Greg Grewe
West Chester, Ohio

 

[13sio]

How about the output

echo $row_AccessGroups["AccessGroupID"];

in do-while loop.