1

I'm working to create a web-based grading application. I am wanting my gradebook to update scores into the SQL database as the user inputs data instead of depending on the user to hit a "save" button. This is the first time I have ever done this, so I have some questions.

  1. Right now, this code does not update the SQL table. What am I doing wrong here?

  2. How can I add in error handlers for when the SQL query is not successful? I want some type of alert() to happen if the POST fails and/or if the SQL statement is not successfully executed. How would I add this in?

  3. Is there a more secure way to doing what I am trying to do?

Desired end result:

User is able to update gradebook by just typing in the score on the input field, no need to click a save button. If there is an error that occurs that keeps the SQL table from updating according to user input, then a javascript alert should happen.

HTML/javascript page:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>


<input type='text' data-assID='6' data-usid='1' data-curScore='10' value='10'>


<script>
  $("input[type=text]").change(function(){
    var newScore = $(this).val();
    var curScore = $(this).attr('data-curScore');
    var assID = $(this).attr('data-assID');
    var usid = $(this).attr('data-usid');
    if (Number.isInteger(+newScore) || newScore == 'X' || newScore == 'x') {
      $.ajax({
        url: "SQL.php?assID=" + assID + "&usid=" + usid + "&score=" + newScore,
        type: 'POST'
      });
      alert('Successfully scored assignment '+assID+' to '+newScore+' for user '+usid+'!');
    } else {
      $(this).val(curScore);
      alert('The only valid input options are either an integer or \'X\'');
    }

  });
</script>

SQL.php page source:

<?php

  session_start();
  require '../dbh.int.php';


  if (isset($_POST)) {

    $usid = $_POST['usid'];
    $assID = $_POST['assID'];
    $score = $_POST['score'];

    if (is_numeric($score)) { // If the score is an integer
      if ($score == 0) {
        $SQL = mysqli_prepare($connection, "UPDATE assignGrades SET status = NULL, graded=?, score=0, submitted = NULL WHERE (usid=? AND assID=?)");
        mysqli_stmt_bind_param($SQL, "sss", date('Y-m-d H:i:s'), $usid, $assID);
      }
      else {
        $SQL = mysqli_prepare($connection, "UPDATE assignGrades SET status = NULL, graded=?, score=? WHERE (usid=? AND assID=?)");
        mysqli_stmt_bind_param($SQL, "ssss", date('Y-m-d H:i:s'), $score, $usid, $assID);
      }
      mysqli_stmt_execute($SQL); unset($SQL);
    }

  }


 ?>

SQL database structure: enter image description here

Mathew
  • 113
  • 2
  • 8
  • FYI `is_numeric` accepts also a float. So your comment `// If the score is an integer` is quite misleading – Roko C. Buljan Oct 10 '21 at 00:28
  • Your other issue is that AJAX is asynchronous. You cannot do an `alert()` synchronously. You need to wait for your server to respond and catch the response inside an AJAX `success` callback function. You have all the needed info in the jQuery DOCS for `jQuery.ajax`. – Roko C. Buljan Oct 10 '21 at 00:30
  • @RokoC.Buljan I've looked through this doc but I am not understanding how to fetch success/failure messages from the ```SQL.PHP``` page. Also, this script does not post anything right now, what could be causing this? – Mathew Oct 10 '21 at 00:44
  • Always do intval on integer like : intval($_POST['score']); – SKJ Oct 10 '21 at 01:44
  • This should throw a fatal error `mysqli_stmt_bind_param($SQL, "sss", date('Y-m-d H:i:s'), $usid, $assID);` Are you sure you have error reporting enabled? – Dharman Oct 10 '21 at 15:17
  • If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection – Dharman Oct 10 '21 at 15:19
  • @Dharman the link you posted does not answer my original question... – Mathew Oct 10 '21 at 16:26
  • Fair enough, then perhaps you can [edit] the question and clarify that you have applied the suggestions from my link and this is not what were asking for. At the moment it's not very clear what you are asking for. Also, it seems like you are asking 3 unrelated questions. Please clarify which question should be answered. Once the question is edited we can consider reopening it. – Dharman Oct 10 '21 at 16:28

0 Answers0