时间戳

首页 > 解决方案 > Arranging data from sql database with php

php - Arranging data from sql database with php

问题描述

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>

My page right now.

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>&copy; 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.

enter image description here

标签： phphtmlcsssqlphpmyadmin

解决方案

如果我正确理解了您的问题，您希望能够看到所选专业的所有学生吗？您的查询将是这样的，然后使用您提供的架构：

SELECT studentID, firstname, lastname, gender
FROM   student
       JOIN major ON student.majorID = major.majorID
WHERE  major.majorID = 2;

这将显示电气工程专业的所有用户

编辑：

由于这是一项任务，因此我将尽我所能提供帮助，而无需为此部分编写确切的代码。因此，对于左侧的专业，您可以将每个专业与一个 ID 相关联，并在单击它时将其发送到页面：

http://localhost/studentmajors.php?major=1

在 PHP 中，您将$_GET "major" 然后继续执行前面的查询以获取 1 的 id，但在执行此类操作时要小心，因为它会打开 SQL 注入。

  $major_id = $_GET['major'];

您将要验证输入。我认为这超出了这个范围。

推荐阅读