how to implement a student registration system using MySQLi and AJAX

Rashmi Mishra
personRashmi Mishra
October 30, 2024
0

How to implement a student registration system

 using MySQLi and AJAX

Here’s how to implement a student registration system using MySQLi and AJAX. This example includes the necessary PHP, MySQL, and AJAX code to dynamically load sections based on the selected branch, and to retrieve the roll number based on the selected section.

 Project Structure

/student_registration/

│

├── index.php

├── db.php

├── fetch.php

├── register.php

└── styles.css

Database Setup

1.  Create a database called student_db.

2.  Create tables for branches, sections, and students:

CREATE TABLE branches (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL

);

 

CREATE TABLE sections (

    id INT AUTO_INCREMENT PRIMARY KEY,

    branch_id INT,

    name VARCHAR(100) NOT NULL,

    roll_no_start INT DEFAULT 1,

    FOREIGN KEY (branch_id) REFERENCES branches(id)

);

 

CREATE TABLE students (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    email VARCHAR(100) NOT NULL UNIQUE,

    phone VARCHAR(15) NOT NULL,

    roll_no INT NOT NULL,

    section_id INT,

    FOREIGN KEY (section_id) REFERENCES sections(id)

);

  

  

1. db.php - Database Connection

<?php

$host = 'localhost';

$dbname = 'student_db';

$username = 'root'; // Change this if you have a different username

$password = ''; // Change this if you have a different password

 

// Create connection

$conn = new mysqli($host, $username, $password, $dbname);

 

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

?>

 


2. fetch.php 

Fetch Branches, Sections, and Roll Numbers

<?php

include 'db.php';

 

$action = $_POST['action'] ?? '';

 

if ($action == 'fetchBranches') {

    $result = $conn->query("SELECT * FROM branches");

    $branches = $result->fetch_all(MYSQLI_ASSOC);

    echo json_encode($branches);

}

 

if ($action == 'fetchSections') {

    $branchId = $_POST['branch_id'];

    // Using query method with string interpolation for branchId

    $result = $conn->query("SELECT * FROM sections WHERE branch_id = $branchId");

    $sections = $result->fetch_all(MYSQLI_ASSOC);

    echo json_encode($sections);

}

 

if ($action == 'fetchRollNumbers') {

    $sectionId = $_POST['section_id'];

   

    // Using query method to get roll_no_start for the section

    $result = $conn->query("SELECT roll_no_start FROM sections WHERE id = $sectionId");

    $section = $result->fetch_assoc();

    $rollNoStart = $section ? $section['roll_no_start'] : 1;

 

    // Count existing students to determine the next roll number

    $countResult = $conn->query("SELECT COUNT(*) as count FROM students WHERE section_id = $sectionId");

    $count = $countResult->fetch_assoc()['count'];

 

    // Set the next roll number

    $nextRollNo = $rollNoStart + $count;

    echo json_encode(['roll_no' => $nextRollNo]);

}

?>

 

 

  

 3. index.php - Frontend

<!DOCTYPE html>

<html lang="en">

<head>

    <meta charset="UTF-8">

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Student Registration</title>

    <link rel="stylesheet" href="styles.css">

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

</head>

<body>

 

<h1>Student Registration</h1>

<form id="studentForm">

    <input type="text" name="name" id="studentName" placeholder="Name" required>

    <input type="email" name="email" id="studentEmail" placeholder="Email" required>

    <input type="text" name="phone" id="studentPhone" placeholder="Phone" required>

 

    <label for="branch">Select Branch:</label>

    <select name="branch" id="branchSelect" required>

        <option value="">Select Branch</option>

    </select>

 

    <label for="section">Select Section:</label>

    <select name="section" id="sectionSelect" required disabled>

        <option value="">Select Section</option>

    </select>

 

    <label for="roll_no">Roll No:</label>

    <input type="text" name="roll_no" id="roll_no" placeholder="Roll Number" required readonly>

 

    <button type="submit">Register Student</button>

</form>

 

<script>

$(document).ready(function () {

    fetchBranches();

 

    // Fetch branches from the database

    function fetchBranches() {

        $.ajax({

            type: 'POST',

            url: 'fetch.php',

            data: { action: 'fetchBranches' },

            success: function (response) {

                let branches = JSON.parse(response);

                let options = '<option value="">Select Branch</option>';

                branches.forEach(function (branch) {

                    options += `<option value="${branch.id}">${branch.name}</option>`;

                });

                $('#branchSelect').html(options);

            }

        });

    }

 

    // Fetch sections based on selected branch

    $('#branchSelect').change(function () {

        let branchId = $(this).val();

        if (branchId) {

            $.ajax({

                type: 'POST',

                url: 'fetch.php',

                data: { action: 'fetchSections', branch_id: branchId },

                success: function (response) {

                    let sections = JSON.parse(response);

                    let options = '<option value="">Select Section</option>';

                    sections.forEach(function (section) {

                        options += `<option value="${section.id}">${section.name}</option>`;

                    });

                    $('#sectionSelect').html(options).prop('disabled', false);

                }

            });

        } else {

            $('#sectionSelect').html('<option value="">Select Section</option>').prop('disabled', true);

            $('#roll_no').val('');

        }

    });

 

    // Fetch roll number based on selected section

    $('#sectionSelect').change(function () {

        let sectionId = $(this).val();

        if (sectionId) {

            $.ajax({

                type: 'POST',

                url: 'fetch.php',

                data: { action: 'fetchRollNumbers', section_id: sectionId },

                success: function (response) {

                    let data = JSON.parse(response);

                    $('#roll_no').val(data.roll_no);

                }

            });

        } else {

            $('#roll_no').val('');

        }

    });

 

    // Form submission

    $('#studentForm').on('submit', function (e) {

        e.preventDefault();

        let formData = $(this).serialize();

 

        $.ajax({

            type: 'POST',

            url: 'register.php',

            data: formData,

            success: function (response) {

                alert(response);

                $('#studentForm')[0].reset();

                $('#sectionSelect').prop('disabled', true);

                $('#roll_no').val('');

                fetchBranches(); // Refresh branches if needed

            }

        });

    });

});

</script>

 

</body>

</html>

 

 

  

 4. register.php - Handle Registration Logic

<?php

include 'db.php';

 

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    // Get the form data

    $name = $conn->real_escape_string($_POST['name']);

    $email = $conn->real_escape_string($_POST['email']);

    $phone = $conn->real_escape_string($_POST['phone']);

    $roll_no = $conn->real_escape_string($_POST['roll_no']);

    $section_id = (int)$_POST['section']; // Cast to int for security

 

    // Prepare the SQL query using the MySQLi query method

    $sql = "INSERT INTO students (name, email, phone, roll_no, section_id) VALUES ('$name', '$email', '$phone', '$roll_no', $section_id)";

 

    // Execute the query

    if ($conn->query($sql) === TRUE) {

        echo "Student registered successfully!";

    } else {

        echo "Error: " . $conn->error;

    }

}

 

// Close the connection

$conn->close();

?> 

 


 5. styles.css - Basic Styles

body {

    font-family: Arial, sans-serif;

    margin: 20px;

}

 

form {

    margin-bottom: 20px;

}

 

input, select {

    margin-right: 10px;

    margin-bottom: 10px;

}

 

button {

    cursor: pointer;

}

 

 How It Works

1.  Database Connection: db.php establishes a connection to the MySQL database using MySQLi.

2.  Data Fetching:

o    fetch.php retrieves branches and sections based on the selected branch using AJAX.

o    When a section is selected, it retrieves the starting roll number and counts existing students in that section to calculate the next roll number.

3.  Frontend Logic:

o    index.php dynamically updates the sections and roll numbers based on the selected branch and section.

o    The form submits data to register.php, which handles the registration of the student in the database.

Running the Project

1.  Place the project folder in the htdocs directory of your XAMPP installation.

2.  Start the XAMPP server.

3.  Access the project via your browser at http://localhost/student_registration/index.php.

This code sets up a basic student registration system with branches, sections, and dynamic roll number assignment using MySQLi and AJAX. 


Download the CODE---->  


Tags

You Might Like

Show more
PHP

Post a Comment

0Comments

Post a Comment (0)
Share to other apps
Copy Post Link