Creating School Database System
Create a database MySQL
1. Click Databases
2. Create Databases
3. Add a unique name ex. student_records
4. Click Create
Create a table
1. Add Name and Number of columns
2. Create entities/column names
3. Name the first column as ID
6. Birthday
• Null Index – set as primary key
7. Gender
4. Add First_Name (no spacing) 8. Enrolled_Date
5. Last_name (no spacing)
Add data to your Database MySQL
3 way to add data
1. Add data manually
Go to INSERT tab then type the information needed.
2. Go to SQL tab
Click Insert
Then add values
3. Go to IMPORT tab
Then you can import here the database you have created from
other computer applications ex. Microsoft Excel (.csv)
Displaying Data and Creating connection
1. Go to Local Disk (C:)
2. Open XAMPP
3. Open htdocs
4. Create a folder ex. school_registrar
VS code
1. Open the folder in VS code or any code editor.
2. Create these folders:
• CSS
• JS
• Connections
• images
• index.php
• Check if your file is working
• In browser type
http://localhost/school_database
Name of your folder
Create variables (index.php) VS code
1. $host = "localhost"
2. $username = "root"
3. $password = "p@ssw0rd02"
4. $database = "students_database“
5. $con = new mysqli($host, $username, $password, $database);
Add echo to check errors
1. if($con->connect_error){
echo $con->connect_error;
}
Add query
2. $sql = "SELECT * FROM list_of_students ORDER BY id DESC";
Declare student’s variable
3. $students = $con->query($sql) or die ($con->error);
4. Add row variable for the query result
$row = $students->fetch_assoc();
VS code
(add echo if you want to display result)
5. Add do while loop
do{
echo $row[‘First_name']."_".$row[‘Last_Name’]; “ <br/>”;
}while($row = $students->fetch_assoc());
?>
( run to see result)
Add HTML
<body>
<h1> Students Information </h1>
<br/>
<a href="insert.php"> Insert New </a>
<table>
<thead>
<tr>
<th></th>
<th>First Name</th>
<th>Last Name</th>
</tr>
</thead>
<tbody>
Add HTML
<?php do{ ?>
<tr>
<!-- <td><a href="details.php">view</a</td> -->
<td><a href="details.php?ID=<?php echo $row['ID'];?>">view</a></td>
<td><?php echo $row['First_Name']; ?></td>
<td><?php echo $row['Last_Name']; ?></td>
</tr>
<?php }while($row = $students->fetch_assoc()) ?>
</tbody>
</table>
</body>
</html>
*You can add styles in CSS folder
Creating Function and Insert Form
1. Open Connections
folder.
2. Add connection.php
file then write these
codes
index.php
1. Write the following:
include_once("connections/connection.php");
$con = connection();
• Insert.php file
This is from MySQL
database INSERT
insert.php file
index.php
1. Write the following:
<a href="insert.php"> Insert New </a>
2. Run
3.
Creating User Accounts and Access
Add a log in page (ex. login.php)
1. Create a login.php file
2. Copy the codes from index.php
3. Delete $sql To check if the codes are working
4. Delete <body> content successfully try to write an echo.
5. Write $_SESSION
>if (isset($_POST['login'])){
6. Write isset
echo "Login";
}
if(!isset($_SESSION)){
session_start();
}
login.php <body>
Create another table in MySQL
1. Create a table name ex. (user_accounts)
2. Add entities/columns names
• ID
• username
• password
• access
3. Add data for Administrator and Users using INSERT TAB
• Username
• Password
• Access
Then click BROWSE to check.
VS CODE
Add query to get password and username from the database.
if (isset($_POST['login'])){
// echo "Login";
$username = $_POST['username'];
$password = $_POST['password'];
$sql = "SELECT * FROM student_users WHERE username = '$username' AND password =
'$password' ";
}
login.php
Create logout.php file
index.php
Create
details.php file
• Copy the codes from
index.php
• Delete <body>
content
• In SESSION,
Change userlogin to
Access
index.php
• Link the details.php file
<td> <a href="details.php">view</p</td>
*Run
*It will display view and if you log in as Administrator it will allow you to see the
details.
Using GET method add a parameter
In index.php file write the following codes.
• <td><a href="details.php?ID=<?php echo $row[''];?>">view</a></td>
*ID – is from the URL
* Run and hover the view you will be able to see the ID number of the selected student.
In details.php write the following codes to create query.
These are from the database
Adding delete and edit features
In details.php file
• Add edit and delete links
<a href="edit.php?ID=<?php echo $row['ID'];?>">Edit</a>
<a href="delete.php">Delete</a>
<br/>
Creating edit features
1. Add edit.php file
2. Copy codes from insert.php
3. Paste in edit.php (to copy the same form)
*Run
*You don’t see any information because we need to pass the ID to
edit.php.
In edit.php
$id = $_GET['ID'];
$sql = "SELECT * FROM list_of_students WHERE ID ='$id'";
$students = $con->query($sql) or die ($con->error);
$row = $students->fetch_assoc();
In details.php
• <a href="edit.php?ID=<?php echo $row['ID'];?>">Edit</a>
In edit.php
$id = $_GET['ID'];
$sql = "SELECT * FROM list_of_students WHERE ID ='$id'";
$students = $con->query($sql) or die ($con->error);
$row = $students->fetch_assoc();
In edit.php
In edit.php
In edit.php
• Add query
Change INSERT to UPDATE
Change index.php to details.php
*Run
*First name and last name are already updating
In edit.php
• Create options for gender
In details.php
• Add back and
delete button
<input type="text" name="ID" value="<?php echo $row['id'];?>">
• Then run
In edit.php
Change submit to update
In delete.php
• Run to check functionality or try print_r($_POST);
• You will be able to see the delete and ID number
Add a query to link in your database
In delete.php Add echo header to link index page
In details.php
• You can hide id details
<input type="hidden" name="ID" value="<?php echo $row['ID'];?>">
• And the delete button
<input type="hidden" name="ID" value="<?php echo $row['ID'];?>">
Try to hide delete button in user account or
proceed to the next step.
