PDO updating MYSQL record

MySQL database
#1

I am having a nightmare with something I thought was working. Novice to PDO. I have a single page form that picks up details from a MYSQL database and allows the user to edit. So I know the connection to the database is OK.

On clicking the submit button it runs another php page to update the record. At which point it goes wrong.

$id = htmlspecialchars($_POST['id']);
$book_year = htmlspecialchars($_POST['book_year']);
$chapter = htmlspecialchars($_POST['chapter']);
$para = htmlspecialchars($_POST['para']);
$title = htmlspecialchars($_POST['title']);
$summary = htmlspecialchars($_POST['summary']);
$seen_date = htmlspecialchars($_POST['seen_date']);



$pdo = new PDO($dsn, $user, $pass, $options);
$data = [
    'id' => $id,
    'book_year' => $book_year,
    'chapter' => $chapter,
    'para' => $para,
    'title' => $title,
    'summary' => $summary,
    'seen_date' => $seen_date,
];

// issue is in next 4 lines
$sql = "UPDATE update_tbl SET book_year=:book_year, chapter=:chapter, para=:para, title :title, 
summary=:summary, seen_date=:seen_date WHERE id=:id";
$stmt= $pdo->prepare($sql);
$stmt->execute($data);

It seems to be something in those last 4 lines it isn’t happy with. I’m rapidly losing the will to live so hoping someone can help out. Thanks

#2

Do you have php’s error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php would help you by reporting and displaying all the errors it detects?

Do you have error handling for the PDO prepare and execute statements, so that you will know when/why they are failing? Note: a pdo connection always uses an exception for a connection error, you should use exceptions for the other pdo statements as well. What are the $options you are using?

Htmlspecialchars is an output function. It is used when you output data in a html context. Do NOT use it on the input data to your post method from processing code.

Your form and form processing code should be on the same page so that you can display any user/validation error messages when you re-display the form and re-populate the form fields with the submitted data values so that the user doesn’t need to reenter the data that they have modified.

Your post method form processing code should -

  1. Detect if a post method form has been submitted.
  2. Keep the set of post data as an array, then operate on elements in this array throughout the rest of the code.
  3. Trim, than validate all inputs, storing validation error messages in an array using the field name as the main array index.
  4. If there are no validation errors (the array holding the error messages will be empty), use the submitted form data. If there are errors, you would test/display the content of the errors array at the appropriate location in the html document when you re-display the form.
  5. Your PDO connection code should set the character set to match your database tables, set emulated prepared queries to false, set the error mode to exceptions, and set the default fetch mode to assoc.
  6. If you use positional prepared query place-holders ?, you can save a lot of typing.
  7. Note: an insert/update query can result in duplicate data. You would have an appropriate unique (composite) index defined for your table. Your code would catch any exception from the execution of the insert/update query, test if the error number is for a duplicate index, then setup a message telling the visitor what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it.
  8. Upon successful completion of the post method form processing code, you should redirect to the exact same url of the current page to cause a get request for that page.
#3

The issue appears to be in the mysql syntax based on the apache error log.

It’ll take me a little time to digest the rest of your post - thanks for the info.

#4

Finally found it - missing equals sign!

#5

This is why you should use the simplest syntax. Using positional place-holders ? would have made that error stand out.

#6 
title :title

title=:title
Sponsor our Newsletter | Privacy Policy | Terms of Service