I am building a ticketing system and am currently working on the profile page. I have created drop down menu's so admins can select assign users to a specific group. My page is written in php and when submitting my form everything gets updated except the drop down menu's. I have checked and my variables are being assigned to the correct value depending on which group I choose from the menu.
When I saw this was not working I decided to try inputting code to update the group table directly into my database from phpmyadmin. First i tested with another field in the same table to make sure i had the correct syntax then using the syntax that worked before i changed the field to the group field and got an error.
The group field is assigned as tinyint and limited to 3 chars. I have also tried int instead of tinyint. It's also set to not null and has no other extra attributes. I have tried having it setup as a prim key as well but that made no difference.
As I said before when running the script I get no errors, the group just doesn't get updated in the database even though the variables in the update query are getting a number value assigned. I am stumped as to what could be the problem. Any help would be appreciated.
This is the sql code used.
UPDATE profile SET group = '2' WHERE uid = '2'
This is the error I recieved in phpmyadmin.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group = '2' WHERE uid = '2'' at line 1
This is the php code section with the problem
$sqle = "SELECT * FROM `profile` WHERE uid='$_SESSION[uid]'";
$querye = mysql_query($sqle, $conn) or die(mysql_error());
$rowe = mysql_num_rows($querye);
$curgrp = $rowe[group];
$qgedit = "SELECT gid, gname FROM `group` WHERE gtypid>'$_SESSION[gtypid]' and gtypid!='$curgrp'";
$gedit = mysql_query($qgedit);
$gerow = mysql_num_rows($gedit);
// Query groups for slected user
$gsql = "SELECT group.gname, group.gid, profile.uid, profile.group FROM `group`, `profile` WHERE profile.uid='$_SESSION[uid]' and profile.group=group.gid";
$gquery = mysql_query($gsql, $conn) or die(mysql_error());
$grparray = mysql_fetch_array($gquery);
$g2sql = "SELECT group.gname, group.gid, profile.uid, profile.group2 FROM `group`, `profile` WHERE profile.uid='$_SESSION[uid]' and profile.group2=group.gid";
$g2query = mysql_query($g2sql, $conn) or die(mysql_error());
$grp2array = mysql_fetch_array($g2query);
// End user query
/* Set options for groups*/
$options="";
while ($gerow=mysql_fetch_array($gedit)) {
$gid=$gerow["gid"];
$gname=$gerow["gname"];
$options.="<option value=\"$gid\">".$gname."</option>";
}
// End of options
if ( !isset($_POST['submit'])) {
while ($rowe = mysql_fetch_array($querye)) {
if($rowe['disabled'] == "1")
{
$checkdisabled = 'checked="checked" ';
}
else
{
$checkdisabled = '';
}
echo '<form action="" method="post">';
echo '<div id="edtprof"><center><h2>Personal Information</h2><table width="28%" align="center" background="images/trimmtext.gif" style="border-style:groove;border-color:navy;margin-top:12px;">
<tr><td width="50% align="left"><h3>Username:</h3></td><td width="50% align="right"><h4><input type="text" name="username" value="'.$rowe['username'].'" readonly></h4>
<tr><td width="50% align="left"><h3>Disabled:</h3></td><td width="50% align="right"><h4><input type="checkbox" ' . $checkdisabled . 'name="disabled" value="disabled"></h4>';
echo '</td></tr><tr><td width="50% align="left"><h3>First Name:</h3></td><td width="50% align="right"><h4><input type="text" name="first_name" id="first_name" value="'.$rowe['first_name'].'"></h4>';
echo '</td></tr><tr><td width="50% align="left"><h3>Last Name:</h3></td><td width="50% align="right"><h4><input type="text" name="last_name" value="'.$rowe['last_name'].'"></h4>';
echo '</td><tr><tr><td width="50% align="left"><h3>Group:</h3></td><td width="50% align="right"><h4><select name="group"><option VALUE=0>Choose'. $options .'</SELECT> </h4>';
echo '</td><tr><tr><td width="50% align="left"><h3>Group 2:</h3></td><td width="50% align="right"><h4><select name="group2"><option VALUE=0>Choose'. $options .'</SELECT></h4>';
echo '</td></tr><tr><td width="50% algin="left"><h3>Home Phone:</h3></td><td width="50% align="right"><h4><input type="text" name="phone" value="'. $rowe['phone'].'"></h4></td></tr>';
echo '<tr><td width="50% algin="left"><h3>Alt Phone:</h3></td><td width="50% align="right"><h4><input type="text" name="altphone" value="'. $rowe['altphone'].'"></h4></td></tr>';
echo '<tr><td width="50% algin="left"><h3>Alt Phone Type:</h3></td><td width="50% align="right"><h4><input type="text" name="altphn_title" value="'. $rowe['altphn_title'].'"></h4></td></tr>';
echo '<tr><td width="50% algin="left"><h3>Email Address:</h3></td><td width="50% align="right"><h4><input type="text" name="email" value="'. $rowe['email'].'"></h4></td></tr></table>
<h2>Physical Address</h2><table width="28%" align="center" background="images/trimmtext.gif" style="border-style:groove;border-color:navy;margin-top:12px;">
<tr><td width="50% align="left"><h3>Address:</h3></td><td width="50% align="right"><h4><input type="text" name="address" value="'.$rowe['address'].'"></h4></td></tr>
<tr><td width="50% align="left"><h3>Address Line 2:</h3></td><td width="50% align="right"><h4><input type="text" name="address2" value="'.$rowe['address2'].'"></h4></td></tr>
<tr><td width="50% align="left"><h3>City:</h3></td><td width="50% align="right"><h4><input type="text" name="city" value="'.$rowe['city'].'"></h4></td></tr>
<tr><td width="50% align="left"><h3>State:</h3></td><td width="50% align="right"><h4><input type="text" name="state" value="'.$rowe['state'].'"></h4></td></tr>
<tr><td width="50% align="left"><h3>Zip Code:</h3></td><td width="50% align="right"><h4><input type="text" name="zip" value="'.$rowe['zip'].'">-<input type="text" name="zip4" value="'.$rowe['zip4'].'"></h4></td></tr>
<tr><td width="50% align="left"><h3>Cross Streets:</h3></td><td width="50% align="right"><h4><input type="text" name="cross_roads" value="'.$rowe['cross_roads'].'"></h4></td></tr>
<tr><td align="left"><input type="submit" name="submit" value="Save!"></td></tr></table></center></div></form>';
}
} else {
$_POST['group'] = stripslashes($_POST['group']);
$_POST['group2'] = stripslashes($_POST['group2']);
$first_name = $_POST[first_name];
$last_name = $_POST[last_name];
$group = $_POST[group];
$group2 = $_POST[group2];
$phone = $_POST[phone];
$altphone = $_POST[altphone];
$altphn_title = $_POST[altphn_title];
$email = $_POST[email];
$address = $_POST[address];
$address2 = $_POST[address2];
$city = $_POST[city];
$state = $_POST[state];
$zip = $_POST[zip];
$zip4 = $_POST[zip4];
$cross_roads = $_POST[cross_roads];
if (isset($_POST["disabled"]) && $_POST["disabled"] == "disabled") {
$disabled = 1;
}
else {
$disabled = 0;
}
mysql_query("update profile set disabled='$disabled', first_name='$first_name', last_name='$last_name', group='$group', group2='$group2', phone='$phone', altphone='$altphone', altphn_title='$altphn_title', email='$email', address='$address', address2='$address2',
city='$city', state='$state', zip='$zip', zip4='$zip4', cross_roads='$cross_roads' WHERE uid='$_SESSION[uid]'");
mysql_close();