vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)
-   -   Adding Query into showthread.php (https://vborg.vbsupport.ru/showthread.php?t=75368)

Adrian Schneider 01-29-2005 03:02 PM
Adding Query into showthread.php
 
How would I add :

PHP Code:
if ($threadid == 1)
{
$gettitle = mysql_query("SELECT * from USER where USERNAME='$post[title]'");
$postloser = mysql_fetch_array($gettitle);
} 
...into showthread.php to be defined while in the post stuff.

(So in each post it will work depending on what the title is (which will be someones username).

I tried looking for a place to stick it in, but of course the vb code for that page is much more complicated than mine. :ermm:

Andreas 01-29-2005 03:07 PM
You should not use mySQL-functions directly - use the class provided by vBulletin.
Furthermore, this would be highly inefficient, as it adds a query for every post.

Adrian Schneider 01-29-2005 03:11 PM
Okay I'll try to explain what I'm doing so hopefully one of you can give me a push in the right direction.

In the given thread the title input is a dropdown of the member list, then when they post it sets the title as that. I added into the postbit 'Accept' / 'Decline' game buttons, and the accept one takes the post username as winner, and the post title as loser. I need a way for it to recognize the rating//losses (2 fields in user table) of username (posttitle). I have everything working except this. :(

Would there be a way to do this without running a query per post?

miz 01-29-2005 11:20 PM
huh ?
i got you all worng
are you tring to make like vote systen ?
that users will vote who is the winner of the game ?

if you want to use the query you provided (NOT RECOMANED)
here is the syntax :

PHP Code:
if ($thread[threadid] == 1)
{
$gettitle = $DB_site->query("SELECT * from ".TABLE_PREFIX."USER where USERNAME='$thread[threadpostername]'");
while ($postloser = $DB_site->fetch_array($gettitle)
{
// do ++++ here
this query will get all users  
}
} 
or if you want to get only 1 user
user

PHP Code:
 $gettitle = $DB_site->query_first("SELECT * from ".TABLE_PREFIX."USER where USERNAME='$thread[threadpostername]'"); 
offcurse everything depends on what you want to do
also its might be able that $thread values will be $threadinfo

test on your own risk.

Adrian Schneider 01-29-2005 11:30 PM
Thanks, its actually per post not thread, so I need to get it while it does the post queries. --anyone?

What's the difference between using the $DB_site-> and regular mysql_query?

miz 01-30-2005 12:03 AM
form my expriance, if you using more then 1 db table
then its get mixed up - happend to me before

also i think its more safe - not sure..

Adrian Schneider 01-30-2005 03:01 AM
Would having linked tables help here? And could I add the query into this one (if so, how?). I'm really stuck :disappointed:

PHP Code:
        $posts = $DB_site->query("
                SELECT
                        post.*, post.username AS postusername, post.ipaddress AS ip,
                        user.*, userfield.*, usertextfield.*,
                        " . iif($forum['allowicons'], 'icon.title as icontitle, icon.iconpath,') . "
                        " . iif($vboptions['avatarenabled'], 'avatar.avatarpath, NOT ISNULL(customavatar.avatardata) AS hascustomavatar, customavatar.dateline AS avatardateline,') . "
                        " . iif($vboptions['reputationenable'], 'level,') . "
                        " . iif(!$deljoin, 'NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,') . "
                        editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
                        editlog.reason AS edit_reason,

                        post_parsed.pagetext_html, post_parsed.hasimages,
                        IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
                        " . iif(!can_moderate(), $datastore['hidprofilecache']) . "
                FROM " . TABLE_PREFIX . "post AS post
                LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = post.userid)
                LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
                LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
                " . iif($forum['allowicons'], "LEFT JOIN " . TABLE_PREFIX . "icon AS icon ON(icon.iconid = post.iconid)") . "
                " . iif($vboptions['avatarenabled'], "LEFT JOIN " . TABLE_PREFIX . "avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " . TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid = user.userid)") .
                        iif($vboptions['reputationenable'], " LEFT JOIN " . TABLE_PREFIX . "reputationlevel AS reputationlevel ON(user.reputationlevelid = reputationlevel.reputationlevelid)") . "
                " . iif(!$deljoin, "LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type = 'post')") . "
                LEFT JOIN " . TABLE_PREFIX . "editlog AS editlog ON(editlog.postid = post.postid)
                LEFT JOIN " . TABLE_PREFIX . "post_parsed AS post_parsed ON(post_parsed.postid = post.postid)
                WHERE $postids
                ORDER BY dateline $postorder 
Sorry if this is against the rules (posting so much of the vb code)

miz 01-30-2005 12:52 PM
Quote:
Originally Posted by TheSpecialist
Would having linked tables help here? And could Iadd the query into this one (if so, how?). I'm really stuck :disappointed:

PHP Code:
        $posts = $DB_site->query("
                SELECT
                        post.*, post.username AS postusername, post.ipaddress AS ip,
                        user.*, userfield.*, usertextfield.*,
                        " . iif($forum['allowicons'], 'icon.title as icontitle, icon.iconpath,') . "
" . iif($vboptions['avatarenabled'], 'avatar.avatarpath, NOTISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline,') . "
                        " . iif($vboptions['reputationenable'], 'level,') . "
" . iif(!$deljoin, 'NOT ISNULL(deletionlog.primaryid) AS isdeleted,deletionlog.userid AS del_userid, deletionlog.username AS del_username,deletionlog.reason AS del_reason,') . "
                        editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
                        editlog.reason AS edit_reason,

                        post_parsed.pagetext_html, post_parsed.hasimages,
                        IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
                        " . iif(!can_moderate(), $datastore['hidprofilecache']) . "
                FROM " . TABLE_PREFIX . "post AS post
                LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = post.userid)
                LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
                LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
                " . iif($forum['allowicons'], "LEFT JOIN " . TABLE_PREFIX . "icon AS icon ON(icon.iconid = post.iconid)") . "
" . iif($vboptions['avatarenabled'], "LEFT JOIN " . TABLE_PREFIX ."avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " .TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid =user.userid)") .
iif($vboptions['reputationenable'], " LEFT JOIN " . TABLE_PREFIX ."reputationlevel AS reputationlevel ON(user.reputationlevelid =reputationlevel.reputationlevelid)") . "
" . iif(!$deljoin, "LEFT JOIN " . TABLE_PREFIX . "deletionlog ASdeletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type= 'post')") . "
                LEFT JOIN " . TABLE_PREFIX . "editlog AS editlog ON(editlog.postid = post.postid)
                LEFT JOIN " . TABLE_PREFIX . "post_parsed AS post_parsed ON(post_parsed.postid = post.postid)
                WHERE $postids
                ORDER BY dateline $postorder 
Sorry if this is against the rules (posting so much of the vb code)


try this 1

PHP Code:
        $posts = $DB_site->query("
                SELECT
                        post.*, post.username AS postusername, post.ipaddress AS ip,
                        user.*, userfield.*, usertextfield.*,
                        " . iif($forum['allowicons'], 'icon.title as icontitle, icon.iconpath,') . "
" . iif($vboptions['avatarenabled'], 'avatar.avatarpath, NOTISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline,') . "
                        " . iif($vboptions['reputationenable'], 'level,') . "
" . iif(!$deljoin, 'NOT ISNULL(deletionlog.primaryid) AS isdeleted,deletionlog.userid AS del_userid, deletionlog.username AS del_username,deletionlog.reason AS del_reason,') . "
                        editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
                        editlog.reason AS edit_reason,

                        post_parsed.pagetext_html, post_parsed.hasimages,
                        IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
                        " . iif(!can_moderate(), $datastore['hidprofilecache']) . "
                FROM " . TABLE_PREFIX . "post AS post
                LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = post.userid)
                LEFT JOIN " . TABLE_PREFIX . "user AS winners ON(user.username = post.username)
                LEFT JOIN " . TABLE_PREFIX . "userfield AS userfield ON(userfield.userid = user.userid)
                LEFT JOIN " . TABLE_PREFIX . "usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
                " . iif($forum['allowicons'], "LEFT JOIN " . TABLE_PREFIX . "icon AS icon ON(icon.iconid = post.iconid)") . "
" . iif($vboptions['avatarenabled'], "LEFT JOIN " . TABLE_PREFIX ."avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN " .TABLE_PREFIX . "customavatar AS customavatar ON(customavatar.userid =user.userid)") .
iif($vboptions['reputationenable'], " LEFT JOIN " . TABLE_PREFIX ."reputationlevel AS reputationlevel ON(user.reputationlevelid =reputationlevel.reputationlevelid)") . "
" . iif(!$deljoin, "LEFT JOIN " . TABLE_PREFIX . "deletionlog ASdeletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type= 'post')") . "
                LEFT JOIN " . TABLE_PREFIX . "editlog AS editlog ON(editlog.postid = post.postid)
                LEFT JOIN " . TABLE_PREFIX . "post_parsed AS post_parsed ON(post_parsed.postid = post.postid)
                WHERE $postids
                ORDER BY dateline $postorder 
and its will be under winners
i think its should work not sure - not tested.

Adrian Schneider 01-30-2005 04:52 PM
Thanks I think that is what I needed.

Where it says 'user AS winners ON.....' is winners supposed to be the field name, table name, or what. If someone could finish this off for me I would be very happy. :)

I need the losers rating and loss count (user.losses and user.rating) where the loser = post.title.

miz 01-30-2005 04:56 PM
filed name is winners
just add 1 more but i dont think you should combined it in this query as you might get info you dont want.
if you want to use the query above then modify it and add the info you need
then check if you get the right values, i dont have time to test it
so you can do it yourself as i showed you the right way to go
if you will have problems please post here again


All times are GMT. The time now is 10:35 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01776 seconds
  • Memory Usage 1,832KB
  • 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_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (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
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete