使用php从sql数据库安排数据

发布时间：2020-12-13 16:10:59 所属栏目：PHP教程来源：网络整理

导读：我无法弄清楚如何将sql实现到我的网页的 PHP代码中.不幸的是,这是一项任务. 如果有人能提供帮助那就太棒了,因为我今天有人会帮助我,但他们最后一刻取消了,我仍然坚持这一点. 页面上的My Majors区域,每个主要区域(comp sci,eng,bus)都必须是可点击的,当它们被

我无法弄清楚如何将sql实现到我的网页的 PHP代码中.不幸的是,这是一项任务.

如果有人能提供帮助那就太棒了,因为我今天有人会帮助我,但他们最后一刻取消了,我仍然坚持这一点.

页面上的My Majors区域,每个主要区域(comp sci,eng,bus)都必须是可点击的,当它们被点击时,它会更新index.php页面并显示表格中具有该专业的学生.我确实使用majorID作为外键来识别专业.我很难知道如何使用php来做到这一点.

我正在考虑使用foreach循环来使用此代码,除非我犯了太多错误,一个是未定义的索引和致命的未捕获的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.

这是我的.sql代码

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"),"Shifu","Hoffman","Tigress","Jolie","F"),(4,"Jennifer","Yuh",(5,"Ox","Storming",(6,2,"Monkey","Chan",(7,"Viper","Liu",(8,"Mantis","Rogen",(9,3,"Crane","Cross",(10,"Oogway","Kim",(11,"Ping","Hong","M");

这是我的index.php代码,它连接到phpmyadmin上的sql数据库和我的database.php文件.

<!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>

对不起,这是一个很长的页面,我也不知道如何插入较小的图像.如果我点击计算机科学作为主要的例子,这就是它应该看的样子.哦,database.php文件也使用了一个pdo对象.

enter image description here

解决方法

如果我正确理解您的问题,您希望能够看到所选专业的所有学生吗？您的查询将是这样的,然后使用您呈现的模式：

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

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

编辑：

由于这是一项任务,我将尽力帮助,而无需为此部分编写确切的代码.因此,对于左侧的专业,您可以将每个人与ID和send it to the page相关联：

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

在PHP中,你将$_GET“major”,然后继续前面的id为1的查询,但是在做这样的事情时要小心,因为它打开了SQL注入.

$major_id = $_GET['major'];

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!