Is there a way..

[DragonBlade]

Okays, seems I made a spelling error AND added in an un-needed comma. XP

I tested this out on my forum and it works.



PLUGIN #1:

PHP Code:

// Query to check if/what threadowner's marital status is
$marital_status_threadids = array();
$marital_status_threads_query = sprintf("SELECT thread.threadid, userfield.field17 AS maritalstatus FROM thread LEFT JOIN userfield ON(thread.postuserid=userfield.userid) WHERE thread.threadid IN (0%s)", $ids);
$marital_status_threads_result = $vbulletin->db->query_read($marital_status_threads_query);
while ($marital_status_threads_row = $vbulletin->db->fetch_array($marital_status_threads_result))
  {$marital_status_threadids[$marital_status_threads_row['threadid']] = $marital_status_threads_row['maritalstatus'];} 


NOTE: The "," in the "WHERE thread.threadid IN" statement is now omitted.
NOTE2: I spelled "status" as "statis" in one variable. XD Oopsie.

See how that works out for ya.

[chris1979]

I'm following along because I want to do something similar.

Question: what customfield is this fetching? The code looks like it's for customfield6 - is that right? I'm confused because you also mentioned displaying field17.

What would I need to change to fetch a different customfield?

[DragonBlade]

Eeeeek! field6 was for me testing it on my boards. XP I'll go edit that for 17.

But yes, that's the gist of it. :3

[chris1979]

Field6 is actually what I want for my forum. You are probably psychic.

Now I have two other questions:

1) I want to fetch two fields - field6 and field7, and display them both. (It's firstname / surname.) Do I need to have two plugins or can I edit the code to fetch both at once?

2) I am using the advanced threadbit mod and I think there's a conflict. When the mod is enabled, the second plugin (to displaying the customfield) doesn't have anything to hook to. (It works fine when I turn the advanced threadbit mod off.) Is it possible to just insert the code into my template? Something like $marital_status?

[DragonBlade]

1)

It's actually BETTER to fetch both at once. :3

Notice the query line,

PHP Code:

$marital_status_threads_query = sprintf("SELECT thread.threadid, userfield.field17 AS maritalstatus FROM thread LEFT JOIN userfield ON(thread.postuserid=userfield.userid) WHERE thread.threadid IN (0%s)", $ids); 


For you to have both fields fetched, try

PHP Code:

$my_query = sprintf("SELECT thread.threadid, userfield.field6 AS thisfield, userfield.field7 AS thatfield, FROM thread LEFT JOIN userfield ON(thread.postuserid=userfield.userid) WHERE thread.threadid IN (0%s)", $ids); 


Now, to make those values USEABLE, try this code:

PHP Code:

$my_field6_threadids = array();
$my_field7_threadids = array();
$my_query = sprintf("SELECT thread.threadid, userfield.field6 AS thisfield, userfield.field7 AS thatfield FROM thread LEFT JOIN userfield ON(thread.postuserid=userfield.userid) WHERE thread.threadid IN (0%s)", $ids);
$my_result = $vbulletin->db->query_read($my_query);
while ($my_row = $vbulletin->db->fetch_array($my_result))
{
  $my_field6_threadids[$my_row['threadid']] = $my_row['thisfield'];
  $my_field7_threadids[$my_row['threadid']] = $my_row['thatfield'];
} 


Now, to make the variable easy to access in the templates, use THIS instead for your plugin in "threadbit_display":

PHP Code:

$my_field6 = $my_field6_threadids[$thread['threadid']];
$my_field7 = $my_field7_threadids[$thread['threadid']]; 


2)

No worries! Using those values above, go into your "threadbit" template, and just plug in the values wherever the hell you want. :3

For example, this is USUALLY (without modifications) the part of the threadbit template that is the FIRST column:

        <td class="alt1" id="td_threadstatusicon_$thread[realthreadid]">
                $thread[openclose_editable]
                <img src="$stylevar[imgdir_statusicon]/thread$thread[statusicon].gif" id="thread_statusicon_$thread[realthreadid]" alt="<if condition="$show['threadcount']"><phrase 1="$thread[dot_count]" 2="$thread[dot_lastpost]">$vbphrase[have_x_posts_in_thread_last_y]</phrase></if>" border="" />
        </td>

If I wanted to put your Field 6 in that column, I'd just do it as so:

        <td class="alt1" id="td_threadstatusicon_$thread[realthreadid]">
                $thread[openclose_editable]
                $my_field6
                <img src="$stylevar[imgdir_statusicon]/thread$thread[statusicon].gif" id="thread_statusicon_$thread[realthreadid]" alt="<if condition="$show['threadcount']"><phrase 1="$thread[dot_count]" 2="$thread[dot_lastpost]">$vbphrase[have_x_posts_in_thread_last_y]</phrase></if>" border="" />
        </td>

Bada bing, bada boom!



Now, I've never used this advanced threadbit thing, but it PROBABLY uses the threadbit template, so... yar. :3

The BEST way to go about it is to avoid editing your templates directly if you can avoid it. But if you can't... well, that's what the template editor is there for. :3

[chris1979]

It works!!!!!!!!!!!!!!!!

I have been trying to do this for ages so thanks - I would never have worked it out without your help.

--------------- Added [DATE]1246285171[/DATE] at [TIME]1246285171[/TIME] ---------------

Next I am going to work out how to do the same for the list of private messages

[DragonBlade]

Good luck, and if you need help, don't hesitate to ask. :3

[chris1979]

Thanks!

I might never leave you alone now.

On threadbit, do you know what I'd need to change to also get the last poster's cutomfields? (The first code works for the first poster - so I want something else for the last post column.)

For the list of private messages, do you know what code I should use there?

[DragonBlade]

For the last poster, you'd need another query, slightly different from the first. Unfortunately, by default, vBulletin only stores the last poster's NAME, and not their user ID, so we have to look them up by name. (Well, not TOO unfortunate, I suppose, but working with numbers is so much easier, IMO.)

Anyways, THAT query would look something like this:

PHP Code:

$my_lastposter_query = sprintf("SELECT thread.threadid, userfield.field1 AS myfield1, userfield.field2 AS myfield2, userfield.field3 AS myfield3, userfield.field4 AS myfield4 FROM thread LEFT JOIN userfield ON(thread.lastposter=user.username) LEFT JOIN userfield ON(user.userid=userfield.userid) WHERE thread.threadid IN (0%s)", $ids); 


Note the extra JOIN in there--that's because the "userfield" table doesn't have the "username", and the "thread" table doesn't include the last poster's "userid".

This COULD potentially be bad on performance, but I honestly don't know just how bad it would be. The alternative option would be to add a field to the thread table "lastpostuserid", re-code the thread update to make it so the userid of the last poster is included in the update (like the username is already), and then update all threads on your forum. That's the BEST way to do it, but is much more complex, and if you have a lot of threads on your forum, it could take quite a while to update them all. (If you did NOT update them, though, they would become updated the next time someone posted in them.) That way, we can reduce it to a single JOIN.



Anyways, I'm blabbing on about insignificant details, probably.

For the Private Messages... Well, lemme take a looksee and I'll post again in a few.

--------------- Added [DATE]1246291796[/DATE] at [TIME]1246291796[/TIME] ---------------

Whoo, boy, PMs are a different ballgame, here.

As with the last poster bit for threads, we had to JOIN three tables together here: pm, pmtext, and userfield. See, the User ID we WANT is who the PM is from. This information is only on the pmtext table. However, two and a half things we need to sift the results by are only on the pm table, and not the pmtext table. (The "half" is that "$readstatus" varaible that is sometimes show and sometimes not, depending upon the user's input.) Finally, the data we want to pull in order to display is on the userfield table. Thus, the three JOINed tables.

Next bit of complication arose because there wasn't a decent place (read: "hook location") to put a plugin, so we had to improvise by pre-running a few bits of code that will run AGAIN after the query. (See the comment in the code itself.)



All righty. The "hook location" we'll be using for this plugin is "private_messagelist_filter". I'm sure by now you can fill out all the other fields on your own, except for the Plugin Code, which is below:

PHP Code:

// ***************************** vB Stuff *************************************
// TECHIE   ** The lines below are actually already run in the vB page.      **
//    NOTE: ** However, they are run AFTER our hook, and we need them run    **
//          ** BEFORE we create our query.  Basically, these will get        **
//          ** executed twice.  That's okay, though, they're not really      **
//          ** resource-intensive or anything.                               **
// ****************************************************************************
sanitize_pageresults($totalmessages, $vbulletin->GPC['pagenumber'], $vbulletin->GPC['perpage'], $vbulletin->options['pmmaxperpage'], $vbulletin->options['pmperpage']);
$startat = ($vbulletin->GPC['pagenumber'] - 1) * $vbulletin->GPC['perpage'];
$perpage = $vbulletin->GPC['perpage'];
$pagenumber = $vbulletin->GPC['pagenumber'];
$need_sql_calc_rows = ($search['searchtitle'] OR $search['searchuser'] OR $search['startdate'] OR $search['enddate'] OR $search['read']);
$readstatus = array(0 => '', 1 => '= 0', 2 => '> 0', 3 => '< 2', 4 => '= 2');
$readstatus = ($search['read'] == 0 ? '' : 'AND pm.messageread ' . $readstatus[$search['read']]);
// *************************** End vB Stuff ***********************************


// ***************************** Our Stuff ************************************
// TECHIE   ** For each field you want, you'll want an array to capture the  **
//    NOTE: ** value and assign it to the array with the KEY being the pmid  **
//          ** value and the VALUE being the field value.                    **
// ****************************************************************************
$my_field1_pmids = array();
$my_field2_pmids = array();
$my_field3_pmids = array();
$my_field4_pmids = array();
$pm_userfield_query = sprintf("
  SELECT " . ($need_sql_calc_rows ? 'SQL_CALC_FOUND_ROWS' : '') . "
    pm.pmid,
    userfield.field1 AS myfield1,
    userfield.field2 AS myfield2,
    userfield.field3 AS myfield3,
    userfield.field4 AS myfield4
    " . iif($vbulletin->options['privallowicons'], ", icon.title AS icontitle, icon.iconpath") . "
  FROM pm
  LEFT JOIN pmtext ON(pmtext.pmtextid=pm.pmtextid)
  LEFT JOIN userfield ON(userfield.userid=pmtext.fromuserid
  " . iif($vbulletin->options['privallowicons'], "LEFT JOIN " . TABLE_PREFIX . "icon AS icon ON(icon.iconid = pmtext.iconid)") . "
  WHERE pm.userid=%d AND pm.folderid=%d" .
    ($search['searchtitle'] ? " AND pmtext.title LIKE '%" . $vbulletin->db->escape_string($search['searchtitle']) . "%'"       : '') .
    ($search['searchuser']  ? " AND pmtext.fromusername LIKE '%" . $vbulletin->db->escape_string($search['searchuser']) . "%'" : '') .
    ($search['startdate']   ? " AND pmtext.dateline >= $search[startdate]"                                                     : '') .
    ($search['enddate']     ? " AND pmtext.dateline <= $search[enddate]"                                                       : '') . "
  $readstatus
  LIMIT $startat, " . $vbulletin->GPC['perpage'] . "
", $vbulletin->userinfo['userid'], $vbulletin->GPC['folderid']);
$pm_userfield_result = $vbulletin->db->query_read($pm_userfield_query);
while ($pm_userfield_row = $vbulletin->db->fetch_array($pm_userfield_result))
{
  $my_field1_pmids[$pm_userfield_row['pmid']] = $pm_userfield_row['myfield1'];
  $my_field2_pmids[$pm_userfield_row['pmid']] = $pm_userfield_row['myfield2'];
  $my_field3_pmids[$pm_userfield_row['pmid']] = $pm_userfield_row['myfield3'];
  $my_field4_pmids[$pm_userfield_row['pmid']] = $pm_userfield_row['myfield4'];
} 


WHEW! Quite a lot there! It may be difficult to understand exactly what is going on in the query, but try to trudge your way through it anyhow. :3

(Again, this particular bit is as-of-yet untested, so I may have typos somewhere in there.)





The NEXT plugin you're going to create is at the hook, "private_messagelist_messagebit". This is basically right before the template is called. All we're doing with this plugin is making it easier to call the field variables in the template.

PHP Code:

$my_field1 = $my_field1_pmids[$pm['pmid']];
$my_field2 = $my_field2_pmids[$pm['pmid']];
$my_field3 = $my_field3_pmids[$pm['pmid']];
$my_field4 = $my_field4_pmids[$pm['pmid']]; 


Now, in the template, the varaibles you'll be calling are those $my_field1, $my_field2, etc.



Now for the template itself. It's pm_messagelistbit, and you can use those $my_fieldN variables anywhere you want in there.



.....I hope. :awe:



And demmit, we need an "awesome" smiley here.

[Mellymonster]

Originally Posted by DragonBlade

Okays, seems I made a spelling error AND added in an un-needed comma. XP

I tested this out on my forum and it works.



PLUGIN #1:

PHP Code:

// Query to check if/what threadowner's marital status is
$marital_status_threadids = array();
$marital_status_threads_query = sprintf("SELECT thread.threadid, userfield.field17 AS maritalstatus FROM thread LEFT JOIN userfield ON(thread.postuserid=userfield.userid) WHERE thread.threadid IN (0%s)", $ids);
$marital_status_threads_result = $vbulletin->db->query_read($marital_status_threads_query);
while ($marital_status_threads_row = $vbulletin->db->fetch_array($marital_status_threads_result))
  {$marital_status_threadids[$marital_status_threads_row['threadid']] = $marital_status_threads_row['maritalstatus'];} 


NOTE: The "," in the "WHERE thread.threadid IN" statement is now omitted.
NOTE2: I spelled "status" as "statis" in one variable. XD Oopsie.

See how that works out for ya.

For Marital Status Thread Display this right?

$thread['postusername'] .= $marital_status_threadids[$thread['threadid']];

For Marital Status Thread Query, this?

($hook = vBulletinHook::fetch_hook('forumdisplay_query')) ? eval($hook) : false;  

// Query to check if/what threadowner's marital status is
$marital_status_threadids = array();
$marital_status_threads_query = sprintf("SELECT thread.threadid, userfield.field17 AS maritalstatus FROM thread LEFT JOIN userfield ON(thread.postuserid=userfield.userid) WHERE thread.threadid IN (0%s)", $ids);
$marital_status_threads_result = $vbulletin->db->query_read($marital_status_threads_query);
while ($marital_status_threads_row = $vbulletin->db->fetch_array($marital_status_threads_result))
  {$marital_status_threadids[$marital_status_threads_row['threadid']] = $marital_status_threads_row['maritalstatus'];}

I'm still getting the big error.