vb.org Archive - LEFT JOIN question...

Page 1 of 3

Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- Member Archives (https://vborg.vbsupport.ru/forumdisplay.php?f=202)

- - LEFT JOIN question... (https://vborg.vbsupport.ru/showthread.php?t=43956)

TECK	09-26-2002 11:37 AM

LEFT JOIN question...

i have this query:

Code:

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

Code:

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

instead of:

Code:

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	09-26-2002 12:03 PM

ok i did this...

Code:

$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

09-26-2002 01:08 PM

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

TECK	09-26-2002 01:19 PM

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:

Code:

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

09-26-2002 01:33 PM

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:

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	09-26-2002 01:35 PM

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

09-26-2002 01:56 PM

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	09-26-2002 01:57 PM

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

09-27-2002 09:57 AM

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	09-27-2002 05:29 PM

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.

All times are GMT. The time now is 09:54 AM.

Page 1 of 3

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01229 seconds Memory Usage 1,735KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (6)bbcode_code_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (2)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: