workflow

CREATE DATABASE result_management;

USE result_management;

CREATE TABLE students (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  address VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE subjects (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE results (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id INT(11) UNSIGNED NOT NULL,
  subject_id INT(11) UNSIGNED NOT NULL,
  marks INT(3) UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

Here's the PHP script to retrieve student and subject information

We can create a separate file for database connection and include it in your other PHP files to avoid repeating the same code.

Here's an example of a database connection file named "db_connect.php":

<?php
// Database connection variables
$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "result_management_system";

// Create a database connection
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

// Check the connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

You can include this file in your PHP scripts using the include or require statement

Here's the code that includes the database connection file:

<?php
// Include the database connection file
require_once('db_connect.php');

// Query to retrieve all student information from the "students" table
$student_query = "SELECT * FROM students";

// Query to retrieve all subject information from the "subjects" table
$subject_query = "SELECT * FROM subjects";

// Execute the student query
$student_result = mysqli_query($conn, $student_query);

// Execute the subject query
$subject_result = mysqli_query($conn, $subject_query);

// Store the results in PHP variables
$students = mysqli_fetch_all($student_result, MYSQLI_ASSOC);
$subjects = mysqli_fetch_all($subject_result, MYSQLI_ASSOC);

// Close the database connection
mysqli_close($conn);
?>

Note that the require_once('db_connect.php'); line includes the file containing the database connection code. Make sure to update the file name and path to match the location of your db_connect.php file.

In above two script, we first connect to the database using the mysqli_connect() function. We then execute two separate SQL queries to retrieve all the student information and subject information from their respective tables using the mysqli_query() function. The results of these queries are stored in PHP variables using the mysqli_fetch_all() function.

Finally, we close the database connection using the mysqli_close() function. Note that the database connection details such as host, username, password, and database name may vary depending on your specific configuration.