Connect.php
<?php
$servername = "localhost";
$username = "username";
$password = "pw";
$db = "dbname";
// Create connection
$conn = mysqli_connect($servername, $username, $password,$db);
// Run and Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully"; // this is only for testing purposes and can be removed or commented
?>
index.php
<html>
<body>
<!-- Connect to DB -->
<?php
include('connect.php');
?>
<!-- Start html content -->
<h1>Sport/Activity Nominations System</h1>
<ul>
<li><a href ="student_form.php">Add a new Student</a></li>
<li><a href ="nom_form.php">Add a new Activity Nomination</a></li>
</ul>
<h2>All Nominations</h2>
<!-- Start PHP to Print a Table -->
<?php
//sql statement with two inner joins
$sql = "SELECT p_students.first,p_students.last,p_students.gender,p_students.year_level,p_activities.activity_name
from p_nominations
inner join p_students on p_nominations.student_id = p_students.student_id
inner join p_activities on p_nominations.activity_id = p_activities.activity_id
order by p_students.year_level,p_students.last";
//run query and store results in memory
$result = mysqli_query($conn, $sql);
//if results were found then print out
if (mysqli_num_rows($result) > 0) {
echo '<table width = 500px border ="1">';
echo '<tr><th>First</th><th>Last</th><th>Gender</th><th>Year</th><th>Activity</th>';
// output data of each row using a while statement to loop through array
while($row = mysqli_fetch_assoc($result)) {
echo '<tr><td>'. $row['first'] .'</td><td>'.$row['last'].'</td><td>'.$row['gender'].'</td> <td>'.$row['year_level'].'</td><td>'.$row['activity_name'].'</td></tr>';
}
echo '</table>';
}
?>
<!-- end php -->
</body>
</html>
student_form.php
<?php
include('connect.php');
?>
<html>
<head>
</head>
<body>
<!-- start form -->
<h1>Sport Nominations Form - Add Student</h1>
<form action="addStudentProcess.php" method="post">
First name: <input name ="first" maxlength="30" type="text" required><br>
Last name: <input name ="last" type="text"><br>
Gender: <select name="gender">
<option value="M">Male</option>
<option value="F">Female</option>
</select><br>
Year Level: <select name="year">
<option value=7>Year 7</option>
<option value=8>Year 8</option>
<option value=9>Year 9</option>
<option value=10>Year 10</option>
<option value=11>Year 11</option>
<option value=12>Year 12</option>
</select><br>
<!-- //submit button -->
<input type ="submit">
</form>
<!-- end form -->
</body>
</html>
addStudentProcess.php
<?php
include ('connect.php');
//get values from post
//good practice to validate these values first
$first = $_POST['first']);
$last = validate($_POST['last']);
$gender = $_POST['gender'];
$year = intval($_POST['year']); //intval will cast to integer
//check data for no special characters
function validate($data){
$data = htmlspecialchars($data);
$data = trim($data);
return $data;
}
//insert them into DB using SQL statement
$sql = "INSERT INTO p_students(first, last, year_level, gender) VALUES ('$first', '$last', $year, '$gender')";
if(mysqli_query($conn, $sql)){
echo "Records added successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
//we can now redirect to another page once completed
header('location:index.php');
?>
nom_form.php
<html>
<body>
<?php
include('connect.php');
?>
<h1> Student Nomination Form </h1>
<form action="addNomProcess.php" method="post">
<!-- Dynamic Drop down box of sports which looks up values from db for students -->
Select Student: <select name ="student">
<!-- Start PHP -->
<?php
//sql statement
$sql = "select * from p_students order by last asc";
//run query and store results in memory
$result = mysqli_query($conn, $sql);
//if results were found then print out
if (mysqli_num_rows($result) > 0) {
// output data of each row using a while statement to loop through array
while($row = mysqli_fetch_assoc($result)) {
echo '<option value = ' . $row['student_id'] . '>'
. $row['first'].' '.$row['last'].'</option>';
}
}
?>
<!-- End PHP -->
</select><br>
<!-- Dynamic Drop down box of sports which looks up values from db for activities -->
Sport: <select name ="activity">
<!-- Start PHP -->
<?php
//sql statement
$sql = "select * from p_activities order by activity_name asc";
//run query and store results in memory
$result = mysqli_query($conn, $sql);
//if results were found then print out
if (mysqli_num_rows($result) > 0) {
// output data of each row using a while statement to loop through array
while($row = mysqli_fetch_assoc($result)) {
echo '<option value = ' . $row['activity_id'] . '>' . $row['activity_name'].'</option>';
}
}
?>
<!-- End PHP -->
</select><br>
<input type ="submit">
</form>
</body>
</html>
addNomProcess.php
<?php
include ('connect.php');
$student = intval($_POST['student']); //intval will cast to integer
$activity = intval($_POST['activity']); //intval will cast to integer
//check data for no special characters
function validate($data){
$data = htmlspecialchars($data);
return $data;
}
//insert them into DB using SQL statement
$sql = "INSERT INTO p_nominations(student_id,activity_id) VALUES ($student,$activity)";
if(mysqli_query($conn, $sql)){
echo "Records added successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
//we can now redirect to another page once completed
header('Location: index.php');
?>
import.php - see reading file for flowchart
<?php
include('connect.php');
//check to see if form sent and submit button value pressed
if(isset($_POST["submit"])){
// access FILES go to item file and access name of stored file
if($_FILES['file']['name']){
//explode the form name file and access its uploaded filename from the name property i.e. data.csv
$filename = explode(".",$_FILES['file']['name']);
//check to see if file has a csv extension_loaded
if($filename[1] =='csv'){
//create a handler to read through file r
$handle =fopen($_FILES['file']['name'],"r");
//read through the document line by line and grab each cell and store in variable
while($data =fgetcsv($handle)){
//assign value from CSV to variable from row and check for SQl injection
//mysqli_real_escape_string is a built in method to validate and remove unwanted characters
$student_id = mysqli_real_escape_string($conn, $data[0]);
$activity_id = mysqli_real_escape_string($conn, $data[1]);
//you could build a function to test value ranges etc. = extension work
//create a sql statement with variables for insert
$sql = "insert into p_nominations(student_id, activity_id) values ($student_id,$activity_id)";
//run query using functional procedure ->
if(mysqli_query($conn, $sql)){
echo("New record added");
}else{
echo $sql." ". $conn->error;
}
}
//close the file handler
fclose($handle);
//let the user know they are done via JS notification prompt
echo("<script>alert('import done')</script>");
}
}
}
?>
<html>
<body align="center">
<!-- Start HTML Page -->
<h3 align="center">Import Sport Nominations Data from CSV</h3>
<p> File type: CSV only</p>
<p>Two Columns - student_id and activity_id</p>
<!-- Multipart form allows file uploads -->
<form method="POST" enctype="multipart/form-data">
<label>Select CSV File:</label>
<!-- input type file and name = file which will be referenced above in PHP -->
<input type ="file" name="file"/>
</br>
<input type="submit" name="submit" value="Import"/>
</form>
</body>
</html>