I need help with writing a function

[DRandy]

This is what I'm trying to do:

 

Let's say I have content (post1) that's been published by a registered author to a blog.

 

(post1) has a unique id that's found in the databse table (posts) in the field (ID).

 

The author of (post1) has a unique id that's found in the same table (posts) in the field (post_author).

 

The author of (post1) has a unique url found in the database table (users) in the field (user_url).

 

In the table (users) there is also a field called (newpostid), which holds an id of a totally separate post (post2) published by a different author (author2).

 

What I want to do is be able to get author2's (user_url) that published (post2) and show it in (post1).

 

I've been able to show the id of post2 within post1 but that's as far as I've got. I've been struggling with this for a couple weeks now.

 

Can someone help me figure this out and get me going in the right direction?

 

I'm somewhat familiar with php and able to manipulate code that is already written.

 

But this really has me stumped.

 

I'm using MySQL with phpMyAdmin. I don't need to worry about connecting to my database because that has already been done. I think I need to do something like SELECT * FROM ... but I don't understand how to write the complete function for this.

 

Thanks for your help.

[mcmuney]

You inquiry doesn't make much sense. If I understand you correctly, authorA is posting in posts table as post1 and authorB is posting in users table as post2, but you want to display authorB's user_url and post2 with post1?

 

What I think you're trying to do is store original post in the posts table and user comments in the users table. If that assumption is correct, then you need to use a LEFT join function to connect the posts table with the users table using a common field. For example, if the post id for post1 is 1 in table posts, then you need to have a field in users table where users comments for post1 is also 1. This will allow you to join the two tables using both fields where postid=1.

[DRandy]

The way I have it set right now is Author 2 registers through Author 1's post. That post's id becomes the connection. So when Author 2 makes a post, Author 1's url is displayed.

 

So I'm doing something like:

 

function get_the_url() {
$query = "SELECT 
  a.*, 
  b.user_url 
FROM 
  posts AS a, 
  posts AS b 
WHERE 
  a.id = 'id of post1' AND 
  a.newpostid = b.id";

$result = $wpdb->query($query);

return $result;
}

 

But I can't get it to work.

 

Maybe I'm missing something.

 

Thanks for your help.