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

mike_fro · June 17, 2013, 3:18pm

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
    $query = $this->db->query(&#8220;SELECT * FROM lead_details WHERE lead_type = &#8221; . $lead_type .&#8221; ORDER BY lead_id DESC&#8221;);  
    foreach ($query->result() as $row)
    {
    
    $who = $this->db->query(&#8220;SELECT * FROM users WHERE id = $row->marketer_id&#8221;);
      foreach ($who->result() as $user) {
    
    $lead_type = $row->lead_type;
    $contracted = $row->contracted;
    $marketer = $user->f_name;
    $marketer.=&#8221; $user->l_name&#8221;;
    $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&#8217;, $marketer_id);
  $this->db->where(&#8216;lead_type&#8217;, $lead_type);
  $this->db->from(&#8216;lead_details&#8217;);
  $agent_leads = $this->db->count_all_results();
  
    
?>
<table width=&#8220;94%&#8221;>
    <thead>
      <tr>
      <th width=&#8220;7%&#8221; height=&#8220;3%&#8221;>Marketer</th>
      <th width=&#8220;7%&#8221;>Agent Leads</th>
      <th width=&#8220;7%&#8221;>Not Called</th>
      <th width=&#8220;7%&#8221;>LVM1</th>
      <th width=&#8220;7%&#8221;>LVM2</th>
      <th width=&#8220;7%&#8221;>LVM3</th>
      <th width=&#8220;7%&#8221;>LVM4</th>
      <th width=&#8220;7%&#8221;>Not Interested</th>
      <th width=&#8220;7%&#8221;>DNC</th>
      <th width=&#8220;7%&#8221;>Please Call Back</th>
      <th width=&#8220;7%&#8221;>Sent Contracting</th>
      <th width=&#8220;7%&#8221;>Sent Contract-LVM1</th>
      <th width=&#8220;7%&#8221;>Sent Contract LVM2</th>
      <th width=&#8220;7%&#8221;>Sent Contract-LVM3</th>
      <th width=&#8220;7%&#8221;>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=&#8220;percentageStats&#8221;>
      <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=&#8220;alert alert-warning&#8221;> <a class=&#8220;close&#8221; data-dismiss=&#8220;alert&#8221;>×</a> Total Statistics:Below are a list of all statistics for the life time of the Agent Recruitment System. </div>
  <table width=&#8220;94%&#8221;>
    <thead>
      <tr>
      <th width=&#8220;7%&#8221; height=&#8220;3%&#8221;>TOTALS</th>
      <th width=&#8220;7%&#8221;>Agent Leads</th>
      <th width=&#8220;7%&#8221;>Not Called</th>
      <th width=&#8220;7%&#8221;>LVM1</th>
      <th width=&#8220;7%&#8221;>LVM2</th>
      <th width=&#8220;7%&#8221;>LVM3</th>
      <th width=&#8220;7%&#8221;>LVM4</th>
      <th width=&#8220;7%&#8221;>Not Interested</th>
      <th width=&#8220;7%&#8221;>DNC</th>
      <th width=&#8220;7%&#8221;>Please Call Back</th>
      <th width=&#8220;7%&#8221;>Sent Contracting</th>
      <th width=&#8220;7%&#8221;>Sent Contract-LVM1</th>
      <th width=&#8220;7%&#8221;>Sent Contract LVM2</th>
      <th width=&#8220;7%&#8221;>Sent Contract-LVM3</th>
      <th width=&#8220;7%&#8221;>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=&#8220;percentageStats&#8221;>
      <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.

ParkinT · June 17, 2013, 4:08pm

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.

mike_fro · June 17, 2013, 4:15pm

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.

fretburner · June 17, 2013, 5:06pm

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:

$query = $this->db->query("SELECT * FROM lead_details WHERE lead_type = $lead_type  GROUP BY marketer_id ORDER BY lead_id DESC");

mike_fro · June 17, 2013, 6:03pm

fretburner:

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:
$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!