db->fetch_array question

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Boofo
    Senior Member
    • Apr 2002
    • 2033
    • 4.1.x

    [Forum] db->fetch_array question

    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:

    Code:
    $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.
    vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

    vBulletin.org's ol' Moderator

    I have a lifetime terrorist hunting permit - #091101

    chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden
  • Mosh
    Senior Member
    • Aug 2004
    • 163
    • 4.1.x

    #2
    Bob,

    Use $db->query_read instead for arrays.

    Then use while or foreach to loop through the array.

    Regards,

    Mosh.
    -- Wolfshead Solutions - Closed down permanently as of 1/7/2013.
    -- As of 1/7/2013, due to medical reasons I no longer support my vbulletin.org hacks as I have left the vBulletin community.
    -- My Free vBulletin.org Hacks

    Comment

    • Boofo
      Senior Member
      • Apr 2002
      • 2033
      • 4.1.x

      #3
      Originally posted by Mosh
      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.

      Code:
          $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];
      Last edited by Boofo; Wed 9 Jun '10, 3:39am.
      vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

      vBulletin.org's ol' Moderator

      I have a lifetime terrorist hunting permit - #091101

      chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

      Comment

      • Mosh
        Senior Member
        • Aug 2004
        • 163
        • 4.1.x

        #4
        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.
        -- Wolfshead Solutions - Closed down permanently as of 1/7/2013.
        -- As of 1/7/2013, due to medical reasons I no longer support my vbulletin.org hacks as I have left the vBulletin community.
        -- My Free vBulletin.org Hacks

        Comment

        • Boofo
          Senior Member
          • Apr 2002
          • 2033
          • 4.1.x

          #5
          Originally posted by 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.
          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.
          vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

          vBulletin.org's ol' Moderator

          I have a lifetime terrorist hunting permit - #091101

          chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

          Comment

          • Boofo
            Senior Member
            • Apr 2002
            • 2033
            • 4.1.x

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

            Code:
            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:

            HTML Code:
            	<dd>{vb:raw userinfo.referralname}</dd>
            Does anyone see anything wrong with this?
            vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

            vBulletin.org's ol' Moderator

            I have a lifetime terrorist hunting permit - #091101

            chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

            Comment

            • Boofo
              Senior Member
              • Apr 2002
              • 2033
              • 4.1.x

              #7
              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):

              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

              Code:
                      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?
              vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

              vBulletin.org's ol' Moderator

              I have a lifetime terrorist hunting permit - #091101

              chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

              Comment

              • Removed-836727
                Banned by User Request
                • Apr 2006
                • 1274

                #8
                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

                Comment

                • Boofo
                  Senior Member
                  • Apr 2002
                  • 2033
                  • 4.1.x

                  #9
                  Originally posted by ragtek
                  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.
                  vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

                  vBulletin.org's ol' Moderator

                  I have a lifetime terrorist hunting permit - #091101

                  chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

                  Comment

                  • Removed-836727
                    Banned by User Request
                    • Apr 2006
                    • 1274

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

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

                    Comment

                    • Boofo
                      Senior Member
                      • Apr 2002
                      • 2033
                      • 4.1.x

                      #11
                      Originally posted by ragtek
                      I'm using xdebug for this (xdebug_time_index()) but i think this would be too difficult to explain.

                      => Instead use microtime
                      PHP Code:
                      $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.
                      vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

                      vBulletin.org's ol' Moderator

                      I have a lifetime terrorist hunting permit - #091101

                      chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

                      Comment

                      • Boofo
                        Senior Member
                        • Apr 2002
                        • 2033
                        • 4.1.x

                        #12
                        None of the devs or crack coders has an opinion on this?
                        vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

                        vBulletin.org's ol' Moderator

                        I have a lifetime terrorist hunting permit - #091101

                        chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

                        Comment

                        • Boofo
                          Senior Member
                          • Apr 2002
                          • 2033
                          • 4.1.x

                          #13
                          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?

                          Code:
                                  $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.
                          vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

                          vBulletin.org's ol' Moderator

                          I have a lifetime terrorist hunting permit - #091101

                          chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

                          Comment

                          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                          Working...