Jump to content

Archived

This topic is now archived and is closed to further replies.

xero.03

Simple help

Recommended Posts

[code]SELECT @frm_id:=b.fid as forum_id, a.fid as cat_id, b.ftitle as forum_title, b.fdesc as forum_desc, b.fmodid as forum_mods, c.poid as lp_id,
c.ponode as lp_node, c.potitle as lp_title, d.uid as lp_posterid, d.username as lp_poster, c.podate as lp_postdate FROM pre_forums a INNER JOIN
pre_forums b ON (a.fid=b.fparent AND a.fparent=0) INNER JOIN pre_posts c ON (c.poforum=b.fid AND c.podate=(SELECT MAX(podate) FROM pre_posts
WHERE poforum=@frm_id)) INNER JOIN pre_users d ON d.uid=c.poposter[/code]

Basically the variable declared in the beginning isn't being passed on to my sub-query in "pre_posts c", is there another way to pass that value or should I just call the last post data with each forum (in that case for each forum my query number will increase creating slightly more overhead, which chouldn't be too bad but I'm looking to avoid that)

EDIT: After alittle consideration it may be better to use a left join (since the forum might not always have posts) but either way my original request is still the same.

Share this post


Link to post
Share on other sites
The problem is using a variable in the same "statement" -- you tend to get rather funny results.

Share this post


Link to post
Share on other sites
I'm running it though PHP, hmmm so I should just call the last post query seperate since there's no way to do it without a set statement?

Share this post


Link to post
Share on other sites
You could use a SET statment...

Share this post


Link to post
Share on other sites
Sorry, I didn't read your first response carefully the first time... why are you using a variable at all here?

Share this post


Link to post
Share on other sites
the join between the last_post data relies on the current fid (forum id) but since the call is being made in a subquery I can't just use the table name and instead am trying to use a variable but the variable doesn't seem to want to work in the subquery

Share this post


Link to post
Share on other sites
Are you sure that you can't reference the original field directly? There shouldn't be a problem, as far as I can see.

Share this post


Link to post
Share on other sites
Nevermind (for the most part) I found a superior solution: preorder tree transversal. Now it's just up to messing around with it awhile and finding some good tutorials, thanks anyways fenway (unless you can teach me non-binary tree transversal ;) ).

Share this post


Link to post
Share on other sites
You mean for storing hierarchal data?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.