Apparently no error in update query but the record is not updated

Question

this is my code for usersedit.php and the other one is for users-edit-action.php after updating its saying that the data is succfully updated but it doesnot change anything in mysql.. please help me figureout the problem, thankyou users-edit.php

<?php include("../includes/config.php"); ?>
<?php
if ($_SESSION["isadmin"])
{

$con=mysql_connect($dbserver,$dbusername,$dbpassword);
if (!$con) { die('Could not connect: ' . mysql_error()); }

mysql_select_db($dbname, $con);
$accountid=$_GET["id"];
$result = mysql_query("SELECT * FROM accounts WHERE (id='".$accountid."')");
while($row = mysql_fetch_array($result))
{
$id=$row['id'];
$firstname = $row['firstname'];
$lastname = $row['lastname'];
$email=$row['email'];
$type=$row['type'];
}
mysql_close($con);
?>
<!DOCTYPE HTML>
<html>
<head>
<title>Edit User</title>
<link rel="StyleSheet" href="../admin/css/style.css" type="text/css" media="screen">
</head>


<body>
<?php include("../admin/includes/header.php"); ?>
<?php include("../admin/includes/nav.php"); ?>
<?php include("../admin/includes/manage-users-aside.php"); ?>
<div id="maincontent">

<div id="breadcrumbs">
<a href="">Home</a> >
<a href="">Manage Users</a> >
<a href="">List Users</a> >
Edit User
</div>
<h2>Edit User</h2>

<form method="post" action="users-edit-action.php">
<input type="hidden" value="<?php echo $accountid; ?>" name="id" />
<label>Email/Username:</label><input type="text" name="email" value="<?php echo $email;     ?>" /><br /><br />
<label>Password:</label><input type="password" name="password" value="<?php echo     $password;?>" /><br /><br />
<label>First Name:</label><input type="text" name="firstname" value="<?php echo      $firstname; ?>" /><br /><br />
<label>Last Name:</label><input type="text" name="lastname" value="<?php echo $lastname; ?>" /><br /><br />
<label>Type:</label><br />
<input type="radio" name="type" value="S" <?php if ($type == 'S') echo     'checked="checked"'; ?> />Student<br />
<input type="radio" name="type" value="T" <?php if ($type == 'T') echo 'checked="checked"'; ?> /> Teacher<br />

<input type="submit" value="Edit" />
</form>
</div>
</body>
<?php include("../admin/includes/footer.php"); ?>
</html>
<?php

}    else
{
header("Location: ".$fullpath."login/unauthorized.php");
}
?>

this is users-edit-action.php

<?php include("../includes/config.php");?>
<?php

$id=$_POST["id"];
$firstname=$_POST["firstname"];
$lastname=$_POST["lastname"];
$email=$_POST["email"];
$type=$_POST["type"];


$con=mysql_connect($dbserver,$dbusername,$dbpassword);
if (!$con) { die('Could not connect: ' . mysql_error()); }


mysql_select_db($dbname, $con);
$query=("UPDATE accounts SET firstname='".$firstname."' , lastname='".$lastname."         ,password='".$password."' , email='".$email."' type='".$type."' WHERE (id='".$id."')");
$result = mysql_query($query);
echo "User has been updated Successfully!!";
mysql_close($con);
?>

please help me figure out and solve the problem

You are not escaping any of the variables you are constructing your query out of, do they contain quotes? Please dump and inspect $query. — Lucas Green, Aug 28 '12 at 03:17

John Woo · Accepted Answer · 2012-08-28T03:32:22.627

Escape column names that is a reserved keyword of MySQL

$query=("UPDATE accounts 
         SET firstname='" . $firstname . "'   ,
             lastname='" . $lastname . "      ,
              `password`='" . $password . "'  ,          
              email='" . $email . "'          ,            // <== forgot comma
              type='" . $type . "' WHERE (id='".$id."')
        ");

Password should be escaped.
You forgot to add comma between email and type.

Your current query is prone to SQL Injection. Use PDO or MYSQLI

Example of using PDO extension:

<?php

    $query = "UPDATE accounts 
               SET firstname = ?,
                   lastname = ?,
                   `PassWord` = ?,          
                   email = ?,          
                   type = ? 
            WHERE id = ?
        ";

    $stmt = $dbh->prepare($query);
    $stmt->bindParam(1, $firstname);
    $stmt->bindParam(2, $lastname);
    $stmt->bindParam(3, $password);
    $stmt->bindParam(4, $email);
    $stmt->bindParam(5, $type);
    $stmt->bindParam(6, $id);

    $stmt->execute();
    echo ($stmt) ? "Successful" : "Error Occured";

?>

this will allow you to insert records with single quotes.

Add result check for `mysql_query()` : `echo (!$result) ? "Error" : "User has been updated Successfully!!";` — deex, Aug 28 '12 at 03:29
this is how i canged the code as u said.but still it is not updating the records in database...i never had this problem before..please help — trouble creator, Aug 28 '12 at 07:28
$con=mysql_connect($dbserver,$dbusername,$dbpassword); if (!$con) { die('Could not connect: ' . mysql_error()); }mysql_select_db($dbname, $con); $result=("UPDATE accounts SET firstname='" . $firstname . "' , lastname='" . $lastname . " , 'password'='" . $password . "' , email='" . $email . "' , type='" . $type . "' WHERE (id='".$id."') "); echo "User has been updated Successfully!!"; mysql_close($con); ?> — trouble creator, Aug 28 '12 at 07:29

Apparently no error in update query but the record is not updated

1 Answers1