Why are my SQL queries not changing my database?

Question

New to php and sql. I'm having to run a local database on Netbeans (created with db browser).

My database is connecting fine, returning results. But when i go to edit/change results via the interface, it doesn't have any affect. Am I missing something?

this is my code for inserting a new entry:

    <?php

    error_reporting(E_ALL);
    ini_set('display_errors', 1);

    $pdo = new PDO('sqlite:events.db'); 

    if (isset($_POST['submit'])) {

    try {

      $sql = "INSERT INTO events (type, name) VALUES (:eventsType, :eventsName)";
      //named paramaters
      $stmt = $pdo->prepare($sql);

      $eventstype = filter_input(INPUT_POST, 'eventstype');
      $stmt->bindValue(':eventstype', $eventstype, PDO::PARAM_STR);

      $eventsName = filter_input(INPUT_POST, 'eventsName');
      $stmt->bindValue(':eventsName', $eventsName, PDO::PARAM_STR);

      //$movieRating = filter_input(INPUT_POST, 'movieRating');
      //$stmt->bindValue(':movieRating', $movieRating, PDO::PARAM_INT);

      $stmt->execute();
      $pdo = null;

      echo "<h1>Event added to database.</h1>";
      echo '<a href="index.php">Return to main menu</a>';

    } catch (PDOException $e) {
        //for development
        print "We had an error: " . $e->getMessage() . "<br/>";
        die();
    }
    ?>

    <?php } else {  ?>

    <form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post">
    Type: <input type="text" name="eventsType"><br>
    Name: <input type="text" name="eventsName"><br>
    //Rating: <input type="text" name="movieRating"><br>
    <input type="submit" name="submit">
    </form>

    <?php } ?>

this is for updating an entry:

    <?php

    $pdo = new PDO('sqlite:events.db');


    if (isset($_POST['submit'])) {

    try {


      $sql = "UPDATE events SET type = :eventsType, name =:eventsName WHERE id=:movieId";
      //named paramaters
      $stmt = $pdo->prepare($sql);

      $eventsId = filter_input(INPUT_POST, 'eventsId');
      $stmt->bindValue(':eventsId', $eventsId, PDO::PARAM_INT);

      $eventsType = filter_input(INPUT_POST, 'eventsType');
      $stmt->bindValue(':eventsType', $eventsType, PDO::PARAM_STR);

      /*$movieDescription = filter_input(INPUT_POST, 'movieDescription');
      $stmt->bindValue(':movieDescription', $movieDescription, PDO::PARAM_STR);*/

      /*$movieRating = filter_input(INPUT_POST, 'movieRating');
      $stmt->bindValue(':movieRating', $movieRating, PDO::PARAM_STR);*/

      $stmt->execute();
      $pdo = null;

      echo "<h1>Done</h1>";
      echo '<a href="index.php">Return to main menu</a>';


    } catch (PDOException $e) {
        print "We had an error: " . $e->getMessage() . "<br/>";
        die();
    }
    ?>



    <?php } else {
      try {

        $sql = "SELECT * FROM events WHERE id=:eventsId LIMIT 1";

        //named paramaters
        $stmt = $pdo->prepare($sql);

        $id = filter_input(INPUT_GET, 'id');
        $stmt->bindValue(':eventsId', $id, PDO::PARAM_INT);

        $stmt->execute();
        $r = $stmt->fetch(PDO::FETCH_ASSOC);
        $pdo = null;

        if (!$r){
          print "No events specified to update";
          exit();
        }

      } catch (PDOException $e) {
          print "We had an error: " . $e->getMessage() . "<br/>";
          die();
      }
      ?>



    <form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post">
    EventID: <input type="text" readonly name="movieId" value="<?= htmlspecialchars($r['id']); ?>"><br>
    Type: <input type="text" name="movieTitle" value="<?= htmlspecialchars($r['title'])?>"><br>
    Name: <input type="text" name="movieDescription" value="<?= htmlspecialchars($r['description']); ?>"><br>

    <input type="submit" name="submit" value="Update record" >
    </form>

    <?php } ?>

and this is for deleting:

 <?php

$pdo = new PDO('sqlite:events.db');


if (isset($_POST['submit'])) {

if ( isset($_POST['confirm']) && $_POST['confirm'] == 'yes'){
  try {
    //better, but still need to do more checking for security
    $sql = "DELETE from events WHERE id=:eventId";
    //named paramaters
    $stmt = $pdo->prepare($sql);

    $eventsId = filter_input(INPUT_POST, 'eventsId');
    $eventsId = filter_input(INPUT_POST, 'eventsId', FILTER_SANITIZE_NUMBER_INT);
    $stmt->bindValue(':eventsId', $eventsId, PDO::PARAM_INT);

    $stmt->execute();
    $pdo = null;
    echo '<h1>Film has been removed from database</h1>';
    echo '<a href="index.php">Return to main menu</a>';

  } catch (PDOException $e) {
      print "We had an error: " . $e->getMessage() . "<br/>";
      die();
  }

} else {
  echo 'You need confirm.';

}


?>



<?php } else {
  try {

    $sql = "SELECT * FROM events WHERE id=:eventsId LIMIT 1";

    //named paramaters
    $stmt = $pdo->prepare($sql);

    $id = filter_input(INPUT_GET, 'id');
    $stmt->bindValue(':eventsId', $id, PDO::PARAM_INT);

    $stmt->execute();
    $r = $stmt->fetch(PDO::FETCH_ASSOC);
    $pdo = null;

    if (!$r){
      print "No film specified to update";
      exit();
    }

  } catch (PDOException $e) {
      print "We had an error: " . $e->getMessage() . "<br/>";
      die();
  }
  ?>



  Title:<?= htmlspecialchars($r['type'])?> <br>
  Desc:<?= htmlspecialchars($r['name']); ?> <br>
  Year:<?= htmlspecialchars($r['rating']); ?> <br>




<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post">
<input type="text" readonly name="eventsId" value="<?= htmlspecialchars($r['id']); ?>"><br>
<input type="checkbox" name="confirm" value="yes">Yes, delete this record<br>
<input type="submit" name="submit" value="Delete record">
</form>

<?php } ?>

I have no idea why it's not working. Its an example uni gave us and told us to change. My row in my table are id, type, name and description.

Any help greatly appreciated as i really dont know whats going on!

The UPDATE should be throwing an error... there's three bind placeholders but only two of them have value supplied. The DELETE should be throwing an error too. The call to bindValue refers to a bind placeholder `:eventsId` that doesn't exist in the SQL statement. (The placeholder in the SQL statement has a different name.) If we are going to catch exceptions from PDO, we need to enable PDO exceptions with `PDO::ATTR_ERRORMODE`. (The assignment is only partially about getting the code to run. What's vitally important is to learn the skills needed to *debug* a program.) — spencer7593, May 24 '17 at 22:51

score 1 · Answer 1 · answered May 24 '17 at 22:43

It seems likely you're getting errors from pdo.

Either set pdo's error mode to throw exceptions ....

$pdo = new PDO('sqlite:events.db',
               array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));

or check your execute() methods for errors.

$stmt->execute() || die $stmt->errorInfo();

Why are my SQL queries not changing my database?

1 Answers1