- <?php
- // Connect to MySQL (replace these values with your actual database credentials)
- $host = "localhost";
- $username = "root";
- $password = "";
- $database = "test";
- $conn = mysqli_connect($host, $username, $password, $database);
- if (!$conn) {
- die("Connection failed: " . mysqli_connect_error());
- }
- // Create database if it doesn't exist
- $sql = "CREATE DATABASE IF NOT EXISTS $database";
- mysqli_query($conn, $sql);
- // Create student table
- $sql = "
- CREATE TABLE IF NOT EXISTS Student (
- student_id VARCHAR(20) PRIMARY KEY,
- name VARCHAR(255) NOT NULL,
- dob VARCHAR(255) NOT NULL,
- gender ENUM('male', 'female') NOT NULL,
- address TEXT NOT NULL,
- mobile VARCHAR(20) NOT NULL,
- email VARCHAR(255) UNIQUE NOT NULL
- );";
- mysqli_query($conn, $sql);
- // Create department table
- $sql = "
- CREATE TABLE Department (
- department_id INT AUTO_INCREMENT PRIMARY KEY,
- name VARCHAR(255) UNIQUE NOT NULL
- );";
- mysqli_query($conn, $sql);
- // Create mapping table
- $sql = "
- CREATE TABLE IF NOT EXISTS Mapping (
- id INT AUTO_INCREMENT PRIMARY KEY,
- student_id VARCHAR(20) NOT NULL,
- department_id INT NOT NULL,
- UNIQUE KEY (student_id, department_id),
- FOREIGN KEY (student_id) REFERENCES Student(student_id),
- FOREIGN KEY (department_id) REFERENCES Department(department_id)
- );";
- mysqli_query($conn, $sql);
- // Process form submission
- if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['create'])) {
- // Retrieve form data
- $name = $_POST["name"];
- $student_id = $_POST["student_id"];
- $dob = $_POST["dob"];
- $gender = $_POST["gender"];
- $address = $_POST["address"];
- $mobile = $_POST["mobile"];
- $email = $_POST["email"];
- $departments = $_POST["department"];
- // Insert student data into the Student table
- $sql = "INSERT INTO Student (student_id, name, dob, gender, address, mobile, email)
- VALUES ('$student_id', '$name', '$dob', '$gender', '$address', '$mobile', '$email')";
- if (mysqli_query($conn, $sql) === TRUE) {
- echo "Student record created successfully";
- } else {
- echo "Error creating student record: " . mysqli_error($conn);
- }
- $sql = "INSERT IGNORE INTO Department (name) VALUES ('$departments')";
- mysqli_query($conn, $sql);
- $sql = "SELECT department_id FROM Department WHERE name='$departments'";
- $result = mysqli_query($conn, $sql);
- $row = mysqli_fetch_assoc($result);
- $departmentId = intval($row['department_id']);
- // Create Mapping
- $insertMappingQuery = "INSERT INTO Mapping (student_id, department_id) VALUES ('$student_id', '$departmentId')";
- mysqli_query($conn, $insertMappingQuery);
- }
- // Get all Student details
- if (
- $_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['all_students'])
- ) {
- $student_id = $_GET["student_id"];
- $sql = "SELECT s.student_id, s.name, s.dob, s.gender, s.address, s.mobile, s.email, GROUP_CONCAT(d.name) AS departments
- FROM Student s
- LEFT JOIN Mapping m ON s.student_id = m.student_id
- LEFT JOIN Department d ON m.department_id = d.department_id
- GROUP BY s.student_id";
- $result = mysqli_query($conn, $sql);
- $students = array();
- if (mysqli_num_rows($result) > 0) {
- while ($row = mysqli_fetch_assoc($result)) {
- $students[] = $row;
- }
- }
- echo json_encode($students);
- }
- // Get Student dtails by ID
- if ($_SERVER["REQUEST_METHOD"] == "GET" && isset($_GET['student_by_id'])) {
- $student_id = $_GET["student_id"];
- $sql = "SELECT s.student_id, s.name, s.dob, s.gender, s.address, s.mobile, s.email, GROUP_CONCAT(d.name) AS departments
- FROM Student s
- LEFT JOIN Mapping m ON s.student_id = m.student_id
- LEFT JOIN Department d ON m.department_id = d.department_id
- WHERE s.student_id = '$student_id'
- GROUP BY s.student_id";
- $result = mysqli_query($conn, $sql);
- $students = array();
- if (mysqli_num_rows($result) > 0) {
- while ($row = mysqli_fetch_assoc($result)) {
- $students[] = $row;
- }
- }
- echo json_encode($students);
- }
- // Delete Student details by ID
- if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['delete'])) {
- $student_id = $_POST['student_id'];
- // Check if the student ID exists
- $check_student_sql = "SELECT * FROM Student WHERE student_id = '$student_id'";
- $result = mysqli_query($conn, $check_student_sql);
- if (mysqli_num_rows($result) > 0) {
- // Student ID exists, proceed with deletion
- // Delete mapping data from the Mapping table
- $delete_mapping_sql = "DELETE FROM Mapping WHERE student_id = '$student_id'";
- if (mysqli_query($conn, $delete_mapping_sql)) {
- // Delete student data from the Student table
- $delete_student_sql = "DELETE FROM Student WHERE student_id = '$student_id'";
- if (mysqli_query($conn, $delete_student_sql)) {
- echo "Student record deleted successfully";
- } else {
- echo "Error deleting student record: " . mysqli_error($conn);
- }
- } else {
- echo "Error deleting mapping records: " . mysqli_error($conn);
- }
- } else {
- echo "Student ID not found";
- }
- }
- // Update Student details
- if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['update'])) {
- $name = $_POST["name"];
- $student_id = $_POST["student_id"];
- $dob = $_POST["dob"];
- $gender = $_POST["gender"];
- $address = $_POST["address"];
- $mobile = $_POST["mobile"];
- $email = $_POST["email"];
- $departments = $_POST["department"];
- // Update student data in the Student table
- $sql = "UPDATE Student SET name='$name', dob='$dob', gender='$gender', address='$address', mobile='$mobile', email='$email' WHERE student_id='$student_id'";
- if (mysqli_query($conn, $sql) === TRUE) {
- echo "Student record updated successfully";
- } else {
- echo "Error updating student record: " . mysqli_error($conn);
- }
- // Delete existing mapping data from the Mapping table
- $delete_mapping_sql = "DELETE FROM Mapping WHERE student_id='$student_id'";
- mysqli_query($conn, $delete_mapping_sql);
- // Insert new mapping data into the Mapping table
- foreach ($departments as $department) {
- $department_query = "SELECT department_id FROM Department WHERE name = '$department'";
- $result = mysqli_query($conn, $department_query);
- $department_row = mysqli_fetch_assoc($result);
- $department_id = $department_row['department_id'];
- $sql = "INSERT INTO Mapping (student_id, department_id) VALUES ('$student_id', '$department_id')";
- if (mysqli_query($conn, $sql) === FALSE) {
- echo "Error creating mapping record: " . mysqli_error($conn);
- }
- }
- }
- mysqli_close($conn);
- ?>
[text] fvbfiufvnef
Viewer
*** This page was generated with the meta tag "noindex, nofollow". This happened because you selected this option before saving or the system detected it as spam. This means that this page will never get into the search engines and the search bot will not crawl it. There is nothing to worry about, you can still share it with anyone.
Editor
You can edit this paste and save as new: