php - SELECT 语句中的交叉匹配表

Question

我有表1和表2。在两个表中，1 个字段是通用的。例子:table1 'name' 字段值与 table2 'studentname' 字段值相同。

现在我希望能够通过 SELECT 查询中的交叉匹配来获取数据。

表1

name  |  subject  | 
------+-----------+
Kyle  |  Science  |
John  |  Science  | 
Peter |  Maths    | 

表2

score  |  studentname
-------+-------------
78     | John
89     | Kyle
83     | Peter

这就是我到目前为止所做的。运气不好。

    foreach ($my_con->query("SELECT subject, examDate, table1.name, score, studentname FROM table1 JOIN table2 ON table1.name = table2.studentname SORT BY table2.score DESC") as $result){
            $gtname = $result['name'];
            $gtsubject = $result['subject'];
            $gtScore = $result['score'];
            $gtTIme = $result['date'];

} };

我希望 echo 是这样的:

Kyle   Science  89
JOHN   Science  78

Answer 1

您的查询中的结束引号位置可能导致了该错误。改变这个 -

foreach ($my_con->query("SELECT subject, table1.name, score, studentname FROM table1 JOIN table2 ON table1.name = table2.studentname" SORT BY table2.score DESC) as $result){

对此 -

foreach ($my_con->query("SELECT subject, table1.name, score, studentname FROM table1 JOIN table2 ON table1.name = table2.studentname SORT BY table2.score DESC") as $result){