Adding Query into showthread.php

[Adrian Schneider]

How would I add :

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]

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]

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]

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 :


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

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

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]

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]

Would having linked tables help here? And could I add the query into this one (if so, how?). I'm really stuck :disappointed:


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

Would having linked tables help here? And could Iadd the query into this one (if so, how?). I'm really stuck :disappointed:


$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


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

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]

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