Welcome Guest, Not a member yet? Register   Sign In
Confused on For Each & Displaying Correct Results
#1

[eluser]mfroseth[/eluser]
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:

<code>
&lt;?php
$query = $this->db->query("SELECT * FROM lead_details WHERE lead_type = " . $lead_type ." ORDER BY lead_id DESC");
foreach ($query->result() as $row)
{

$who = $this->db->query("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.=" $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('marketer_id', $marketer_id);
$this->db->where('lead_type', $lead_type);
$this->db->from('lead_details');
$agent_leads = $this->db->count_all_results();


?&gt;
<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>&lt;?php echo $marketer; ?&gt;</td>
<td>&lt;?php echo $agent_leads; ?&gt;</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>
&lt;?php } } ?&gt;
<div class="alert alert-warning"> <a class="close" data-dismiss="alert">×</a> <strong>Total Statistics:</strong> 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>&nbsp;</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>
</code>

Any help would be greatly appreciated. I just can't wrap my head around this.
#2

[eluser]niki_mihaylov[/eluser]
Hmm im not 100% sure that i understood your idea, but if you want to get unique leads by marketer you can use
GROUP by in your query. Like so:

Code:
$query = $this->db->query(“SELECT * FROM lead_details WHERE lead_type = ” . $lead_type .” GROUP BY marketer_id ORDER BY lead_id DESC”);




Theme © iAndrew 2016 - Forum software by © MyBB