PHP：如何从2个表中获取数据

发布于 2025-01-06 17:09:26 字数 398 浏览 2 评论 0原文

如果我使用表“students”中的电子邮件和密码登录，如何从表“data”中获取电子邮件地址匹配的数据？

CREATE TABLE `students` (
  `email` varchar(150) NOT NULL,
  `password` varchar(150) NOT NULL
)

CREATE TABLE `data` (
  `student_id` varchar(50) NOT NULL,
  `studygroup_id` varchar(50) NOT NULL,
  `applied_courses` varchar(50) NOT NULL,
  `study_results` varchar(50) NOT NULL,
  `email` varchar(150) NOT NULL
)

原文

If I log in with my email and password in table 'students', how can I get the data from the table 'data' where the emailadresses match?

CREATE TABLE `students` (
  `email` varchar(150) NOT NULL,
  `password` varchar(150) NOT NULL
)

CREATE TABLE `data` (
  `student_id` varchar(50) NOT NULL,
  `studygroup_id` varchar(50) NOT NULL,
  `applied_courses` varchar(50) NOT NULL,
  `study_results` varchar(50) NOT NULL,
  `email` varchar(150) NOT NULL
)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一场春暖 2025-01-13 17:09:26

嗯，简单的答案就是执行一个查询，例如

SELECT * FROM data WHERE email = '$emailAddress'

$emailAddress 是用于登录的电子邮件地址。

但是您应该真正考虑一下您的架构设计。也许去阅读一些有关基础知识的书籍/教程，您所拥有的可能存在许多问题。您的“学生”表上可能应该有一个数字主键，并将其作为其他表中的外键引用。您还应该考虑重命名第二个表。 “数据”并没有真正描述它的作用；它只是描述了它的用途。数据库中的所有内容（或几乎所有内容）都是数据！另外你所有的 id 列都是 varchars。除非您有字母数字 ID，否则您应该将这些列设置为其所保存数据的正确类型。

Well, the easy answer would just be to execute a query like

SELECT * FROM data WHERE email = '$emailAddress'

Where $emailAddress is the email address that has been used to log in.

But you should really think about your schema design. Perhaps go and read some books/tutorials on the basics and there are a number of possible issues with what you have. You should probably have a numeric primary key on your "students" table and reference this as a foreign key in your other table. You should also think about renaming the second table. "Data" doesn't really describe what it does; everything (or very nearly) in a database is data! Plus all your id columns are varchars. Unless you have alphanumeric ids you should make these columns the correct type for the data they hold.

回复收藏 0 原文

计㈡愣 2025-01-13 17:09:26

请澄清问题。密码从哪里来？ PHP 脚本？

SELECT * FROM data WHERE student.email = "$my_email" AND student.password = "$my_password"

回复收藏 0 原文

一念一轮回 2025-01-13 17:09:26

Students 表还应包含 Student_id，

alter table student add column student_id int auto_increment primary key

然后是查询

select a.email, a.password,b.studentgroup_id, b.applied_course,b.student_result
from student a inner join 
data b 
on a.student_id=b.student_id

Students table should also contain the student_id

alter table student add column student_id int auto_increment primary key

then the query

select a.email, a.password,b.studentgroup_id, b.applied_course,b.student_result
from student a inner join 
data b 
on a.student_id=b.student_id

回复收藏 0 原文

摘星┃星的人 2025-01-13 17:09:26

如果您想确认登录并在一个查询中获取数据，请使用 LEFT JOIN，在下面的示例中，即使 中没有任何内容，它也会为您提供 students 表中的结果该电子邮件地址的数据 表。

$query = "SELECT * FROM `students` 
  LEFT JOIN `data` ON `students`.`email` = `data`.`email` 
  WHERE `students`.`password` = '" . $password . "' 
  AND `students`.`email` = '" . $email. "'";

注意：如果 data 表中的电子邮件地址有多行，则每行都会返回，并且包含相同的 student.password 和 student.email< /代码>值。

If you want to confirm login and get data in one query, use a LEFT JOIN, which in the following example will give you a result from the students table, even if there is nothing in the data table for that email address.

$query = "SELECT * FROM `students` 
  LEFT JOIN `data` ON `students`.`email` = `data`.`email` 
  WHERE `students`.`password` = '" . $password . "' 
  AND `students`.`email` = '" . $email. "'";

Note: if there are multiple rows in the data table for the email address, each row will be returned and will contain identical student.password and student.email values.

回复收藏 0 原文

~没有更多了~