LEFT JOIN question...

[TECK]

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?

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

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.

[Xenon]

Originally posted by TECK
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.

i think it's not worth..
i don't understand why everyone wnats to have a poll on his portal..

[TECK]

i also think like that stefan... i'm curious what others think about it?
btw, i disabled polls on my forums.. it boosts the queries to 22!!! from 17-18.... holly molly!!

[TECK]

ok i was thinking of this piece of code for forumhome:
$threadpolls = $DB_site->query("
SELECT * FROM thread LEFT JOIN poll USING(pollid) WHERE visible=1 AND open<>10 $iforumperms ORDER BY threadid DESC
");
while ($threadpoll=$DB_site->fetch_array($threadpolls)) {
$pollid=$threadpoll[pollid];
$pollinfo=$DB_site->query_first("SELECT * FROM poll WHERE pollid='$pollid'");

$pollinfo[question]=bbcodeparse($pollinfo[question],$threadpoll[forumid],1);
... <more code here
can i skip the extra $pollinfo query? what is the best way to insert it in $threadpolls?
i use a SUM? i'm not sure...

[Xenon]

you have a query in a while loop???
not god not good ;)

but normally this query $threadpolls = $DB_site->query("
SELECT thread.*,poll.* FROM thread LEFT JOIN poll USING(pollid) WHERE visible=1 AND open<>10 $iforumperms ORDER BY threadid DESC
");
is enough, you don't need the extra pollinfo query then..

[TECK]

aha.. i saw the loop that's why i wanted to make sure the way i placed properly the LEFT JOIN.
i left unchanged the code from showthread.php just to get a better idea how i want to insert it onto foreumhome index.php.

so everything is $pollinfo can be replaced with $threadpoll and it will work.. thanks alot stefan for your help..

[Xenon]

yes it'll work with $threadpoll :)

you're welcome floren.

[TECK]

hmm how do i put a AND to grab only the threads with polls on it?
$polls = $DB_site->query("
SELECT thread.*,poll.* FROM thread LEFT JOIN poll USING (pollid) WHERE visible=1 AND open<>10 $iforumperms ORDER BY threadid DESC
");
while ($poll=$DB_site->fetch_array($polls)) {

[Scott MacVicar]

Its not about the number of queries btw

1 big query that joins say 4 or more tables could take more time to execute than 4 seperate queries to each of the tables.

[Erwin]

Having polls on the homepage is more complicated than that. Do you want just results, or the actual voting as well? If you want the ability to vote as well, you need to figure out if the user has voted or not. Also, need to know if the poll is open or closed. I adapted wa jones' poll to be used on my site. It works quite well. 2 queries when the user hasn't voted, and 3 queries when they user votes. (From memory).

[TECK]

i got it working:
if ($poll=$DB_site->query_first("
SELECT * FROM thread LEFT JOIN poll USING (pollid) WHERE visible=1 AND open<>10 $ ipollperms $iforumperms ORDER BY thread.pollid DESC LIMIT 1
")) {
thanks guys. :)

now, the way it goes is like that: if the user have permissions to post a poll, it will grab it from no matter what thread you post it and display it on forumhome. if a new poll is posted, the current poll is automatically replaced with the new one.
my hack adds only 2 queries, voted or not voted. that brings my forumhome page to a total of 15queries... i think i can live with that...

here it is what it looks like, not voted:

[TECK]

and voted...

[Xenon]

looks good floren

[Erwin]

Wow, 2 queries. That's sweet. The code that I use does the same thing, but I'm sure is bulkier. I'll PM you the URL to my site so you can have a look. Just keep it private. :)

[TECK]

np erwin, i can give you the hack... since i consider you a friend, let me know if you are interested... :)
"edit poll" is based on perms so a normal user who doesnt have the right to edit a poll will not see the link. also it has a link to the actual thread to discuss the poll.
i dont plan on releasing it as many other mods i have for my board...

[DrkFusion]

hehe you are releasing bit by biut of your site every day huh?

Its coming together well, I can see you are doing a very good job.
Keep it up, and Im sure, the majority of us are looking forward to the site.

Regards
-Arunan

[Erwin]

Originally posted by TECK
np erwin, i can give you the hack... since i consider you a friend, let me know if you are interested... :)

Thanks, TECK. :)

The code is one of the leanest, meanest and cleanest I have seen. ;)

[Scott MacVicar]

I'l probably end up publishing my entire site backend because i'm kinda getting bored of all the questions on how it was done. I've not got a design for the new version but all the coding is done and its based on the vB3 schema.

Though you'll probably have to re-write your entire site once vB3 appears. I'm going to start making screenshots too i think.

You got an admin panel floren?

[TECK]

i'm ashamed...
is the standard vb look.... :(

since we are here... how improved is the performance in vb3, scott?
i dont know if i'm gonna upgrade... i spent to much time on code customisation on this one and optimised alot the code...
we will see...