LEFT JOIN question...

i have this query:

$threads=$DB_site->query("
  SELECT * FROM thread WHERE open=1 AND open<>10 ORDER BY lastpost DESC
");
while ($thread=$DB_site->fetch_array($threads)) {

what i try to do is to associate with the $thread[userid] it's actual usergroupid. so i need to find a way to enter something like that:

if ($thread[pollid] and in_array($user[usergroupid], array(5, 6))) {

instead of:

if ($thread[pollid] and in_array($thread[postuserid], array(1, 5))) {

without adding a query. there is a way... using a JOIN.
what is the best aproach? thanks for reading this.

[TECK]

ok i did this...

$threads=$DB_site->query("
  SELECT * FROM thread " . iif($thread[pollid], 'LEFT JOIN user ON (user.userid = thread.postuserid)', '') . "
   WHERE open=1 AND open<>10 $iforumperms ORDER BY lastpost DESC LIMIT 5
");

if ($thread[pollid] and in_array($thread[usergroupid], array(5, 6))) {

i know that's not good because i need to grab the threadstarter id. postuserid will not give me much. is enough that someone posts to the thread and my poll is invisible...
i think i'm gonna have to end with adding a usergroupid field to table thread.

let me know id there are ways arround. thanks.

[Xenon]

postuserid is the userid of the threadstarter Floren, just lastposter changes when someone replies to a thread.

[TECK]

aha.. thanks for the tip.
but still doesnt work stefan... the code above will make dissapear my poll on forumhome page.
however, if i switch back to:

if ($thread[pollid] and in_array($thread[postuserid], array(1, 5, 18))) {

everything is back to normal... any solutions?
also the code i inserted on the main page boosts the no. of queries to 15, instead of 13.. so what do you think?? should i toss it?

i hate the fact that the poll adds 2 extra queries on forumhome..
but is nice to have it there... give some spice to the site.

[Xenon]

well, where do you get the $thread[pollid] value in your iif-clause?

are you sure it's already set?

try to use this code:

$threads=$DB_site->query("
  SELECT thread.*,user.usergroupid FROM thread LEFT JOIN user ON (user.userid = thread.postuserid)
   WHERE open=1 $iforumperms ORDER BY lastpost DESC LIMIT 5
");

btw. i have modified your where clause, it's not nessesary to use open<>10 when you already have open=1


well 15 queries is not so much, i don't see if you really need two, perhaps one is enough. Can you post the full part of the script?

[TECK]

lol.. you are right.. hehe. oupssssssssss.. let me try it.. i'll get back to you... wait for me 5 min.

thanks stefan, that didnt worked.. i tried even that before..
i forgot to mention it.. i think i'm just gonna toss it. they can live without a poll on forumhome.. i hate that after all this work it will raise my no. of queries 15.

but thanks for your help..

[Xenon]

lool, your right, they can surely live without a poll

well just a question, i'm sure you've done that, but who know sometimes everyone can forgot the importanst thing..

you have used $thread=$DB_site->fetch_array($threads) have ya?

[TECK]

yep. always.
it was working perfectly with the $thread[postuserid] = 1

also, are you sure about the open<>10 ? i learn it from vB this.. to avoid the moved threads...

[Scott MacVicar]

you just want the usergroup of the person correct?

[sql]SELECT thread.*, user.*, usergroup.usergroupid FROM thread,user,usergroup WHERE thread.postuserid=user.userid AND user.usergroupid=usergroup.usergroupid AND thread.open = 1 ORDER BY thread.lastpost DESC[/sql]

though you not wanting to specify a specific thread or at least a limit?

[TECK]

yep... that's the one scott... thank you
however, i think i'm gonna pass on it because if i want to insert the polls on the home page, it will add 3 whooping queries to my main page, wich i hate.

what i try to do is add grab the polls from a specific thread (it's working great i have it displayed) but that adds 2 queries (actually 3) to my total.

i currently have 13queries when everything is loaded, on forumhome.
do you think is worth adding a poll on the home page? just to spice up the look? most portals have that... although my homepage will not look at all like a portal.