SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot mike.fro's Avatar
    Join Date
    Jun 2005
    Location
    Lewisville, Texas
    Posts
    130
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Confused on For Each & Displaying Correct Results (CodeIgniter Framework)

    Hi Everyone,
    I’m working on building my first big application and am having some issues trying to figure something out. I have a database of leads and each lead is assigned to a marketer. Now I want to build a reports page where it shows a report for each marketer depending on the lead type. Now I have most of it working the way I would like, however it is showing me a result for each lead in the database. I’ve attached an image of the view.

    Under the column “Agent Leads” you can see there are 2 for “Michael Froseth” however it is also displaying 2 rows for Michael Froseth. I am trying to avoid that but am having trouble. Can anyone help explain this for me? Here is the code I have:
    PHP Code:
    <?php
        $query 
    $this->db->query(&#8220;SELECT * FROM lead_details WHERE lead_type = ” . $lead_type .” ORDER BY lead_id DESC”);  
        
    foreach ($query->result() as $row)
        {
        
        
    $who $this->db->query(&#8220;SELECT * FROM users WHERE id = $row->marketer_id”);
          
    foreach ($who->result() as $user) {
        
        
    $lead_type $row->lead_type;
        
    $contracted $row->contracted;
        
    $marketer $user->f_name;
        
    $marketer.=&#8221; $user->l_name”;
        
    $first_name $row->first_name;
        
    $last_name $row->last_name;
        
    $phone_number $row->phone;
        
    $email $row->email;
        
    $marketer_id $row->marketer_id;
        
      
    $this->db->where(&#8216;marketer_id’, $marketer_id);
      
    $this->db->where(&#8216;lead_type’, $lead_type);
      
    $this->db->from(&#8216;lead_details’);
      
    $agent_leads $this->db->count_all_results();
      
        
    ?>
    <table width=“94%”>
        <thead>
          <tr>
          <th width=“7%” height=“3%”>Marketer</th>
          <th width=“7%”>Agent Leads</th>
          <th width=“7%”>Not Called</th>
          <th width=“7%”>LVM1</th>
          <th width=“7%”>LVM2</th>
          <th width=“7%”>LVM3</th>
          <th width=“7%”>LVM4</th>
          <th width=“7%”>Not Interested</th>
          <th width=“7%”>DNC</th>
          <th width=“7%”>Please Call Back</th>
          <th width=“7%”>Sent Contracting</th>
          <th width=“7%”>Sent Contract-LVM1</th>
          <th width=“7%”>Sent Contract LVM2</th>
          <th width=“7%”>Sent Contract-LVM3</th>
          <th width=“7%”>Contracted</th>
          </tr>
        </thead>
        <tbody>
          <tr>
          <td><?php echo $marketer?></td>
          <td><?php echo $agent_leads?></td>
          <td >N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td >N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          </tr>
          <tr class=“percentageStats”>
          <td></td>
          <td></td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          </tr>
        </tbody>
      </table>
      <?php } } ?>
      <div class=“alert alert-warning”> <a class=“close” data-dismiss=“alert”></a> Total Statistics:Below are a list of all statistics for the life time of the Agent Recruitment System. </div>
      <table width=“94%”>
        <thead>
          <tr>
          <th width=“7%” height=“3%”>TOTALS</th>
          <th width=“7%”>Agent Leads</th>
          <th width=“7%”>Not Called</th>
          <th width=“7%”>LVM1</th>
          <th width=“7%”>LVM2</th>
          <th width=“7%”>LVM3</th>
          <th width=“7%”>LVM4</th>
          <th width=“7%”>Not Interested</th>
          <th width=“7%”>DNC</th>
          <th width=“7%”>Please Call Back</th>
          <th width=“7%”>Sent Contracting</th>
          <th width=“7%”>Sent Contract-LVM1</th>
          <th width=“7%”>Sent Contract LVM2</th>
          <th width=“7%”>Sent Contract-LVM3</th>
          <th width=“7%”>Contracted</th>
          </tr>
        </thead>
        <tbody>
          <tr>
          <td> </td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          </tr>
          <tr class=“percentageStats”>
          <td></td>
          <td></td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          <td>N/A</td>
          </tr>
        </tbody>
      </table>
      </div>
    </div>
    Any help would be greatly appreciated. I just can’t wrap my head around this.

    ar_results.jpg
    Michael John Froseth
    Creative Manager | Premier Planning
    Freelance Web Developer | MichaelFroseth | dotCom

  2. #2
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,287
    Mentioned
    182 Post(s)
    Tagged
    4 Thread(s)
    Are you certain there are not simply TWO rows in the database?
    You can try using the UNIQUE tag in your SQL query. I am sure another Sitepoint member with detailed SQL knowledge can guide you a bit more accurately on this.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  3. #3
    SitePoint Zealot mike.fro's Avatar
    Join Date
    Jun 2005
    Location
    Lewisville, Texas
    Posts
    130
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I honestly believe I a just writing the for each wrong, or in the wrong order. There are 3 leads in the database with the Type set to "Sales System AutoDialer" that is the page that I have pulled up. It should display the marketers with "Sales System AutoDialer" leads attached to them. But instead, it is displaying every lead with "Sales System AutoDialer".

    Basically there should be two people showing, with one person having 2 leads and the other having 1. Michael Froseth should display 2 in the Agent Leads column, and Aaron should display 1. It's showing the correct results, but Michael is being shown twice, for each lead.
    Michael John Froseth
    Creative Manager | Premier Planning
    Freelance Web Developer | MichaelFroseth | dotCom

  4. #4
    SitePoint Mentor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,267
    Mentioned
    33 Post(s)
    Tagged
    5 Thread(s)
    The problem is your outer query loop - it's selecting all the leads with a certain lead type and looping through them. As there are two leads for Michael Froseth, the loop runs twice, leading to a duplicate table.

    You should be able to solve it by adding a GROUP BY clause to the query, like this:
    PHP Code:
    $query $this->db->query("SELECT * FROM lead_details WHERE lead_type = $lead_type  GROUP BY marketer_id ORDER BY lead_id DESC"); 

  5. #5
    SitePoint Zealot mike.fro's Avatar
    Join Date
    Jun 2005
    Location
    Lewisville, Texas
    Posts
    130
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    The problem is your outer query loop - it's selecting all the leads with a certain lead type and looping through them. As there are two leads for Michael Froseth, the loop runs twice, leading to a duplicate table.

    You should be able to solve it by adding a GROUP BY clause to the query, like this:
    PHP Code:
    $query $this->db->query("SELECT * FROM lead_details WHERE lead_type = $lead_type  GROUP BY marketer_id ORDER BY lead_id DESC"); 
    Thank you so much. I knew it was something fairly simple, I just couldn't wrap my head around it!
    Michael John Froseth
    Creative Manager | Premier Planning
    Freelance Web Developer | MichaelFroseth | dotCom


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •