[SOLVED] Error updating table

[skyer2000]

I've never had this problem before and am completely stumped as to what to do. I'm trying to make a form based table update, where the form auto-fills itself (which it does fine), then when submit is pressed it updates the corresponding row.

 

When submit is pressed, it says that the query is successful, but does not update the information in the database. I'm totally stumped, here is the database information:

 

Tablename: users

Columns: id, username, fullname, pass, email, group

 

Here is the code->

 

if ($submit) {

$username = $_POST['username'];
$fullname = $_POST['fullname'];
$pass = $_POST['pass'];
$email = $_POST['email'];
$group = $_POST['group'];

$sql = "UPDATE userlist SET username='$username',fullname='$fullname',pass='$pass',email='$email',group='$group' WHERE username='chad'";

$result = mysql_query($sql);

echo "Record updated/edited!<p>";

}

$sql = "SELECT * FROM userlist WHERE username='chad'";

$result = mysql_query($sql);

$user_info = mysql_fetch_array($result);

?>


<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<input type="hidden" name="id" value="<?php echo $user_info['id']; ?>">
<p>
UserName:<br />
<input type="text" name="username" size="40" maxlength="40" value="<?php echo $user_info['username']; ?>" />
</p>
<p>
Full Name:<br />
<input type="text" name="fullname" size="40" maxlength="40" value="<?php echo $user_info['fullname']; ?>" />
</p>
<p>
Password:<br />
<input type="text" name="pass" size="40" maxlength="40" value="<?php echo $user_info['pass']; ?>" />
</p>
<p>
Email:<br />
<input type="text" name="email" size="40" maxlength="40" value="<?php echo $user_info['email']; ?>" />
</p>
<p>
Group:<br />
<input type="text" name="group" size="40" maxlength="40" value="<?php echo $user_info['group']; ?>" />
</p>
<p>
<input type="submit" name="submit" value="Submit!" />
</p>
</form>

[paul2463]

looking at you code it WILL say it has updated the row even if the query has thrown an error, the reason for this is that the only check you have made is to say "if the form has been submiited" here is the query, run the query, echo out "done that" without checking for any errors

 

try typing this in your page

 

<?php
if ($submit) {

$username = $_POST['username'];
$fullname = $_POST['fullname'];
$pass = $_POST['pass'];
$email = $_POST['email'];
$group = $_POST['group'];

$sql = "UPDATE userlist SET username='$username',fullname='$fullname',pass='$pass',email='$email',group='$group' WHERE username='chad'";

$result = mysql_query($sql) or die ('Error in query: $sql. ' . mysql_error()); //throws an error if it does not understand your query

printf("Records Updated: %d\n <p>", mysql_affected_rows()); //tells you how many rows have been affected by your query

}
?>

 

[skyer2000]

Ok I put in that new code and this is the error I get, which I still do not understand->

 

Error in query: $sql. 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='Admin' WHERE username='chad'' at line 1

[paul2463]

syntactically there is nothing wrong with this query

<?php
$sql = "UPDATE userlist SET username='$username',fullname='$fullname',pass='$pass',email='$email',group='$group' WHERE username='chad'";
?>

 

so there must be something else wrong with it, instead of the $sql = part try echoing out the query and see if it all makes sense

 

<?php
if ($submit) {

$username = $_POST['username'];
$fullname = $_POST['fullname'];
$pass = $_POST['pass'];
$email = $_POST['email'];
$group = $_POST['group'];

echo "UPDATE userlist SET username=$username,fullname=$fullname,pass=$pass,email=$email,group=$group WHERE username=chad";

//$result = mysql_query($sql) or die ('Error in query: $sql. ' . mysql_error()); //throws an error if it does not understand your query

//printf("Records Updated: %d\n <p>", mysql_affected_rows()); //tells you how many rows have been affected by your query

}
?>

[skyer2000]

This is what it echo'd

 

 UPDATE userlist SET username = chad,
fullname = Chuck Hunter,
pass = 73cb18490568c250e98d12dd433c2124,
email = [email protected] ,
GROUP = Admin WHERE username = chad 

 

Turns out it transformed "group" into the command "GROUP". I went ahead and changed it to permission and it works fine. Thanks for the help!

[paul2463]

bloody hell I cant believe I missed that - slap - slap - slap thats me hitting my own head

 

group is a reserved word in mysql so if you want to use it just use backticks on the word GROUP, this makes PHP/MySQL treat it as a string and not a command

 

<?php
$sql = "UPDATE userlist SET username='$username',fullname='$fullname',pass='$pass',email='$email',`group`='$group' WHERE username='chad'";
?>