Jump to content


Photo

Simple help


  • Please log in to reply
9 replies to this topic

#1 xero.03

xero.03
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 15 October 2006 - 06:27 PM

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

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 October 2006 - 09:30 PM

The problem is using a variable in the same "statement" -- you tend to get rather funny results.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 xero.03

xero.03
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 15 October 2006 - 09:52 PM

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?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 October 2006 - 11:52 PM

You could use a SET statment...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 xero.03

xero.03
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 17 October 2006 - 04:06 PM

Examples?

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 October 2006 - 04:17 PM

Sorry, I didn't read your first response carefully the first time... why are you using a variable at all here?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 xero.03

xero.03
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 17 October 2006 - 04:42 PM

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

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 October 2006 - 10:10 PM

Are you sure that you can't reference the original field directly? There shouldn't be a problem, as far as I can see.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 xero.03

xero.03
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 20 October 2006 - 02:30 AM

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

#10 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 October 2006 - 04:08 PM

You mean for storing hierarchal data?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users