I want to setup a manual payment confirmation page

Olumide · November 15, 2023

I want to setup a manual payment confirmation for a cashier. When the student pay his or her school. The cashier update the payment by entering the amount paid. I have a table that contain the students list and also fee table.

Now, the data are posting to the database, but the ajax is not working. From the index page, the cashier should be able to select student, and immediately a student is selected, the ajax will output the details of the student including fee payable.

Here is index.php

<body>
    <label for="studentSelect">Select Student:</label>
    <select id="studentSelect" onchange="getStudentDetails()">
        <!-- Options for students populated dynamically using Ajax -->
    </select>

    <div id="studentDetails">
        <!-- Student details will be displayed here using Ajax -->
    </div>

    <label for="amount">Enter Amount:</label>
    <input type="number" id="amount">

    <button onclick="logPayment()">Log Payment</button>

    <script>
        // Your JavaScript code here
        function getStudentDetails() {
    var studentId = document.getElementById("studentSelect").value;

    // Use Ajax to fetch student details based on the selected student
    var xhr = new XMLHttpRequest();
    xhr.open("GET", "get_student_details.php?studentId=" + studentId, true);

    xhr.onreadystatechange = function () {
        if (xhr.readyState == 4 && xhr.status == 200) {
            var studentDetails = xhr.responseText;
            document.getElementById("studentDetails").innerHTML = studentDetails;
        }
    };

    xhr.send();
}

function logPayment() {
    var studentId = document.getElementById("studentSelect").value;
    var amount = document.getElementById("amount").value;

    // Use Ajax to log the payment and update the database
    var xhr = new XMLHttpRequest();
    xhr.open("POST", "log_payment.php", true);
    xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    
    var params = "studentId=" + studentId + "&amount=" + amount;
    
    xhr.onreadystatechange = function () {
        if (xhr.readyState == 4 && xhr.status == 200) {
            alert(xhr.responseText); // Display the response from the server
        }
    };

    xhr.send(params);
}

    </script>
</body>

Here is the get_student_details.php


<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Connect to your database (replace with your database credentials)
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydbt";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Retrieve data from the AJAX request
    $studentId = $_GET['studentId'];
    //testing here
    
    $sql = "SELECT
        c.classname,
        CONCAT(s.firstname, ' ', s.lastname) AS name,
        sc.feetype,
        f.description,
        f.term_fee
    FROM
        student s
    JOIN
        student_class sc ON s.id = sc.studentid
    JOIN
        class c ON sc.classid = c.id
    JOIN
        fee f ON c.year = f.year AND sc.semesterid = f.semesterid AND f.feetype = sc.feetype
    WHERE
        sc.semesterid = :semesterId
    ORDER BY
        c.year, c.classname, name";
    
    // Prepare the SQL statement
    $stmt = $conn->prepare($sql);
    
    // Bind parameters
    $stmt->bindParam(':semesterId', $studentId, PDO::PARAM_INT);
    
    // Execute the query
    $stmt->execute();
    
    // Fetch the results
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if ($result) {
        // Output student details as HTML (you can customize this part)
        foreach ($result as $row) {
            echo "Class: " . $row["classname"] . "<br>";
            echo "Fee Type: " . $row["feetype"] . "<br>";
        }
    } else {
        echo "No details found for the selected student";
    }
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

$conn = null;
?>

Here is the log_payment.php


<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Connect to your database (replace with your database credentials)
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "mydb";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Retrieve data from the AJAX request
    $studentId = $_POST['studentId'];
    $amount = $_POST['amount'];
    $description = "Payment for Semester XYZ"; // Replace with your description

    // Validate input data
    if (empty($studentId) || empty($amount)) {
        die("Error: Student ID and amount cannot be empty.");
    }

    // Use prepared statement to prevent SQL injection
    $sql = "INSERT INTO payment (studentid, pay_date, amount, description) 
            VALUES (?, CURDATE(), ?, ?)";
    
    // Prepare the SQL statement
    $stmt = $conn->prepare($sql);
    
    // Bind parameters
    $stmt->bindParam(1, $studentId, PDO::PARAM_INT);
    $stmt->bindParam(2, $amount, PDO::PARAM_STR);
    $stmt->bindParam(3, $description, PDO::PARAM_STR);

    if ($stmt->execute()) {
        echo "Payment logged successfully!";
    } else {
        echo "Error logging payment: " . $stmt->errorInfo()[2];
    }
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

$conn = null;
?>

Here is my sql for the tables


CREATE TABLE `fee` (
  `id` int(11) NOT NULL,
  `feetype` enum('Day','Boarder') DEFAULT NULL,
  `year` tinyint(4) DEFAULT NULL,
  `semesterid` int(11) DEFAULT NULL,
  `fee_description` varchar(100) DEFAULT NULL,
  `term_fee` decimal(11,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `fee`
--

INSERT INTO `fee` (`id`, `feetype`, `year`, `semesterid`, `fee_description`, `term_fee`) VALUES
(1, 'Boarder', 7, 13, 'TUITION', '110000.00'),
(2, 'Boarder', 7, 13, 'FEEDING', '191100.00'),
(3, 'Boarder', 7, 13, 'CARDIGAN', '10000.00'),
(4, 'Boarder', 7, 13, 'UNIFORM (2), TIE (1)', '23000.00');

ALTER TABLE `fee`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_fee_semesterid` (`semesterid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `fee`
--
ALTER TABLE `fee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=128;
COMMIT;


CREATE TABLE `student_class` (
  `id` bigint(20) NOT NULL,
  `studentid` int(11) DEFAULT NULL,
  `semesterid` int(11) DEFAULT NULL,
  `classid` int(11) DEFAULT NULL,
  `feetype` enum('Day','Boarder') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `student_class`
--

INSERT INTO `student_class` (`id`, `studentid`, `semesterid`, `classid`, `feetype`) VALUES
(1, 122, 7, 4, 'Boarder'),
(2, 122, 8, 4, 'Boarder'),
(3, 122, 9, 4, 'Boarder');

--
ALTER TABLE `student_class`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unq_student_class` (`studentid`,`semesterid`),
  ADD KEY `idx_student_course_studentid` (`studentid`),
  ADD KEY `idx_student_course_semesterid` (`semesterid`),
  ADD KEY `idx_student_class_classid` (`classid`);

ALTER TABLE `student_class`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1589;


  CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentid` int(11) DEFAULT NULL,
  `pay_date` date DEFAULT NULL,
  `amount` decimal(10,2) NOT NULL,
  `description` varchar(255) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `payment`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_payment_studentid` (`studentid`);
COMMIT;



CREATE TABLE `semester` (
  `id` int(11) NOT NULL,
  `semesterName` varchar(100) NOT NULL,
  `sessionid` int(11) NOT NULL,
  `date_from` date DEFAULT NULL,
  `date_until` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `semester`
--

INSERT INTO `semester` (`id`, `semesterName`, `sessionid`, `date_from`, `date_until`) VALUES
(13, '1st Term', 4, '2023-08-01', '2023-12-15');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `semester`
--
ALTER TABLE `semester`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_semester_session1_idx` (`sessionid`);


ALTER TABLE `semester`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
COMMIT;


CREATE TABLE `class` (
  `id` int(11) NOT NULL,
  `classname` varchar(45) DEFAULT NULL,
  `levelid` int(11) NOT NULL,
  `year` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `class`
--

INSERT INTO `class` (`id`, `classname`, `levelid`, `year`) VALUES
(1, 'Year 7 A', 3, 7),
(2, 'Year 7 B', 3, 7),
(3, 'Year 7 M', 3, 7),
(4, 'Year 8 Ad', 4, 8);

ALTER TABLE `class`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_class_level1_idx` (`levelid`);

ALTER TABLE `class`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

  CREATE TABLE `student` (
  `id` int NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `otherName` varchar(50) NOT NULL,
  `matricNo` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `levelId` int DEFAULT NULL,
  `DOB` date NOT NULL,
  `Phone` varchar(15) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `dateCreated` date NOT NULL,
  `leavingdate` date DEFAULT NULL,
  `login_disabled` tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`id`, `firstName`, `lastName`, `otherName`, `matricNo`, `password`, `levelId`, `DOB`, `Phone`, `Email`, `image`, `dateCreated`, `leavingdate`, `login_disabled`) VALUES
(122, 'John', 'Kay', '', 'MAB/2021/00001', '$7dydgdgdhhdmOt8TurhyM4RVaX4JM3fhffhfhfj', 7, '2008-04-09', '8100000000', 'example@yahoo.com', 'E.jpg', '2022-10-11', NULL, 0),

ALTER TABLE `student`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_student_matricNo` (`matricNo`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1005;
COMMIT;

Barand · November 15, 2023

2 hours ago, Olumide said:

but the ajax is not working

Probably because you have a query which is searching for a specific semester_id and you are passing it a student_id expecting it to find the student.

Olumide · November 15, 2023

3 minutes ago, Barand said:

Probably because you have a query which is searching for a specific semester_id and you are passing it a student_id expecting it to find the student.

Thanks for your feedback sir. I only have the query to get the students list and if a student is selected, the ajax should pop out the details of the students such as "class, fees payable,... but not working maybe sql query is also wrong.

gizmola · November 16, 2023

5 hours ago, Olumide said:

Thanks for your feedback sir. I only have the query to get the students list and if a student is selected, the ajax should pop out the details of the students such as "class, fees payable,... but not working maybe sql query is also wrong.

Yes he literally told you that it is wrong. It's the query in get_student_details.php.

    WHERE
        sc.semesterid = :semesterId

Should be

WHERE
    s.id = :studentId

And your bind parameter needs to be changed. This is wrong.

// Bind parameters 
    $stmt->bindParam(':semesterId', $studentId, PDO::PARAM_INT);

Should be:

// Bind parameters
    $stmt->bindParam(':studentId', $studentId, PDO::PARAM_INT);

Olumide · November 16, 2023

44 minutes ago, gizmola said:
Yes he literally told you that it is wrong. It's the query in get_student_details.php.
    WHERE
        sc.semesterid = :semesterId
Should be
WHERE
    s.id = :studentId
And your bind parameter needs to be changed. This is wrong.
// Bind parameters 
    $stmt->bindParam(':semesterId', $studentId, PDO::PARAM_INT);
Should be:
// Bind parameters
    $stmt->bindParam(':studentId', $studentId, PDO::PARAM_INT);

Thanks for your feed back sir. I changed the queries as requested but still not working. I attached the screenshort.

maxxd · November 16, 2023

You're not populating the studentSelect form element at any point. You create it in the HTML, assign an event listener to the change event, but never actually put anything in there to change.

Olumide · November 16, 2023

2 hours ago, maxxd said:

You're not populating the studentSelect form element at any point. You create it in the HTML, assign an event listener to the change event, but never actually put anything in there to change.

I appreciate you @maxxd for your contribution also. Now, I can select from the student list, but I want it in such a way that if a student is selected, the ajax will output the details of the student including fees payable which are already in the table fee.

Thanks

gizmola · November 16, 2023

The best way to debug ajax calls like this is to use your browser developer tools. I typically use Chrome. You should open the network tab, and look at the request and response data, in order to figure out where your problems might be.

Olumide · November 16, 2023

4 hours ago, gizmola said:

The best way to debug ajax calls like this is to use your browser developer tools. I typically use Chrome. You should open the network tab, and look at the request and response data, in order to figure out where your problems might be.

Thanks @gizmola, I will work on that.

Sign In

I want to setup a manual payment confirmation page

Recommended Posts

Olumide

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

maxxd

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

Olumide

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information