OLA 6: HTML, CSS, PHP, and MySQL
— Student Database Project

Course: CSCI 4410/5410 Web Technologies

Instructor: Dr. Xin Yang
Due date: 11:59 pm April 24th, 2025 (Thursday)

Objective:

In this assignment, you will build a dynamic PHP application connected to a MySQL database. Your task is to create an interactive web page where users can view, insert, update, and delete student records stored in a MySQL database. You will implement PHP-MySQL database connection using object-oriented programming, run SQL queries to manage student data, and display results in a well-formatted HTML table styled with CSS. By completing this project, you will strengthen your understanding of database creation, SQL data manipulation, server-side scripting with PHP, and dynamic web page generation in a real-world database-driven application.

Note: Always test your SQL queries carefully and handle any missing data such as NULL values properly when displaying student information.

Step 1: Create Your Database, Table, and Insert Data (16 Points)

In this step, you will write all the SQL code needed to create your database, create your table, and insert student data into a single SQL file.

(1) Open a text editor (such as Notepad) and write your SQL code to complete the following tasks:

♣ Create a database named CSCI4410.

♣ Create a table named Students with the following fields:
    - ID (INT, Primary Key, Auto_Increment)
    - Name (VARCHAR)
    - BlueCard (VARCHAR)
    - Major (VARCHAR)
    - ClassLevel (VARCHAR)
    - Email (VARCHAR)
    - Gender (VARCHAR)
    - Age (INT)
    - Phone (VARCHAR)

♣ Insert the following 10 student records into your Students table:

('John Doe', '01234567', 'Computer Science', 'Freshman', 'DoeJohn@mtsu.edu', 'Male', 19, '123-456-7890'),
('Jane Doe', '07654321', 'Mathematics', 'Senior', 'DoeJane@mtsu.edu', 'Female', 22, NULL),
('Mary Mia', '09872345', 'Music', 'Senior', 'MaryMia@mtsu.edu', 'Female', 22, '615-123-3344'),
('Michael Jame', '07234589', 'Business', 'Junior', 'MichaelJame@mtsu.edu', 'Male', 20, '615-232-1155'),
('Daniel Jack', '04135892', 'Computer Science', 'Sophomore', 'DanielJack@mtsu.edu', 'Male', 19, '615-333-2266'),
('Lucy Kate', '72358924', 'Computer Science', 'Freshman', 'LucyKate@mtsu.edu', 'Female', 18, '976-111-4567'),
('Lauren Spade', '05896294', 'Computer Science', 'Senior', 'LaurenSpade@mtsu.edu', 'Female', 22, '756-222-1478'),
('Emma Vivian', '67451144', 'Mathematics', 'Sophomore', 'EmmaVivian@mtsu.edu', 'Female', 20, '546-333-7459'),
('Ada Lane', '66655544', 'Art', 'Junior', 'AdaLane@mtsu.edu', 'Female', 19, '765-777-2255'),
('Alan Parker', '88833322', 'Business', 'Senior', 'AlanParker@mtsu.edu', 'Male', 24, '999-222-5588')

(2) Save your complete SQL script as Students.sql

(3) Open phpMyAdmin and import your Students.sql file to create your database, table, and records automatically.

Important: You must complete all database creation, table creation, and data insertion inside your SQL file — not manually through phpMyAdmin.

Step 2: Create Your PHP File and Build the Interactive Page (70 Points)

In this step, you will create a dynamic PHP web page that connects to your database and allows users to perform specific database operations by clicking different buttons.

(1) Create a PHP file named index.php.

(2) Set up a connection to your database using object-oriented PHP with the new mysqli() constructor.

Example connection code:

$servername = "localhost";
$username = "root";
$password = "";
$database = "CSCI4410";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

(3) Design a simple HTML form with **separate buttons** for each database task. Each button should trigger one specific query when clicked.

Required Tasks:

♣ Create a button labeled "Display All Students".
→ When clicked, select and display all student records from the Students table.

♣ Create a button labeled "Display Male Students".
→ When clicked, select and display only the male students.

♣ Create a button labeled "Display Female Students".
→ When clicked, select and display only the female students.

♣ Create a button labeled "Display Students Older Than 21".
→ When clicked, select and display students whose age is greater than 21.

♣ Create a button labeled "Count Distinct Majors".
→ When clicked, display the number of different majors represented among the students.

♣ Create a button labeled "Display Students Without Phone Numbers".
→ When clicked, select and display students whose Phone field is NULL.

♣ Create a button labeled "Insert New Student".
→ When clicked, insert a new student record into the Students table. After insertion, display a success or error message.

♣ Create a button labeled "Delete Student".
→ When clicked, it will allow you to enter the BlueCard ID of the student you want to delete. Then click submit to delete. After deletion, display a success or error message.

♣ Create a button labeled "Update Phone Number".
→ When clicked, it will allow you to enter the BlueCard ID of the student you want to update and enter the new phone number, then click submit to update.

Hints: HTML Button Example:

<form method="post">
  <button name="display_all">Display All Students</button>
  <button name="display_male">Display Male Students</button>
  ...
</form>

Hints: Detect which button is clicked using PHP.

if (isset($_POST['display_all'])) {
   $sql = "SELECT * FROM Students";
   $result = $conn->query($sql);
   // Call the function to display table
    displayTable($result);
}

Hints: Create additional forms when users want to insert, delete or update data.
PHP Insert New Student Form Example:

if (isset($_POST['show_insert_form'])) {
    echo '<form method="post">';
    echo '<input type="text" name="Name" required>';
    ......
    echo '<button type="submit" name="insert_student">Insert</button>';
    echo '</form>';
}

Hints: Create a Reusable "Display Table" Function
To make your PHP code cleaner and avoid writing the same table code many times, you can define a helper function.

    function displayTable($result) {
        if ($result->num_rows > 0) {
            //code to display table 
        }
        else {
            echo "No records found.";
        }
    }

Important Requirements:

♣ Each button should submit the form using the POST method.
♣ Use PHP to detect which button was clicked using isset($_POST['button_name']).
♣ Display all query results in a neatly formatted HTML table with proper borders.
♣ Apply CSS styling from your ola6.css file for clean presentation.

(4) After completing all operations, make sure to close the database connection properly at the bottom of your PHP file using:

$conn->close();

Note: Your index.php page should feel like a small interactive control panel where clicking each button performs a live database action and shows immediate results!

Step 3: Create and Apply Your CSS File (10 Points)

In this step, you will create a CSS stylesheet to improve the appearance of your database web page.

(1) Create a CSS file named ola6.css.

(2) Style your webpage to make it clear, professional, and user-friendly. Apply styles to the following elements:

♣ Set a background color for the body of your page.

♣ Choose an easy-to-read font family for your text (example: Arial, Verdana, sans-serif).

♣ Style your HTML table with:

    Visible table borders
    Padding inside table cells
    Background colors for table headers
    Hover effects for table rows (optional but encouraged)

♣ Style your buttons with:

    Background color
    Text color
    Padding and margins
    Hover effects to change button appearance when the mouse is over it

(3) Link your CSS file correctly inside your index.php file in the <head> section. Use the following full and proper syntax:

<link type="text/css" rel="stylesheet" href="ola6.css">

Note: Your buttons and tables should look neat, organized, and visually pleasant. Good design will make your application easier to use and will earn you full points for this section!

Step 4: Submission Instructions

Please submit the following files in D2L:

♣ Students.sql
♣ index.php
♣ ola6.css

Important: Test everything locally using XAMPP before submission!

Grading:

Part	Points
Database and SQL (Students.sql)	16 Points
PHP Code and Queries (index.php)	70 Points
CSS Design (ola6.css)	10 Points
AI Disclaimer	4 Points

Need Help?

If you have any questions, please email Xin.Yang@mtsu.edu or stop by my office in ROTC annex 113E.

⇧

OLA 6: HTML, CSS, PHP, and MySQL — Student Database Project

Course: CSCI 4410/5410 Web Technologies