Fixing Issues with PHP, AJAX, and MySQL Data Insertion

Olumide · January 25

I have this html form which worked, but I added grandTotal to populate its column "total_fee_payable" in the table. When I added this, I altered the javascript and php code to insert the data into the database. Suddenly, it stopped working and when I checked the console log, it shows data saved successfully. What could be the problem please?

Here is part of my html form

<div id="student-details" class="w3-margin-top w3-margin-bottom">
   <h2>Student Details</h2>
   <!-- Custom label and input container -->
   <div class="w3-row-padding">
      <div class="w3-half w3-padding">
         <label for="student-name">Name:</label>
         <input type="text" id="student-name" name="studentName" class="w3-input w3-border" disabled>
      </div>
      <div class="w3-half w3-padding">
         <label for="current-class">Current Class:</label>
         <input type="text" id="current-class" name="currentClass" class="w3-input w3-border" disabled>
      </div>
      <div class="w3-half w3-padding">
         <label for="selected-semester">Selected Semester:</label>
         <input type="text" id="selected-semester" name="selectedSemester" class="w3-input w3-border" disabled>
      </div>
      <div class="w3-half w3-padding">
         <label for="invoice-number">Invoice Number:</label>
         <input type="text" id="invoice-number" name="invoiceNumber" class="w3-input w3-border" disabled>
      </div>
      <div class="w3-half w3-padding">
         <label for="item">Fee Description:</label>
         <input type="text" id="item" name="feeDescription" class="w3-input w3-border" autocomplete="off">
      </div>
      <div class="w3-half w3-padding">
         <label for="price">Amount:</label>
         <input type="number" id="price" name="feeAmount" class="w3-input w3-border" autocomplete="off">
      </div>
      <div class="w3-half w3-padding">
         <label for="lessFee">Enter Less Fee Description:</label>
         <input type="text" id="fee_description" name="less_fee_description" class="w3-input w3-border" autocomplete="off">
      </div>
      <div class="w3-half w3-padding">
         <label for="priceAmount">Amount:</label>
         <input type="number" id="fee_amount" name="less_fee_amount" class="w3-input w3-border" autocomplete="off">
      </div>
      
      <div class="w3-half w3-padding">
         <label for="price">Discount Amount:</label>
         <input type="number" id="discount_amount" name="discount_amount" class="w3-input w3-border" autocomplete="off">
      </div>
   </div>
</div>

<h2 class="w3-margin-bottom">Analysis</h2>
<div class="w3-responsive">
                  
                    <table class="w3-table w3-hoverable w3-bordered w3-striped" id="order-item-tbl">
    

  <tfoot>
    
        <tr class="bg-gradient bg-dark-subtle bg-opacity-50">
        <th class="bg-transparent w3-center w3-border" colspan="2">Sub-Total</th>
        <th class="bg-transparent w3-border w3-right-align" id="subTotalText">0</th>
    </tr>
    
    <tr class="bg-gradient bg-dark-subtle bg-opacity-50">
        <th class="bg-transparent w3-center w3-border" colspan="2">Grand Total</th>
        <th class="bg-transparent w3-border w3-right-align" id="grandTotal" name="grandTotal">0</th>
    </tr>
</tfoot>


</table>

                </div><br>

                                <button type="button" id="order-form-submit" class="w3-button w3-blue w3-block"><i class="far fa-plus-square"> </i> Add Invoice</button>
            </div>

Here is the javascript

// Function to update the grand total based on the formula
    function updateGrandTotal() {
        // Get values from the form
        var price = parseFloat(document.getElementById('price').value) || 0;
        var feeAmount = parseFloat(document.getElementById('fee_amount').value) || 0;
        var discountAmount = parseFloat(document.getElementById('discount_amount').value) || 0;

        // Calculate grand total
        var grandTotal = price - (feeAmount + discountAmount);

        // Update the grand total element
        document.getElementById('grandTotal').textContent = grandTotal.toFixed(2);
    }

    // Attach the update function to input events
    document.getElementById('price').addEventListener('input', updateGrandTotal);
    document.getElementById('fee_amount').addEventListener('input', updateGrandTotal);
    document.getElementById('discount_amount').addEventListener('input', updateGrandTotal);


    // Function to save invoice data
    function saveInvoiceData() {
        var semesterId = $('#semester').val();
        var studentName = $('#student-name').val();
        var feeDescription = $('#item').val();
        var feeAmount = $('#price').val();
        var lessFeeDescription = $('#fee_description').val();
        var lessFeeAmount = $('#fee_amount').val();
        var discountAmount = $('#discount_amount').val();
        //var grandTotal = $('#grandTotal').val();
        var invoiceNumber = $('#invoice-number').val();

        // Make an AJAX request to save invoice data
        $.ajax({
            url: 'invoice.php',
            method: 'POST',
            data: {
                semesterId: semesterId,
                studentName: studentName,
                feeDescription: feeDescription,
                feeAmount: feeAmount,
                lessFeeDescription: lessFeeDescription,
                lessFeeAmount: lessFeeAmount,
                discountAmount: discountAmount,
                //grandTotal: grandTotal,
                invoiceNumber: invoiceNumber
            },
            success: function (response) {
                // Handle success response if needed
                console.log('Invoice data saved successfully.');
            },
            error: function (error) {
                console.error('Error:', error);
            }
        });
    }

    // Bind the function to  "Save" button
    $('#order-form-submit').on('click', function () {
        saveInvoiceData();
    });
});

Here is the invoice.php

// Check if the form data is set
if (
    isset($_POST['semesterId']) &&
    isset($_POST['studentName']) &&
    isset($_POST['feeDescription']) &&
    isset($_POST['feeAmount']) &&
    isset($_POST['lessFeeDescription']) &&
    isset($_POST['lessFeeAmount']) &&
    isset($_POST['discountAmount'])&&
    //isset($_POST['grandTotal'])&&
    isset($_POST['invoiceNumber'])
) {
    // Get form data
    $semesterId = $_POST['semesterId'];
    $studentName = $_POST['studentName'];
    $feeDescription = $_POST['feeDescription'];
    $feeAmount = $_POST['feeAmount'];
    $lessFeeDescription = $_POST['lessFeeDescription'];
    $lessFeeAmount = $_POST['lessFeeAmount'];
    $discountAmount = $_POST['discountAmount'];
    //$grandTotal = floatval($_POST['grandTotal']);
    $invoiceNumber = $_POST['invoiceNumber'];

    // Additional validation
    if (empty($_POST['studentName']) || empty($_POST['feeDescription']) || empty($_POST['feeAmount'])) {
        header('HTTP/1.1 400 Bad Request');
        echo 'Invalid form data. Please fill out all required fields.';
        exit;
    }

    try {
       
$query = "INSERT INTO invos (semester_id, student_name, fee_description, fee_amount, less_fee_description, less_fee_amount, discount_amount, invoice_number) 
          VALUES (:semesterId, :studentName, :feeDescription, :feeAmount, :lessFeeDescription, :lessFeeAmount, :discountAmount, :invoiceNumber)";


        // Prepare the query
        $stmt = $pdo->prepare($query);

        // Bind parameters
        $stmt->bindParam(':semesterId', $semesterId, PDO::PARAM_INT);
        $stmt->bindParam(':studentName', $studentName, PDO::PARAM_STR);
        $stmt->bindParam(':feeDescription', $feeDescription, PDO::PARAM_STR);
        $stmt->bindParam(':feeAmount', $feeAmount, PDO::PARAM_STR);
        $stmt->bindParam(':lessFeeDescription', $lessFeeDescription, PDO::PARAM_STR);
        $stmt->bindParam(':lessFeeAmount', $lessFeeAmount, PDO::PARAM_STR);
        $stmt->bindParam(':discountAmount', $discountAmount, PDO::PARAM_STR);
        //$stmt->bindParam(':grandTotal', $grandTotal, PDO::PARAM_STR);
        $stmt->bindParam(':invoiceNumber', $invoiceNumber, PDO::PARAM_STR);

        
try {
    // ...
    
    // Output the executed query for debugging
    echo $stmt->queryString;

    // Execute the query
    $stmt->execute();

        
    $rowCount = $stmt->rowCount();

    if ($rowCount > 0) {
        echo 'Invoice data saved successfully.';
    } else {
        echo 'No rows affected. Check for errors.';
    }
} catch (PDOException $e) {
    // Handle database errors
    echo 'Error: ' . $e->getMessage();
}

ginerjm · January 25

So the script does some of its work but then just stops? Are you getting any messages? Do you have PHP error checking turned on? Have you tried placing some echo lines in the code to see what steps it does get through?

Barand · January 25

Have you used the network tab in your browser developer tools to check the ajax responses?

mac_gyver · January 26

the grand total is a derived value. you would calculate it whenever you need it, but do not submit it or store it in the database table.

the jquery .ajax success function is called when the ajax request is successful. all this means is that the request and response occurred. when using an ajax request, it is up to your server-side code to build and output appropriate data that the javascript tests for and uses. since you are console logging a fixed 'Invoice data saved successfully.' success message, that is all you know. as @Barand has posted, you can check the browser's developer tools network tab to see what is actually being output by the server-side code.

as to the actual problem, which of the commented out lines of code are actually in place? does your insert query list the total_fee_payable column and have a corresponding prepared query place-holder, that's being supplied the correct value when the query gets executed?

some other things the code should/should not be doing -

you should be selecting from existing students, that are registered for the selected semester, with a select/option menu, possibly with type-a-head, auto-suggest filtering, which i'm guessing you may be doing, since you have some disabled form fields. this should result in a student_semester_id, that you would use when storing related data.
you should have a 'fee' table that holds the different type of fees/discounts. you would then have a select/option menu to select the fee type, which submits the fee_id, and the fee amount.
you would insert a new row for each different fee/discount for each student_semester_id.
the invoice number should be generated by your system, which it may be based on the disabled form field.

programing already is a tedious typing activity. if you find yourself repeating code that only differs in its name, it is a sign that you should be getting the computer to operate on the repetitive set of values, instead of you doing a lot of typing. some things you can do that will simplify the code -

in the javascript, if you assign a unique class name to the 'fee' fields, you can assign the 'input' event to all the fields with that class name at once, and you can operate on all the values by getting them as a collection and looping over them.
you can get all the form field values at once using either the jquery .serialize() method or a javascript FormData object, without writing out lines of code for each field.
in the php code, detect if a post method form was submitted, then reference the form data. this only takes one line of code, regardless of how many fields there are.
only unchecked checkbox/radio fields won't be set. these are the only cases where you should use isset() statements.
don't copy variables to other variables for nothing. this is just a waste of your time typing. keep the form data as a set, in a php array variable, then operate on elements in this array variable throughout the rest of the code.
you should trim all input data, mainly so that you can detect if it is all white-space characters, before validating it. after you do item #5 on this list, you can trim all the input data at once, using one single line of code.
you should validate all inputs separately, storing user/validation errors in an array, using the field name as the main array index.
after the end of the validation logic, if there are no errors (the array holding the user/validation errors will be empty), use the form data.
if the insert query could result in duplicate records, your exception catch logic should test for a duplicate index error number, and setup a message for the user (add it to the array holding the user/validation errors) letting them know what was wrong with the data that they submitted. for all other error numbers, just re-throw the exception and let php handle it.
if you simply supply an array of the input values to the ->execute([...]) call, you can eliminate all the bindParam() statements.
after the end of using the the form data, if there are no errors, you would setup a success response and output it. if there are errors, you would setup an error response, consisting of the all the error messages, and output it.
for an advanced programming task, if you have more than 2-3 fields, you should use a data-driven design and dynamically validate and process the data.

Olumide · January 26

8 minutes ago, mac_gyver said:

the grand total is a derived value. you would calculate it whenever you need it, but do not submit it or store it in the database table.

the jquery .ajax success function is called when the ajax request is successful. all this means is that the request and response occurred. when using an ajax request, it is up to your server-side code to build and output appropriate data that the javascript tests for and uses. since you are console logging a fixed 'Invoice data saved successfully.' success message, that is all you know. as @Barand has posted, you can check the browser's developer tools network tab to see what is actually being output by the server-side code.

as to the actual problem, which of the commented out lines of code are actually in place? does your insert query list the total_fee_payable column and have a corresponding prepared query place-holder, that's being supplied the correct value when the query gets executed?

some other things the code should/should not be doing -

you should be selecting from existing students, that are registered for the selected semester, with a select/option menu, possibly with type-a-head, auto-suggest filtering, which i'm guessing you may be doing, since you have some disabled form fields. this should result in a student_semester_id, that you would use when storing related data.

you should have a 'fee' table that holds the different type of fees/discounts. you would then have a select/option menu to select the fee type, which submits the fee_id, and the fee amount.

you would insert a new row for each different fee/discount for each student_semester_id.

the invoice number should be generated by your system, which it may be based on the disabled form field.

programing already is a tedious typing activity. if you find yourself repeating code that only differs in its name, it is a sign that you should be getting the computer to operate on the repetitive set of values, instead of you doing a lot of typing. some things you can do that will simplify the code -

in the javascript, if you assign a unique class name to the 'fee' fields, you can assign the 'input' event to all the fields with that class name at once, and you can operate on all the values by getting them as a collection and looping over them.

you can get all the form field values at once using either the jquery .serialize() method or a javascript FormData object, without writing out lines of code for each field.

in the php code, detect if a post method form was submitted, then reference the form data. this only takes one line of code, regardless of how many fields there are.

only unchecked checkbox/radio fields won't be set. these are the only cases where you should use isset() statements.

don't copy variables to other variables for nothing. this is just a waste of your time typing. keep the form data as a set, in a php array variable, then operate on elements in this array variable throughout the rest of the code.

you should trim all input data, mainly so that you can detect if it is all white-space characters, before validating it. after you do item #5 on this list, you can trim all the input data at once, using one single line of code.

you should validate all inputs separately, storing user/validation errors in an array, using the field name as the main array index.

after the end of the validation logic, if there are no errors (the array holding the user/validation errors will be empty), use the form data.

if the insert query could result in duplicate records, your exception catch logic should test for a duplicate index error number, and setup a message for the user (add it to the array holding the user/validation errors) letting them know what was wrong with the data that they submitted. for all other error numbers, just re-throw the exception and let php handle it.

if you simply supply an array of the input values to the ->execute([...]) call, you can eliminate all the bindParam() statements.

after the end of using the the form data, if there are no errors, you would setup a success response and output it. if there are errors, you would setup an error response, consisting of the all the error messages, and output it.

for an advanced programming task, if you have more than 2-3 fields, you should use a data-driven design and dynamically validate and process the data.

Hmmm, this is very deep, I am not an expert in programming but learn everyday. The script work fine before not until I wanted to be inserting the grandTotal under fee_total_payable. And I have comment out the grandTotal again from all my code but still stopped working.

From the console network tab, attached is the picture

Olumide · January 26

5 hours ago, ginerjm said:

So the script does some of its work but then just stops? Are you getting any messages? Do you have PHP error checking turned on? Have you tried placing some echo lines in the code to see what steps it does get through?

I will try to add the echo and see the output.

Olumide · January 26

5 hours ago, Barand said:

Have you used the network tab in your browser developer tools to check the ajax responses?

Here is from the network tool

mac_gyver · January 26

in the network tab, if you click on the URL, which is apparently save_invoice.php, it will show you a list of tabs for the request and response data.

Olumide · January 26

12 minutes ago, mac_gyver said:

in the network tab, if you click on the URL, which is apparently save_invoice.php, it will show you a list of tabs for the request and response data.

I am on it right now, could you believe I only modified the save_invoice.php script and got working, but I have removed all the calculations in javascript and still not working until now, but I want to retrace my steps again. You guys (no, I won't call you guys, but elders) are amazing and wonderful family.

I am still on it, and will surely be here when there is hurdles again.

Thanks for now.

Olumide · January 26

I realized my mistake. You elders are all amazing. I don't know which one I should marked as solutions but you all did very excellent and expedite my problem to be solved. However, I am still working on it as this is just a section and when I am finally done with this, I have a vital question to ask on PHP as someone told me when I went for an interview that in the next two years, PHP will be outdated and not sellable in the market again.

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

// Check if the form data is set
if (
    isset($_POST['semesterId']) &&
    isset($_POST['studentName']) &&
    isset($_POST['feeDescription']) &&
    isset($_POST['feeAmount']) &&
    isset($_POST['lessFeeDescription']) &&
    isset($_POST['lessFeeAmount']) &&
    isset($_POST['discountAmount']) &&
    isset($_POST['invoiceNumber'])     //I think the problem is adding the grandTotal variable here
) {
    // Get form data
    $semesterId = $_POST['semesterId'];
    $studentName = $_POST['studentName'];
    $feeDescription = $_POST['feeDescription'];
    $feeAmount = $_POST['feeAmount'];
    $lessFeeDescription = $_POST['lessFeeDescription'];
    $lessFeeAmount = $_POST['lessFeeAmount'];
    $discountAmount = $_POST['discountAmount'];
    $invoiceNumber = $_POST['invoiceNumber'];

    // Calculate grand total
    $grandTotal = $feeAmount - ($lessFeeAmount + $discountAmount);

    // Additional validation
    if (empty($_POST['studentName']) || empty($_POST['feeDescription']) || empty($_POST['feeAmount'])) {
        header('HTTP/1.1 400 Bad Request');
        echo 'Invalid form data. Please fill out all required fields.';
        exit;
    }

    try {
        
        $query = "INSERT INTO invoices (semester_id, student_name, fee_description, fee_amount, less_fee_description, less_fee_amount, discount_amount, invoice_number, total_fee_payable) 
                  VALUES (:semesterId, :studentName, :feeDescription, :feeAmount, :lessFeeDescription, :lessFeeAmount, :discountAmount, :invoiceNumber, :totalFeePayable)";

        // Prepare the query
        $stmt = $pdo->prepare($query);

        // Bind parameters
        $stmt->bindParam(':semesterId', $semesterId, PDO::PARAM_INT);
        $stmt->bindParam(':studentName', $studentName, PDO::PARAM_STR);
        $stmt->bindParam(':feeDescription', $feeDescription, PDO::PARAM_STR);
        $stmt->bindParam(':feeAmount', $feeAmount, PDO::PARAM_STR);
        $stmt->bindParam(':lessFeeDescription', $lessFeeDescription, PDO::PARAM_STR);
        $stmt->bindParam(':lessFeeAmount', $lessFeeAmount, PDO::PARAM_STR);
        $stmt->bindParam(':discountAmount', $discountAmount, PDO::PARAM_STR);
        $stmt->bindParam(':invoiceNumber', $invoiceNumber, PDO::PARAM_STR);
        $stmt->bindParam(':totalFeePayable', $grandTotal, PDO::PARAM_STR); // Bind grandTotal to the total_fee_payable column

Andou · January 29

On 1/26/2024 at 12:20 AM, Olumide said:

I have a vital question to ask on PHP as someone told me when I went for an interview that in the next two years, PHP will be outdated and not sellable in the market again.

Considering around 77.5% of websites are using PHP (down 1%, but still way more than half), I don't believe them.

I compare it to the concept of inertia - things that are spinning don't want to stop spinning.

Sign In

Fixing Issues with PHP, AJAX, and MySQL Data Insertion

Recommended Posts

Olumide

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

Andou

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information