用MySQLi回显JOIN SQL表的内容 [英] Echo contents of JOIN SQL tables with MySQLi
问题描述
我在一个系统上工作,并且该模块应该回显数据库的内容.
I'm working on a system, and this module is supposed to echo the contents of the database.
在我添加一些JOIN语句之前,它一直运行良好.
It worked perfectly until I added some JOIN statements to it.
我已经检查并测试了SQL代码,它运行良好.不起作用的是我在其中回显JOINed表的内容的那部分.
I've checked and tested the SQL code, and it works perfectly. What's not working is that part where I echo the content of the JOINed table.
我的代码如下:
$query = "SELECT reg_students.*, courses.*
FROM reg_students
JOIN courses ON reg_students.course_id = courses.course_id
WHERE reg_students.user_id = '".$user_id."'";
$result = mysqli_query($conn, $query);
if (mysqli_fetch_array($result) > 0) {
while ($row = mysqli_fetch_array($result)) {
echo $row["course_name"];
echo $row["course_id"];
course_name和course_id既不回显也不给出任何错误消息.
The course_name and course_id neither echo nor give any error messages.
更新:我实际上需要通过联接更多表并更改选定的列来增加查询的复杂性.我需要加入这些表:
UPDATE: I actually need to increase the query complexity by JOINing more tables and changing the selected columns. I need to JOIN these tables:
tutors
,其中包含列: tutor_id
, t_fname
, t_othernames
,电子邮件
,电话号码
faculty
包含以下列: faculty_id
, faculty_name
, faculty_code
具有以下列的 courses
: course_id
, course_code
, course_name
, tutor_id
, faculty_id
tutors
which has columns: tutor_id
, t_fname
, t_othernames
, email
, phone number
faculty
which has columns: faculty_id
, faculty_name
, faculty_code
courses
which has columns: course_id
, course_code
, course_name
, tutor_id
, faculty_id
我想将这些表联接到原始查询中的 reg_students
表中,以便可以按 $ user_id
进行过滤,并希望显示: course_name
, t_fname
, t_othernames
, email
, faculty_name
I want to JOIN these tables to the reg_students
table in my original query so that I can filter by $user_id
and I want to display: course_name
, t_fname
, t_othernames
, email
, faculty_name
推荐答案
我无法想象 user_info
表对加入JOIN有任何好处,因此我将其删除是合理的猜测.我还假设您所需的列都来自 courses
表,因此我用SELECT中的列名来提名表名.
I can't imagine that the user_info
table is of any benefit to JOIN in, so I'm removing it as a reasonable guess. I am also assuming that your desired columns are all coming from the courses
table, so I am nominating the table name with the column names in the SELECT.
为了便于阅读,我喜欢使用 INNER JOIN
而不是 JOIN
.(它们是相同的野兽)
For reader clarity, I like to use INNER JOIN
instead of JOIN
. (they are the same beast)
将 $ user_id
铸造为整数只是我抛出的最佳做法,以防万一变量是由用户提供/不受信任的输入馈送的.
Casting $user_id
as an integer is just a best practices that I am throwing in, just in case that variable is being fed by user-supplied/untrusted input.
您可以使用 mysqli_num_rows()
计算结果集中的行数.
You count the number of rows in the result set with mysqli_num_rows()
.
如果只想使用关联键访问结果集数据,请使用 mysqli_fetch_assoc()
生成结果集.
If you only want to access the result set data using the associative keys, generate a result set with mysqli_fetch_assoc()
.
使用JOIN编写查询时,为每个表声明别名通常会很有帮助.这在很大程度上减少了代码膨胀和读者负担.
When writing a query with JOINs it is often helpful to declare aliases for each table. This largely reduces code bloat and reader-strain.
未经测试的代码:
$query = "SELECT c.course_name, t.t_fname, t.t_othernames, t.email, f.faculty_name
FROM reg_students r
INNER JOIN courses c ON r.course_id = c.course_id
INNER JOIN faculty f ON c.faculty_id = f.faculty_id
INNER JOIN tutors t ON c.tutor_id = t.tutor_id
WHERE r.user_id = " . (int)$user_id;
if (!$result = mysqli_query($conn, $query)) {
echo "Syntax Error";
} elseif (!mysqli_num_rows($result)) {
echo "No Qualifying Rows";
} else {
while ($row = mysqli_fetch_assoc($result)) {
echo "{$row["course_name"]}<br>";
echo "{$row["t_fname"]}<br>";
echo "{$row["t_othernames"]}<br>";
echo "{$row["email"]}<br>";
echo "{$row["faculty_name"]}<br><br>";
}
}
这篇关于用MySQLi回显JOIN SQL表的内容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!