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.

[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: