I'm having trouble figuring out how to implement the sql into my php code for my webpage. This is an assignment unfortunately.
If anyone could help that'd be awesome because I had someone who was going to help me today but they cancelled last minute and I'm still stuck on this.
My Majors area on the page, each major (comp sci, eng, bus) has to be clickable and when they're clicked on, it updates the index.php page and shows the students in the table that have that major. I do use majorID as a foreign key to identify majors. I'm having trouble knowing how to use php to do this.
I was thinking of using this code using a foreach loop, except when I did I get too many errors, one being undefined index and fatal uncaught pdo.
<aside>
<!-- display a list of majors-->
<h2>Majors</h2>
<nav>
<ul>
<?php foreach ($majors as $major) : ?>
<li>
<a href="?majorID=<?php
echo $major['majorID']; ?>">
<?php echo $major['majorname']; ?>
</a>
</li>
<?php endforeach; ?>
</ul>
</nav>
</aside>
This is my .sql code
DROP DATABASE IF EXISTS richard_ricardo_student_db;
CREATE DATABASE richard_ricardo_student_db;
DROP USER 'richardricardo1'@'localhost';
CREATE USER 'richardricardo1'@'localhost' IDENTIFIED BY 'richardisgreat';
GRANT SELECT, INSERT, UPDATE, DELETE ON `richard\_ricardo\_student\_db`.* TO 'richardricardo1'@'localhost';
CREATE table major (
majorID int NOT NULL AUTO_INCREMENT,
majorname varchar(255),
PRIMARY KEY (majorID)
);
CREATE table student (
studentID int NOT NULL AUTO_INCREMENT,
majorID int,
firstname varchar(255),
lastname varchar(255),
gender varchar(10),
PRIMARY KEY (studentID),
FOREIGN KEY (majorID) REFERENCES major (majorID)
);
INSERT INTO major (majorID, majorname)
VALUES (1, "Computer Science"),(2, "Electrical Engineering"),(3, "Business");
INSERT INTO student (studentID, majorID, firstname, lastname, gender)
VALUES (1, 1, "Po", "Black", "M"),(2, 1, "Shifu", "Hoffman", "M"),(3, 1, "Tigress", "Jolie", "F"),(4, 1, "Jennifer", "Yuh", "F"),(5, 1, "Ox", "Storming", "M"),(6, 2, "Monkey", "Chan", "M"),(7, 1, "Viper", "Liu", "F"),(8, 2, "Mantis", "Rogen", "M"),(9, 3, "Crane", "Cross", "M"),(10, 3, "Oogway", "Kim", "M"),(11, 3, "Ping", "Hong", "M");
And this is my index.php code that connects to the sql database on phpmyadmin and my database.php file.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<link rel="stylesheet" href="main.css?v=1">
<title>richard ricardo kung fu school</title>
</head>
<body>
<header>
<h1>Richard Ricardo Kung Fu School - Students</h1>
</header>
<section>
<div id="MajorList">
<h2>Majors</h2>
<div id="MajorPadding">
<?php
include('richard_ricardo_database.php');
$stmt = $pdo->query('SELECT * FROM Major');
while ($row = $stmt->fetch())
{
echo $row['majorname']. "<br><br>";
}
?>
</div>
</div>
<div id="StudentList">
<h2>Student List</h2>
<table class="greyGridTable">
<thead>
<tr>
<th>Student ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Gender</th>
<th></th>
</tr>
<?php
$stmt = $pdo->query('SELECT * FROM Student');
while ($row = $stmt->fetch())
{
echo "<tr><td>".$row['studentID']."</td><td>".strtoupper($row['firstname'])."
</td><td>".strtoupper($row['lastname'])."</td><td>".$row['gender']."
</td><td><a href='richard_ricardo_delete_student.php?id=".$row['studentID']."'>
<button>Delete</button></a></td></tr>";
}
?>
</table>
<br>
<a href="richard_ricardo_add_student_form.php">Add Student</a>
<br>
<br>
<a href="richard_ricardo_add_major_form.php">List / Add Major</a>
<br>
</div>
</section>
<br>
<footer>© 2015 richard ricardo kung fu school</footer>
</body>
</html>
Sorry this is such a long page, I'm also not sure how to insert smaller images. This is how it's supposed to look, if I were to click on computer science as the major for example. Oh also the database.php file uses a pdo object.
