SQL database not updating data in PHP

Question

I'm trying to update some data from my database but nothing I've tried/found has been of any success to me. There are no errors or anything, literally nothing happens. The page reloads but it does not store anything into the database. How can I fix this problem?

The code:

function AddToBook() {
          $get_post_id = filter_var(htmlentities($_GET['pid']), FILTER_SANITIZE_NUMBER_INT);
          $book_id = filter_var(htmlentities($_GET['bid']), FILTER_SANITIZE_NUMBER_INT);
          $get_episodes = filter_var(htmlentities($_GET['ep']), FILTER_SANITIZE_NUMBER_INT);
          $episode = $get_episodes + 1;

          // Insert book data into wpost

          $odb = new PDO("mysql:host=localhost;dbname=test", 'root', '');

          $updatePostRecord = "UPDATE wpost SET book_id=:book_id, episode_number=:episode WHERE id=:get_post_id";
          $UpdatePost = $odb->prepare($updatePostRecord);
          $UpdatePost->bindParam(':book_id',$book_id,PDO::PARAM_INT);
          $UpdatePost->bindParam(':episode',$episode,PDO::PARAM_INT);
          $UpdatePost->bindParam(':get_post_id',$get_post_id,PDO::PARAM_INT);
          $UpdatePost->execute();

          // Insert post data into books
          $updateBookRecord = "UPDATE books SET episodes='$episode' WHERE id='$book_id'";
          $UpdateBook = $conn->prepare($updateBookRecord);
          $UpdateBook->execute();
        }

Trukken · Accepted Answer · 2020-08-17T08:37:07.817

You want to use the PDO class that you have defined there instead of $conn (that is not defined), might as well put the variables into brackets just to make sure they are interpreted correctly, if you use a string literal.

$updateBookRecord = "UPDATE books SET episodes='{$episode}' WHERE id='{$book_id}'";
$UpdateBook = $obd->prepare($updateBookRecord);
$UpdateBook->execute();

Also, as it stand right now this is not a proper prepared statement. You should use bindParam function like on the initial UpdatePost.

Here is how it would look as a proper prepared statement.

$updateBookRecord = "UPDATE books SET episodes=:episode WHERE id=:book_id";
$UpdateBook = $obd->prepare($updateBookRecord);
$UpdateBook->bindParam(':episode',$episode,PDO::PARAM_INT);
$UpdateBook->bindParam(':book_id',$book_id,PDO::PARAM_INT);
$UpdateBook->execute();

Jitesh Dhamaniya · Answer 2 · 2020-08-17T08:39:15.210

0

When you use single quotes '' with variable, php understand it as a string not variable. so you might want to change your update statement to

 $updateBookRecord = "UPDATE books SET episodes = $episode WHERE id= $book_id ";

or alternatively

$updateBookRecord = "UPDATE books SET episodes = ". $episode . " WHERE id= ".$book_id;

However this is not the standard way to do things, and invite sql injections, you better use PDO or other mechanism to make it more secure. https://www.w3schools.com/sql/sql_injection.asp

edited Aug 17 '20 at 08:39

answered Aug 17 '20 at 08:24

Jitesh Dhamaniya

1,326
2
8
17

1

This answer promotes the creation of sql statements via string concatenation, which is error prone and enables sql injection type attacks. Only follow this advice if you do not mind your website to be hacked! – Shadow Aug 17 '20 at 08:33
@Shadow answer edited. – Jitesh Dhamaniya Aug 17 '20 at 08:39
Does not make it any better. – Shadow Aug 17 '20 at 09:11

score 0 · Answer 3 · answered Aug 17 '20 at 08:31

0

An update can successfully update 0 rows. I would triple check your WHERE clause to see if it is actually trying to match existing rows.

answered Aug 17 '20 at 08:31

Savage75

15
3

SQL database not updating data in PHP

3 Answers3