PHP is not updating the data in table (bindParam)

Question

Below is the attached query that I am trying to run through a POST request.

I have checked (echo) the values on submit, I am getting the latest values which means no issue on the form side. I am not getting any errors. echo after the query is also working but the record is not getting updated in the table. I have double check typos in the query it seems fine to me. Below is the query (I have replaced table name here)

$sql="UPDATE table_name SET name = :name, email = :email, category = :category, mobile = :mobileno, description = :description, website = :website, address = :address, city = :city, state = :state, zip = :zip, image = :image  WHERE  id = :editid";
$query = $dbh->prepare($sql);
$query-> bindParam(':name', $name, PDO::PARAM_STR);
$query-> bindParam(':email', $email, PDO::PARAM_STR);
$query-> bindParam(':category', $category, PDO::PARAM_STR);
$query-> bindParam(':mobileno', $mobileno, PDO::PARAM_STR);
$query-> bindParam(':description', $description, PDO::PARAM_STR);
$query-> bindParam(':website', $website, PDO::PARAM_STR);
$query-> bindParam(':address', $address, PDO::PARAM_STR);
$query-> bindParam(':city', $city, PDO::PARAM_STR);
$query-> bindParam(':state', $state, PDO::PARAM_STR);
$query-> bindParam(':zip', $zip, PDO::PARAM_STR);
$query-> bindParam(':image', $image, PDO::PARAM_STR);
$query-> bindParam(':editid', $editid, PDO::PARAM_STR);
$query->execute();

Is PDO set to [throw exceptions](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work) on errors and have you checked the web servers error log? Have you double checked that the value in `$editid` actually exists in the database? Is the table actually called `table_name`, or have you, for some strange reason, just changed that name here in your question? If you've changed it, is the rest a rewrite or a copy/paste? — M. Eriksson, Feb 12 '22 at 10:01
yes, the server is in debug mode, showing all other errors. `$editid` exists in the database I have double-checked that as well. — Ikram Khan Niazi, Feb 12 '22 at 10:02
Have you checked https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-S ? Name is a reserved keyword and should be put between backticks. — Marty McVry, Feb 12 '22 at 10:03
I have mentioned in the table (in the end) that I have changed the table name. — Ikram Khan Niazi, Feb 12 '22 at 10:06
_"yes, the server is in debug mode"_ - Sure, but have you specifically set PDO to throw exceptions, like the link I posted explains? If all values are correct, the SQL query correct, no errors are thrown (which it should on the reserved keywords as well), then I don't see how it couldn't update. There must be something else going on we can't see. — M. Eriksson, Feb 12 '22 at 10:06
The table name should be ok. It's the field `name` that should be escaped. Check my answer below. Hopefully it makes things clear. — Marty McVry, Feb 12 '22 at 10:11
Out of curiosity, how do check if any rows are being updated? Checking the database and seeing that the new values aren't inserted, or are you checking `rowCount()`? If it's the later, are you sure the values are different? Because if you update a table setting the same values, `rowCount()` will return 0 (no rows affected). We need more debugging info. Can you post the table schema, example data in the table and and of the data you're trying to update? Try and and example data to your database so we can see dumps of the actual values you're using. — M. Eriksson, Feb 12 '22 at 10:36

Marty McVry · Accepted Answer · 2022-02-12T10:59:52.313

In MySQL there are a few reserved keywords that you cannot use without escaping the fields in a query. The complete list can be found in the MySQL-documentation.

Try the query below and check whether or not the query processes correctly now.

$sql="UPDATE `table_name` SET `name` = :name, `email` = :email, `category` = :category, `mobile` = :mobileno, `description` = :description, `website` = :website, `address` = :address, `city` = :city, `state` = :state, `zip` = :zip, `image` = :image  WHERE `id` = :editid";
$query = $dbh->prepare($sql);
$query-> bindParam(':name', $name, PDO::PARAM_STR);
$query-> bindParam(':email', $email, PDO::PARAM_STR);
$query-> bindParam(':category', $category, PDO::PARAM_STR);
$query-> bindParam(':mobileno', $mobileno, PDO::PARAM_STR);
$query-> bindParam(':description', $description, PDO::PARAM_STR);
$query-> bindParam(':website', $website, PDO::PARAM_STR);
$query-> bindParam(':address', $address, PDO::PARAM_STR);
$query-> bindParam(':city', $city, PDO::PARAM_STR);
$query-> bindParam(':state', $state, PDO::PARAM_STR);
$query-> bindParam(':zip', $zip, PDO::PARAM_STR);
$query-> bindParam(':image', $image, PDO::PARAM_STR);
$query-> bindParam(':editid', $editid, PDO::PARAM_STR);
$query->execute();

EDIT: If all the inputs are strings, and the table fields are strings, this should work. Although I suspect that the fields that are being used aren't all strings... In that case the binding would fail and the query won't process. I will update this answer should you provide more information about the table you're trying to update and some sample data.

It works on my end... (Just tried it on a testing server). Are you sure all the inputs are strings in your code? — Marty McVry, Feb 12 '22 at 10:43
You have no idea whether it works or not. there could be no connection, no table, no database, no column, no match, no data. There could be collation error, invalid format error, constraint violation error. There could be ANYTHING. Such problems are solved not with code but with debugging. — Your Common Sense, Feb 12 '22 at 11:11
By the way, what particular field you meant to be a reserved keyword to justify your answer? — Your Common Sense, Feb 12 '22 at 11:11
In a comment on the original question: `name` is a reserved keyword. — Marty McVry, Feb 12 '22 at 11:21

PHP is not updating the data in table (bindParam)

1 Answers1