我已经做了2天了，现在转向stackoverflow寻求帮助。我试图为"故事"的每个用户的写作返回display_name，其中故事(SID)可以有多个在其上工作的用户(即这里称为WID的多个写作id)。
一切正常，除了显示名称总是相同的每一篇文章。当我对$getauthor执行var_dump()时，我简单地获得wp_users中所有display_name的数组。但是我需要的只是返回作者的显示名，作者在写作表中为那个故事(SID)写了一篇文章(WID)， SID在写作和故事表中。
更清楚一点:当有人开始写一篇博客文章(称为故事)时，就会在故事中为该用户ID创建一条带有新SID的记录。然后，当人们向该SID添加内容时，每个添加内容都会在写入表中获得具有相同SID的WID记录。我的代码所做的是为所有结果(编写的片段)返回SID的原始作者，而实际上应该发生的是每个WID应该具有添加该片段的作者。
它应该看起来像:
Author: Kate
This is my blog
Author: Mike
This is my addition.
但是它看起来是:
Author: Kate
This is my blog
Author: Kate
This is my addition.
DB架构如下:
wp_users表:
ID
display_name
故事表:
SID
ID
story_name
书写表:
WID
ID
SID
approved
text
和代码:
<?php
global $wpdb;
$user_ID = get_current_user_id();
// get the SID
$the_SID = ( isset( $_GET['writing'] ) ) ? $_GET['writing'] : false;
$results = $wpdb->get_results("
SELECT wp_users.ID, wp_users.display_name,
stories.ID, stories.SID, stories.story_name, stories.category,
writing.ID, writing.text, writing.approved
FROM stories
JOIN wp_users ON stories.ID = wp_users.ID
JOIN writing ON stories.SID = writing.SID
WHERE (stories.SID = $the_SID) AND (writing.approved = 'Y')");
$sql = "SELECT display_name FROM wp_users where ID = ". $results->writing.ID .";";
$getauthor = $wpdb->get_results($sql);
$author = $getauthor[0]->display_name;
?>
<?php
foreach ($results as $result) {
echo "<br>" . "Author: " . $author . "<br>" . $results[0]->text . "<br>";
}
?>
这是您的查询:
SELECT wp_users.ID, wp_users.display_name,
----------------^
stories.ID, stories.SID, stories.story_name, stories.category,
---------------^
writing.ID, writing.text, writing.approved
---------------^
FROM stories JOIN
wp_users
ON stories.ID = wp_users.ID JOIN
writing
ON stories.SID = writing.SID
WHERE (stories.SID = $the_SID) AND (writing.approved = 'Y');
标识的列都有相同的名称，ID。您应该为它们定义单独的别名:
SELECT wp_users.ID as userID, wp_users.display_name,
stories.ID as storiesID, stories.SID, stories.story_name, stories.category,
writing.ID as writingID, writing.text, writing.approved
FROM stories JOIN
wp_users
ON stories.ID = wp_users.ID JOIN
writing
ON stories.SID = writing.SID
WHERE (stories.SID = $the_SID) AND (writing.approved = 'Y');
那么这可能会起作用:
$sql = "SELECT display_name FROM wp_users where ID = ". $results->writingID .";";
但是，您可以在相同的查询中得到您想要的，只需再次加入wp_users:
SELECT u.ID as userID, u.display_name,
s.ID as storiesID, s.SID, s.story_name, s.category,
w.ID as writingID, w.text, w.approved,
uw.display_name as writer_name
FROM stories s JOIN
wp_users u
ON s.ID = u.ID JOIN
writing w
ON s.SID = w.SID JOIN
wp_users uw
ON w.ID = uw.ID
WHERE (s.SID = $the_SID) AND (w.approved = 'Y');