Using the vB Database Class

[KGodel]

I am having strange issues with the vB_Database class. I have several custom pages which I use through the BF Easypages plugin (and some not) and I get different results.

When I add a tool to the ACP, it seems that most of the DB class functions work fine, however in my page, for instance, the following work:

$vbulletin->db->query_read()
$vbulletin->db->query_read_slave()

but the following won't work:

$vbulletin->db->fetch_array()

This leaves me to use the following to get the information, but I'd like to switch all of my code completely over to use the vB Database class so that I can upgrade our PHP version without causing a bunch of deprication issues.

mysql_fetch_array()

After reading the documentation (https://members.vbulletin.com/api/vBulletin/vB_Database.html), there don't seem to be rules governing what should and shouldn't work. Is it perhaps that I cannot run multiple queries this way before fetching the results? I'm left very confused. Is there an article that details the use of the various class functions?

Page code in question listed below:

// Check for User ID
$userid = filter_input(INPUT_GET, 'userid', FILTER_VALIDATE_INT);
if ($userid == null) {
$userid = $vbulletin->userinfo[userid];
}
// Check for Missing in Action Roster
// Page Start //
require_once(DIR . '/custom/functions.php');
page_header("Achievement Point Helper");

// Define Usergroup Levels
$grouplevels = array();
// Group ID, Required Points, Rank Image Source
$grouplevels[] = array(35, 3, "<img src='images/ranks/v9/mem_stea.png' alt='Steadfast Member' />", "Steadfast Member");
$grouplevels[] = array(51, 8, "<img src='images/ranks/v9/mem_seni.png' alt='Senior Member' />", "Senior Member");
$grouplevels[] = array(52, 15, "<img src='images/ranks/v9/mem_reno.png' alt='Renowned Member' />", "Renowned Member");
$grouplevels[] = array(53, 30, "<img src='images/ranks/v9/mem_visi.png' alt='Visionary Member' />", "Visionary Member");

// Define Basic Membergroups
$membergroups = array(2, 35, 51, 52, 53);
$leadergroups = array(5, 6, 17, 18, 24, 38, 50, 54, 55, 56, 58);

echo "
<table width='100%'>
<tbody>
<tr>
<td>";

if ($userid != null) {

// Query DB for Post Content //
$threads_q = $vbulletin->db->query_read("SELECT COUNT(*) FROM " . TABLE_PREFIX . "thread WHERE postuserid = $userid AND forumid NOT IN (13, 19, 28, 43, 47, 49, 71, 86, 126, 141,162, 167, 168, 178, 180, 181, 219, 222, 233, 237, 238, 239, 243, 254, 255, 256, 259, 267, 284, 285, 287, 301, 304, 306, 307, 312, 313, 314, 316, 319, 337, 338, 345, 346, 375, 381, 384, 385, 391, 396, 402)");
$recruits_q = $vbulletin->db->query_read("SELECT COUNT(*) FROM " . TABLE_PREFIX . "user WHERE referrerid = $userid");
$awards_q = $vbulletin->db->query_read("SELECT COUNT(*) FROM " . TABLE_PREFIX . "award_user WHERE userid = $userid AND award_id NOT IN (6, 7, 10, 20, 21, 23, 24, 25)");
$info_q = $vbulletin->db->query_read("SELECT user.username, user.posts, user.joindate, user.usergroupid, user.membergroupids, user.iga_points, userfield.field49, userfield.field59 FROM " . TABLE_PREFIX . "user as user LEFT JOIN " . TABLE_PREFIX . "userfield as userfield ON (userfield.userid=user.userid) WHERE user.userid = $userid");


// Transfer to Arrays
$u_threads = mysql_fetch_array($threads_q);
$u_recruits = mysql_fetch_array($recruits_q);
$u_awards = mysql_fetch_array($awards_q);
$u_info = mysql_fetch_array($info_q);

// Get Counts
$username = $u_info[0];
if ($username == null) {
goto error;
}
$posts = $u_info[1];
$joindate = new DateTime(date("Y-m-d", $u_info[2]));
$currentdate = new DateTime();
$displaydate = vbdate($vbulletin->options['dateformat'], $u_info[2], true);
//date
$datediff = $currentdate->diff($joindate);
$datediffm = $datediff->format('%m');
$datediffy = $datediff->format('%y');
$datediff = $datediffm + (12 * $datediffy);
//end date
$usergroup = $u_info[3];
$othergroups = $u_info[4];
$igapoints = $u_info[5];
$staffyesno = $u_info[6];
$permvmyesno = $u_info[7];
$threads = $u_threads[0];
$recruits = $u_recruits[0];
$awards = $u_awards[0];
$totalpoints = 0;

// Default Messages //
$nopoints = "<font color='red'>No Points</font>";

// Set Points Output //
// Post Points
$p10 = $p25 = $p50 = $p100 = $p250 = $p500 = $nopoints;

if ($posts >= 10) {
$p10 = "<font color='green'>1 Point</font>";
$totalpoints += 1;
}
if ($posts >= 25) {
$p25 = "<font color='green'>1 Point</font>";
$totalpoints += 1;
}
if ($posts >= 50) {
$p50 = "<font color='green'>1 Point</font>";
$totalpoints += 1;
}
if ($posts >= 100) {
$p100 = "<font color='green'>2 Point</font>";
$totalpoints += 2;
}
if ($posts >= 250) {
$p250 = "<font color='green'>2 Points</font>";
$totalpoints += 5;
}
if ($posts >= 500) {
$totalpoints += 3;
$p500 = "<font color='green'>3 Points</font>";
}

// Thread Points
$t10 = $t20 = $t50 = $t100 = $nopoints;

if ($threads >= 10) {
$t10 = "<font color='green'>1 Point</font>";
$totalpoints += 1;
}
if ($threads >= 20) {
$t20 = "<font color='green'>1 Point</font>";
$totalpoints += 1;
}
if ($threads >= 50) {
$t50 = "<font color='green'>2 Points</font>";
$totalpoints += 2;
}
if ($threads >= 100) {
$totalpoints += 3;
$t100 = "<font color='green'>3 Points</font>";
}

// Recruited Member Points
$r4 = $r5 = $nopoints;

if ($recruits >= 1 && $recruits <= 4) {
$r4 = "<font color='green'>$recruits Point(s)</font>";
$totalpoints += $recruits;
} elseif ($recruits >= 5) {
$r5points = floor($recruits / 5);
$r4 = "<font color='green'>4 Points</font>";
$r5 = "<font color='green'>$r5points Points</font>";
$totalpoints += (4 + $r5points);
}

// Membership Length Points
$m1 = $m3 = $m12 = $nopoints;

if ($datediff >= 1) {
$m1 = "<font color='green'>1 Point</font>";
$totalpoints += 1;
}
if ($datediff >= 3) {
$m3 = "<font color='green'>1 Point</font>";
$totalpoints += 1;
}
if ($datediff >= 12) {
$m12 = "<font color='green'>5 Points</font>";
$totalpoints += 5;
}

// Award Points
$a = $nopoints;

if ($awards > 0) {
$apoints = 2 * $awards;
$totalpoints += $apoints;
$a = "<font color='green'>$apoints Points</font>";
}

// Points for Positions
$staffpts = $nopoints;

if ($staffyesno == "Yes") {
$staffpts = "<font color='green'>5 Points</font>";
$totalpoints += 5;
}

$igp = 0;
$igpts = $nopoints;

// Points for In-Game Activity
if ($igapoints >= 50) {
$igp += 1;
}
if ($igapoints >= 100) {
$igp += 1;
}
if ($igapoints >= 150) {
$igp += 1;
}
if ($igapoints >= 200) {
$igp += 1;
}
if ($igapoints >= 250) {
$igp += 1;
}
if ($igapoints >= 300) {
$igp += 1;
}
if ($igapoints >= 350) {
$igp += 1;
}
if ($igapoints == 400) {
$igp += 1;
}
if ($igp > 0) { $totalpoints += $igp; $igpts = "<font color='green'>$igp Point(s)"; }

// End of Points //

echo "<p><strong>Username</strong>: $username <br/>
<strong>Threads</strong>: $threads <br/>
<strong>Posts</strong>: $posts<br/>
<strong>Recruits</strong>: $recruits <br/>
<strong>Join Date (mm-dd-YYYY)</strong>: $displaydate <br/>
<strong>Nominated Medals</strong>: $awards </p>";

// Begin Table //
echo "<table width='100%' border='0' cellspacing='2' cellpadding='0'>
<thead>
<tr>
<td width='50%' align='center'><strong>Achievement</strong></td>
<td width='50%' align='center'><strong>Points Available</strong></td>
</tr>
</thead>
<tfoot>
<tr>
<td><strong>Total Points</strong></td>
<td><font color='green'><strong>$totalpoints Points</strong></font></td>
</tr>
</tfoot>
<tbody>
<tr>
<td>(1) Reaching 10 posts on the forums</td>
<td>$p10</td>
</tr>
<tr>
<td>(1) Reaching 25 posts on the forums</td>
<td>$p25</td>
</tr>
<tr>
<td>(1) Reaching 50 posts on the forums</td>
<td>$p50</td>
</tr>
<tr>
<td>(2) Reaching 100 posts on the forums</td>
<td>$p100</td>
</tr>
<tr>
<td>(2) Reaching 250 posts on the forums</td>
<td>$p250</td>
</tr>
<tr>
<td>(3) Reaching 500 posts on the forums</td>
<td>$p500</td>
</tr>
<tr>
<td>(1) Reaching 10 threads on the forums</td>
<td>$t10</td>
</tr>
<tr>
<td>(1) Reaching 20 threads on the forums</td>
<td>$t20</td>
</tr>
<tr>
<td>(2) Reaching 50 threads on the forums</td>
<td>$t50</td>
</tr>
<tr>
<td>(3) Reaching 100 threads on the forums</td>
<td>$t100</td>
</tr>
<tr>
<td>(1) Point for each member recruited (up to 4)</td>
<td>$r4</td>
</tr>
<tr>
<td>(1) Point for each 5 new members recruited</td>
<td>$r5</td>
</tr>
<tr>
<td>(1) Membership for 1 month</td>
<td>$m1</td>
</tr>
<tr>
<td>(1) Membership for 3 months</td>
<td>$m3</td>
</tr>
<tr>
<td>(5) Membership for 1 year</td>
<td>$m12</td>
</tr>
<tr>
<td>(2) Points for each nominated medal awarded on the forums</td>
<td>$a</td>
</tr>
<tr>
<td>(5) Points for holding a staff position</td>
<td>$staffpts</td>
</tr>
<tr>
<td>(1) Point for each 50 in-game activity points</td>
<td>$igpts</td>
</tr>
</tbody>
</table>";

echo "<p><font color='red'><strong>NOTE</strong></font>: This is not a complete list of your points. Some points, such as points for being a leader or administrator,
holding an rank in an in-game guild, points for playing with others divisions and groups, and points for division activity must be counted manually. You may
also lose points for any Justice Tickets on record.</p>";

// If Viewing your own stats, Check if you can level up
if ($userid == $vbulletin->userinfo[userid]) {

$newgroup = $newrank = $newranktitle = "";

// If they are not in leadership
if (in_array($usergroup, $membergroups)) {
// Go Through Each Rank and Check if They Can Achieve It
foreach ($grouplevels as $level) {
$groupid = $level[0];
$reqpoints = $level[1];
$rankcode = $level[2];
$ranktitle = $level[3];

/// If they have points to rank up
if ($totalpoints >= $reqpoints && $usergroup < $groupid) {
if ($groupid == 53) {
if ($igp >= 2) {
$newgroup = $groupid;
$newrank = $rankcode;
$newranktitle = $ranktitle;
$log = array($newgroup, 'P', $newranktitle, $username);
}
} else {
$newgroup = $groupid;
$newrank = $rankcode;
$newranktitle = $ranktitle;
$log = array($newgroup, 'P', $newranktitle, $username);
}
}
}

//Check for Visionary Member Decay
if ($usergroup == 53 && $igp < 2 && $permvmyesno == "No") {
$newgroup = 52;
$newrank = "<img src='images/ranks/v9/mem_reno.png' alt='Renowned Member' />";
$newranktitle = "Renowned Member";
$log = array($newgroup, 'D', $newranktitle, $username);
}

// If they earned a new usergroup, change it
if ($newgroup != null) {
$userdata = & datamanager_init('User', $vbulletin, ERRTYPE_ARRAY);
$userdata->set_existing($vbulletin->userinfo);
$userdata->setr('usergroupid', $newgroup);
$userdata->setr('usertitle', $newrank);
// Set permanent VM if they have earned it via activity points.
if ($newgroup == 53 && $igp == 8) {
$userdata->setr('field59', 'Yes');
$userdata->setr('customtitle', 0);
}
$userdata->save();

// the "1" indicates to use the second line of the phrase specified for this task
log_cron_action(serialize($log), $nextitem, 1);

//$db->query_write("UPDATE " . TABLE_PREFIX . "user SET usergroupid = $newgroup, usertitle = $newrank WHERE userid = $userid");
echo "<p><img src='images/important.png' alt='Important!'/> You qualified for a new rank! Your rank is now $newranktitle! Congratulations</p>";
}
}

// If they are in leadership, check additional groups
if (in_array($usergroup, $leadergroups)) {
// If they are in leadership, check additional groups
// If no additional usergroup, set apprentice
if ($othergroups == null) {
$othergroups = "2";
}

foreach ($membergroups as $gid) {
$group = (string) $gid;
if (in_array($group, explode(",",$othergroups)) !== false) {
$secgroup = $group;
$secgroupid = $gid;
break;
}
}


// Go Through Each Rank and Check if They Can Achieve It
foreach ($grouplevels as $level) {
$groupid = $level[0];
$reqpoints = $level[1];
$ranktitle = $level[3];

// If they have points to rank up
if ($totalpoints >= $reqpoints && $secgroupid < $groupid) {

if ($groupid == 53) {
if ($igp >= 2) {
$newgroup = (string) $groupid;
$newranktitle = $ranktitle;
$log = array($newgroup, 'S', $newranktitle, $username);
}
} else {
$newgroup = (string) $groupid;
$newranktitle = $ranktitle;
$log = array($newgroup, 'S', $newranktitle, $username);
}
}
}

$othergroups = str_replace($secgroup, $newgroup, $othergroups);

// If they earned a new usergroup, change it
if ($newgroup != null) {
$userdata = & datamanager_init('User', $vbulletin, ERRTYPE_ARRAY);
$userdata->set_existing($vbulletin->userinfo);
$userdata->setr('membergroupids', $othergroups);
// Set permanent VM if they have earned it via activity points.
if ($newgroup == 53 && $igp == 8) {
$userdata->setr('field59', 'Yes');
}
$userdata->save();
// the "1" indicates to use the second line of the phrase specified for this task
log_cron_action(serialize($log), $nextitem, 1);

//$db->query_write("UPDATE " . TABLE_PREFIX . "user SET usergroupid = $newgroup, usertitle = $newrank WHERE userid = $userid");
echo "<p><img src='images/important.png' alt='Important!'/> You qualified for a new rank! Your rank is now $newranktitle! Congratulations</p>";
echo "<p>2nd Group: $secgroup and NewGroup = $groupid<p/>";
echo $othergroups;
}
}
}
} else {

error:
echo "<p>Please enter a valid UserID.</p>";
}

// Footer
page_footer();

[kh99]

I was about to write that it should work, and then I realized that there is a difference: There's a second parameter to mysql_fetch_array() which tells what kind of index to return, and the default is both column names and integers (MYSQL_BOTH). The vbulletin fetch_array also has that parameter but the default is DBARRAY_ASSOC (which is set to MYSQL_ASSOC). So if you're using the integers, you'd want to pass DBARRAY_NUM or DBARRAY_BOTH as the second parameter.

Did someone ask about this somewhere else (or maybe you under a different name)? I saw a similar question earlier today, and I just realized that this is probably the answer to that one too.

[KGodel]

I had my pages break on the CMS on vB.com (I'm jcwill there), but I beleive that is a separate issue because it broke prior to my changing this.

[kh99]

I had my pages break on the CMS on vB.com (I'm jcwill there), but I beleive that is a separate issue because it broke prior to my changing this.

Oh, right. I should have remembered that was you over there. Anyway, I think that's the answer to your problem there too, and it's kind of my fault since I told you to change to using $vbulletin->db->fetch_array() because I didn't realize that by default it doesn't return the integers. Sorry about that. :o

[KGodel]

I will try that on the page and see if that helps. It's still funny because in the ACP stuff I use I do not include the DBARRAY_X parameter and use numbers and it works completely fine. Odd, but I will try this in the future and hopefully get rid of using all the old default mysql_ functions and upgrade php and see if we can finally move up to use some neat new functions. ^^

Edit: That did not solve my problems on the CMS but did solve them on my custom page. Thanks!

[kh99]

I will try that on the page and see if that helps. It's still funny because in the ACP stuff I use I do not include the DBARRAY_X parameter and use numbers and it works completely fine.

That is strange. I don't see how that would work. Anyway, hmm, I was sure that would turn out to be the problem on the CMS thing.

[KGodel]

I should probably start using field names anyway to be honest. It's much easier to keep track of for others who are looking at my code, and probably for myself when I see these problems, haha. I also wouldn't have to change my indexes every time I added a field to a query that wasn't at the end of the list.