[Forum] db->fetch_array question

[Boofo]

If you wanted to pull more than 1 piece if info from a query, I know you use db->fetch_array, but I'm not sure how you would use it. In this example:

$referrals = $db->query_first("
SELECT username, userid
FROM " . TABLE_PREFIX . "user
WHERE referrerid = '".$userinfo['userid']."'
");

$userinfo['referralname'] = $referrals[username];
 $userinfo['referralid'] = $referrals[userid];

How would you pull more than one username and userid? Like a list.

[Mosh]

Bob,

Use $db->query_read instead for arrays.

Then use while or foreach to loop through the array.

Regards,

Mosh.

[Boofo]

Bob,

Use $db->query_read instead for arrays.

Then use while or foreach to loop through the array.

Regards,

Mosh.

I tried using the while, but never got it to work right. It lists the names but as one link instead of separately.

    $referrals = $db->query_read(" 
        SELECT username, userid 
        FROM " . TABLE_PREFIX . "user  
        WHERE referrerid = '".$userinfo['userid']."'  
    "); 
        while ($referral = $db->fetch_array($referrals)) 
        { 
            $ref['referralname'] .= ", $referral[username]"; 
            $ref['referralid'] .= $referral[userid]; 
        } 
        $db->free_result($referrals); 

        $userinfo['referralname'] = ltrim($ref[referralname], ', '); 
        $userinfo['referralid'] = $ref[referralid];

[Mosh]

You are going to need to construct your rows first in the while loop, then spit out the results after - if you look at code that utilises the misc_start hook of my Who Has Rated A Thread you will get the gist of what I am on about.

[Boofo]

You are going to need to construct your rows first in the while loop, then spit out the results after - if you look at code that utilises the misc_start hook of my Who Has Rated A Thread you will get the gist of what I am on about.

I looked at that but it still doesn't want to link each name, just all of them under a single link. The separate names show fine.

I guess it will have to do with just pulling the names and not trying to link them to their profile. Thank you, anyway, sir.

[Boofo]

I finally got it, although I'm sure there is a cleaner way to do it. This is what I ended up with:

if ($vbulletin->options['usereferrer'] AND !$userinfo['referrerid']) 
{ 
    $referrals = $db->query_read(" 
        SELECT username, userid 
        FROM " . TABLE_PREFIX . "user 
        WHERE referrerid = '".$userinfo['userid']."' 
    "); 
        while ($referral = $db->fetch_array($referrals)) 
        { 
            $ref['referralname'] .= ", <a href=\"member.php?u=$referral[userid]\">$referral[username]</a>"; 
        } 
        $db->free_result($referrals); 

        $userinfo['referralname'] = ltrim($ref[referralname], ', '); 
}

I call it from the template as:

	<dd>{vb:raw userinfo.referralname}</dd>

Does anyone see anything wrong with this?

[Boofo]

Mosh and I were discussing this earlier and I have a question for the devs. Which way is less server intensive if you have 1000 referred members? The vb way of doing it (first code):

	while ($referral = $db->fetch_array($referrals))
	{
		$templater = vB_Template::create('boofo_referral_bit');
		$templater->register('referral', $referral);
		$referral_row .= $templater->render();
	}
	$db->free_result($referrals);

	$userinfo['referralname'] = ltrim($referral_row, ', ');

Which uses 2 templates (the bit and the main) and renders a template for each referred username found, or

        while ($referral = $db->fetch_array($referrals)) 
        { 
            $ref['referralname'] .= ", <a href=\"member.php?$session[sessionurl]$referral[userid]-$referral[username]\">$referral[username]</a>"; 
        } 
        $db->free_result($referrals); 

        $userinfo['referralname'] = ltrim($ref[referralname], ', ');

Which only adds the code to the main template.

I would think not having to render a template for every username would be faster and less server intensive. But I could be wrong. Any suggestions?

[Removed-836727]

Try it out^^

place a timer before you start the while part and one after the while part, then you can see how long it took
and in the debug info you can see how many memory,etc is needed

[Boofo]

Try it out^^

place a timer before you start the while part and one after the while part, then you can see how long it took
and in the debug info you can see how many memory,etc is needed

If I knew how to do that, I wouldn't be asking the question here.

[Removed-836727]

I'm using xdebug for this (xdebug_time_index()) but i think this would be too difficult to explain.

=> Instead use microtime

$timeStartComma = microtime(true);
//now do something, for example the while part
$timeEndComma = microtime(true);
echo "Part needed " . ($timeEndComma-$timeStartComma) . " seconds\n"; 

[Boofo]

I'm using xdebug for this (xdebug_time_index()) but i think this would be too difficult to explain.

=> Instead use microtime

$timeStartComma = microtime(true);
//now do something, for example the while part
$timeEndComma = microtime(true);
echo "Part needed " . ($timeEndComma-$timeStartComma) . " seconds\n"; 

Still way over my head. And it wouldn't do me any good testing it as I have a total of 3 members right now, as I am still setting up the site.

Thanks, but I'll wait for a dev or someone who knows that can tell me by looking at the code I posted.

[Boofo]

None of the devs or crack coders has an opinion on this?

[Boofo]

I have another question concerning this. Do you need to declare an array if you are only using the while and not using a foreach? As in the following?

        $ref = array(); 
        while ($referral = $vbulletin->db->fetch_array($referrals)) 
        { 
            $ref['referralname'] .= ", <a href=\"member.php?$session[sessionurl]$referral[userid]-$referral[username]\">$referral[username]</a>"; 
        } 
        unset($referral); 
        $vbulletin->db->free_result($referrals);

What is the proper way? It runs the same with or without the array declaration, but I am trying to learn the right way to do things like this.