vb.org Archive - LEFT JOIN question...

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.

Xenon

09-27-2002 05:32 PM

Quote:

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

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

ok i was thinking of this piece of code for forumhome:

Code:

$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

09-27-2002 06:12 PM

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

but normally this query

Code:

$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	09-27-2002 06:21 PM

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

09-27-2002 06:28 PM

yes it'll work with $threadpoll :)

you're welcome floren.

TECK	09-27-2002 08:52 PM

hmm how do i put a AND to grab only the threads with polls on it?

Code:

$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

09-27-2002 10:47 PM

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

09-28-2002 12:49 AM

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	09-28-2002 04:04 AM

1 Attachment(s)

i got it working:

Code:

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	09-28-2002 07:40 AM

1 Attachment(s)

and voted...

Xenon

09-28-2002 09:08 AM

looks good floren

Erwin

09-28-2002 01:09 PM

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	09-28-2002 09:34 PM

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

09-29-2002 02:05 AM

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

09-29-2002 08:26 AM

Quote:

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

09-29-2002 01:25 PM

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

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

All times are GMT. The time now is 09:59 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01085 seconds Memory Usage 1,802KB 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 (10)bbcode_code_printable (2)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (28)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 bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: