填充数据库中两个不同表的下拉列表

I'm trying to populate dropdowns from two different tables in the database, which are joined together in a 3rd table using the ids from the first two. I used the first answer here as a guide for the dropdowns.

我正在尝试从数据库中的两个不同的表中填充下拉列表,这些表在第3个表中使用前两个中的ID连接在一起。我在这里使用了第一个答案作为下拉菜单的指南。

The tables: courses -- id, title; classes -- id, title; classes_to_courses -- coursesID, classesID

表格:课程 - 身份证,职称;类 - id,title; classes_to_courses - coursesID,classesID

The code:

<?php
include("global.php");
doHeader();
$currentCourseClass = "SELECT id, title FROM courses";
$rsCurrentCourseClass = mysql_query($currentCourseClass);

while($get_row = mysql_fetch_assoc($rsCurrentCourseClass)) {
$categories[] = array("id" => $get_row['id'], "val" => $get_row['title']);
}

$currentCourseClass2 = "SELECT cl.id as classid, cl.title as classtitle, cr.id as courseid FROM classes_to_courses c2c LEFT JOIN classes cl ON c2c.coursesID = cl.id LEFT JOIN courses cr ON c2c.classesID = cr.id";

 $rsCurrentCourseClass2 = mysql_query($currentCourseClass2);


 while($get_row = mysql_fetch_assoc($rsCurrentCourseClass2)) {
 $subcats[$get_row['courseid']][] = array("id" => $get_row['classid'], "val" => $get_row['classtitle']);
 }
/**/
$jsonCats = json_encode($categories);
$jsonSubCats = json_encode($subcats);


?>

My issue seems to be with the second query, $currentCourseClass2. How do I select the courseid from the joined table classes_to_courses so that my array is set up properly?

我的问题似乎是第二个查询$ currentCourseClass2。如何从已连接的表classes_to_courses中选择courseid,以便正确设置我的数组?

Thanks.

1 个解决方案

#1

Not sure what error you are receiving, but it looks like your join may be wrong. In the code snippet, you have:

不确定您收到了什么错误,但看起来您的加入可能是错误的。在代码段中,您有:

SELECT 
    cl.id as classid, 
    cl.title as classtitle
    cr.id as courseid 
FROM classes_to_courses c2c 
LEFT JOIN classes cl ON c2c.coursesID = cl.id 
LEFT JOIN courses cr ON c2c.classesID = cr.id";

So you are trying to join the classes table using the c2c.coursesID, and vice-versa for the courses table.

因此,您尝试使用c2c.coursesID加入classes表,反之亦然使用courses表。

Instead, shouldn't you be joining the classes table based on the c2c.classesID and the courses table on the c2c.coursesID?

相反,你不应该根据c2c.classesID和c2c.coursesID上的courses表加入classes表吗?

SELECT 
    cl.id as classid, 
    cl.title as classtitle
    cr.id as courseid 
FROM classes_to_courses c2c 
LEFT JOIN classes cl ON c2c.classesID = cl.id 
LEFT JOIN courses cr ON c2c.coursesID = cr.id";

#1