CODEWITHSUNDEEP
phpsql
user5000
user5000

Reputation: 129

Can't get query to reference user name in third table

I've been at it for 2 days and now turning to stackoverflow for help. I am trying to return the display_name for each piece of writing by a user for a "story" where the story (SID) can have multiple users who worked on it (that is, multiple writing IDs known here as WID).

Everything works except that the display name is always the same for every piece of writing. When I do a var_dump() for $getauthor I simple get an array of all the display_names in wp_users. But what I need is to return just the display name for the author who wrote the one piece of writing (the WID) in the writing table for that story (SID) which is in the writing and stories tables.

A little more clarity: When someone starts a blog post (called a story), that creates a record in stories for that user ID with a new SID. Then when people add to that SID, each addition is given a WID record in the writing table with the same SID. What my code is doing is returning the ORIGINAL author of the SID for all results (pieces of writing), when in fact what should happen is that each WID should have the author that added that piece.

It should look like:

Author: Kate
This is my blog
Author: Mike
This is my addition.

But what it looks like is:

Author: Kate 
This is my blog
Author: Kate
This is my addition.

Here is the DB architecture:

wp_users table:

ID
display_name

Stories table: 

SID
ID
story_name

Writing table: 

WID
ID 
SID
approved
text

And the code:

<?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>";
}
?>

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

This is your query:

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');

The identified columns all have the same name, ID. You should define separate aliases for them:

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');

Then perhaps this will work:

$sql = "SELECT display_name FROM wp_users where ID = ". $results->writingID .";";

However, you could get what you want in the same query, just by joining in the wp_users again:

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');

Upvotes: 1

Related Questions