I need help with a db query function

DRandy

Can't get any help here

igorlopez

I tried to understand your post but believe i failed.
My understanding is that you have two tables

posts:
ID

users:
user_url
previous_post_id

The way your scripts works is that upon registering a new user from an existing post which is identified by: posts:ID with the value postid_a => new users record with the row:
users: user_url = newUser
users: previous_post_id = postid_a

What you now want is that when the new user identified by newUser makes a post one should see who was it that posted the post identified by postid_a.

For me this looks impossible since the posts table where the postid_a stems from has no knowledge of which user made that post.

Now i may be mistaken (been that quite often :o ) but i think you need to have a column in your posts table where you store which user id was responsible.

My design would have been in the line with

posts:
ID : Primary key auto increment
userId : foreign key, refers to record in users

users:
userId : Primary key auto increment
user_url : Name to display
reg_user : userId from posts:userId of the post from which the registration took place

With this design it should be quite easy to populate a user record during registry by selecting userId from posts where ID=postid_a

Good luck

DRandy

Can't get any help

igorlopez

In my posts table, I have many other fields like ID, post_author, post_date, post_title, many others that don't pertain to this.

In my users table, I have ID, previous_post_id, user_url and other fields that don't pertain to this.

Well since i am sort of a newbie i am not the one to say what 'pertain to this' but i am quite sure that it is easier for us to help and for you to get proper answers (instead of me rambling) if we get the whole picture.
In this case it is almost necessary to see the db schema.

Since you now mention more columns maybe it is possible to work something out.

One question regarding your schema:
Is there a relation between table 'posts' field : post_author and table 'users' field ID?

If yes then you have a solution in that you need to find which post_author was responsible for the users previous_post_id by

// The following query should just result in one post since it is the primary key 
$query = "select post_author from posts where ID='$postid_a'";
$result = ... perform query with above query depending on type of db connection
// Extract the user_url from users by
$postAuthor = ... extract the one row out of $result depending on resource type
$query = "select user_url from users where ID='$post_author'";
$result = ... perform query with above query depending on type of db connection
$post_url = ... extract the one row out of $result depending on resource type

Is not exactly proper code but you get the idea.

DRandy

I'll try another forum

DRandy

None

igorlopez

So you got it working? Very good.

Just a few comments to your code snippet.

In your query you are selecting all fields with the * and that is of course ok if you need them. You should be aware of that using the construction

$i = mysql_fetch_row($result);
echo $i[6];

your script will break if for some reason you were to rearrange your fields. A much better way is to use the associative fetch as:

$i = mysql_fetch_assoc($result);
echo $i['user_url'];

if the field user_url is column 6 in your current users table.

Don't forget to mark the thread as closed with the Thread tool.

DRandy

Nothing

igorlopez

HeHe,

This starts to get complicated 🆒

What you are talking about is how to traverse a linked list and since you are only concerned about the referrers maybe it is not a bad idea to have them in your users table instead of previous_post_id (you can always get the previous_post_id from the posts table using the new field referrer)

Lets make an example with letters denominating users.

A--|---------------|
   B--|--|         |
      D  H--|--|   |
            |  K   C--|--|
            J         |  E--|
                      G     F

The users table could then look something like this 
 only user and prev post id (or referrer)
A   NULL  (NULL in both cases)
B    2   (or A with changed table)
C    1   (or A with changed table)
D    4   (or B with changed table)
E    9   (or C with changed table)
F    11  (or E with changed table)
G    13  (or C with changed table) 
H    17  (or B with changed table)
J    18  (or H with changed table)
K    18  (or H with changed table)

And the posts table
only post id and author
1    A
2    A
3    B
4    B
5    C
6    A
7    B
8    D
9    C
10   A
11   E 
12   F
13   C
14   C
15   G
16   A
17   B
18   H
19   B
20   J
21   K
22   A
23   B

The first thing you have to decide is how far up the tree you want to traverse once a user makes a post or if you always want the linked list to go all the way up to the root.
Since each users record contains a pointer (indirectly) to his/hers referrer so you only need to loop through the users table so many numbers of branches you want to climb or break if you reach the root before reaching the limit. Thinking about it i do believe a recursive function is appropriate but i suck at writing them. It is probably doable by plain while construct.
Lets take an example the users K first post.
With your original construction you have to check that he is referred by post 18 so by querying the post table who made post 18 you get user H and that user H were referred by post 17 looking in the users table,...and keep on doing this until ready. If you change your users table and add the referrer as a foreign key to its own user ID you can keep the querys just to the users table.

Maybe there is already some support for this, i don't know.

DRandy

Recursive huh?

Looks like I have some studying to do. I'll come back tomorrow.

Thanks

troybtj

Here is a terribly simple recursive function:

function count($i)
{
$i++;
echo $i;
count($i)
}

That would run forever (assuming you have an infinite bit integer machine)

But the concept is the same for traversing a tree, except you call the same function again with the parent id you find on each "trip" through the function.

Pseudo-code:

function getlinks($post_id)
{
(your SQL Query to get the parent post)
$i++
echo <link to parent URL from query above>
if $i < MaxNumberOfParentLinks {
getlinks($parentpostid);
}
}

DRandy

Hey Thanks

I'll work on it today and see what I can come up with.

I'll let you know.

DRandy

Never Mind

troybtj

Make the function take an argument ($lasturl or something), and if it not empty, do a different query to find the parent URL, and then have the function call itself again with the parent url right after the echo['post_url']; statement.

Otherwise, split out the function so it is always called with a URL for an argument, perhaps by using your code above as a prep function, then calling a new function of getparents($lasturl) which would be recursive, and lookup either the parent 'x' links, or all of them, depending on how far you want to display.

The reason I don't see an immediate/simple rearrangement of your code is you start with a global variable of a numeric post ID, rather than the URL. However, since that function is done, instead of "echo['$posturl'], you would call getparents($posturl) which would then display the original posturl, and it's parents.

Sorry for no proper code here. :o

DRandy

Went Away

troybtj

I'm guessing a bit on your database setup, but something like this. Unsure of the order of the join to find previous post URL in query, but this is close, I think, from previous posts.

Problem is the Global $authordata, need to incorporate that into the query to pull the parent post ID as well as the parent URL (using a join between the users and posts tables), then echo the URL, and call the function with the parent post ID as an argument.

<?php
function  get_parent_url($post_id) {
global $db; //, $authordata; Need to get this from database
//$postid = $authordata->previous_post_id; This will come from query
// Query needs something like LEFT JOIN $db->posts on "post_id=$previous_post_id
$query = "SELECT user_url, previous_post_id FROM $db->users 
 LEFT JOIN $db->posts on "post_id=$previous_post_id "
WHERE previous_post_id='$post_id'";
$result = mysql_query($query) or die(mysql_error()); 
$i = mysql_fetch_assoc($result); 
echo $i['user_url'];
get_parent_url($i['previous_post_id']);
} 
?>

It is passed in a URL, the query will look up the URL of the previous post, echo that URL, then pass parent post ID back to this function, which looks up the URL of the previous post, echos .......

DRandy

None

troybtj

I left out a ; in the SQL string at the end, there needs to be one on either side of the closing "

If you replace the correct table names/columns and touch up the join in my SWAG query, you should end up being able to get the URL from the post table, and the post_id from the user table, and the function will run.

That is a "bare naked" version, there needs to be error checking so it doesn't "or die" when there isn't a parent post, also a limit to the number of parents, which you can increment in a local variable.

DRandy

No help here

No one is giving any direction to my problem

troybtj

Try:

$query = "SELECT user_url, previous_post_id FROM $db->users LEFT JOIN $wpdb->post on  post_id=$previous_post_id  WHERE previous_post_id=$post_id;";

I had too many quotes in there.