Update query not working in PHP and Mysql

Question

I have made an update page which fetches record from a table, shows all the details on html form where user can change/Edit the values and submit. Next page fetches those values using $_POST and Update the table.

$new_id = $_POST['c_id'];
$new_name = $_POST['c_name'];
$table_name = "tcompany";

$sqlStatement = "UPDATE $table_name SET 'name'=$new_name WHERE 'id'= $new_id";
if($result_1 = mysql_query($sqlStatement)) 
{
    header('Location: edit_company.php');
}
else {
        echo "". mysql_error();
}

I am getting error : 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 ''name'=HARDWARE Exporters WHERE 'id'= 69' at line 1

I am not considering security issues related to injection. Using this code for personal use.

Logan Wayne · Accepted Answer · 2015-08-05 08:29:39Z

Don't use apostrophe (') for column names and use it instead to your variables.

$sqlStatement = "UPDATE ".$table_name." SET name='$new_name' WHERE id='$new_id'";

You should also sanitize the values you are binding to your query. Use *_real_escape_string.

$new_id = mysql_real_escape_string($_POST["c_id"]);

And mysql_* API is already deprecated and you should consider using mysqli prepared statement instead.

If you want an example of prepared statement, using the code you have given, you can refer below. No need to sanitize each values before using them to your query.

/* ESTABLISH FIRST YOUR CONNECTION */
$con = new mysqli("YourHost","Username","Password","Database"); /* REPLACE NECESSARY DATA */

if($stmt = $con->prepare("UPDATE ? SET name = ? WHERE id = ?")){ /* CHECK IF STATEMENT IS TRUE */
  $stmt->bind_param("ssi",$table_name,$_POST["c_name"],$_POST["c_id"]); /* BIND VALUES TO YOUR QUERY */
  $stmt->execute(); /* EXECUTE THE QUERY */
  $stmt->close();
} /* END OF PREPARED STATEMENT */

René Hoffmann · Accepted Answer · 2015-08-05 08:25:17Z

0

The problem is that variable $new_name contains spaces. So you should quote the use of variables in the statement, like this:

$sqlStatement = "UPDATE $table_name SET 'name'='$new_name' WHERE 'id'= '$new_id'";

answered Aug 5, 2015 at 8:25

René Hoffmann

2,8252 gold badges23 silver badges45 bronze badges

Collectives™ on Stack Overflow

Update query not working in PHP and Mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related