foreach question

[savagenoob]

Can anyone tell me why Count(ID) is not counting correctly? I want it to count how many entries in the database for each agent but its saying 28 prospects, 0 insureds when it should be 1 prospect, 5 insureds for the time frame i selected.

 

foreach($emplist as $emplists){
                $prospc = mysql_query("SELECT Count(ID) FROM clients WHERE agentname = '$emplists' AND Status = 'Prospect' AND Agency = '$agency'") or die(mysql_error());
                $prospcount = mysql_fetch_assoc($prospc);
                $prospects = $prospcount['Count(ID)'];
                $insuredc = mysql_query("SELECT Count(ID) FROM clients WHERE agentname = '$emplists' AND Status = 'Insured' AND Agency = '$agency'") or die(mysql_error());
                $inscount = mysql_fetch_assoc($insuredc);
                $insureds = $inscount['Count(ID)'];
}

[jcbones]

There is not enough info for use to help you.  We would need to see exactly what data you are working with.  But, You could combine those queries together, with a couple of case statements.

 

foreach($emplist as $emplists){
			$sql = "SELECT 
							SUM(CASE WHEN Status = 'Prospect' THEN 1 ELSE 0 END) as prospect, 
							SUM(CASE WHEN Status = 'Insured' THEN 1 ELSE 0 END) AS insured
						FROM clients 
						WHERE agentname = '$emplists' 
							AND Status = 'Prospect' 
							AND Agency = '$agency'";
			$result = mysql_query($sql);
			if(mysql_num_rows($result) > 0) {
				$r = mysql_fetch_assoc($result);
				$prospects = $r['prospect'];
				$insureds = $r['insured'];
			}
}

 

Other than that, we are going to need to know where you get your time frame, your $emplists, and your $agency from.

[PFMaBiSmAd]

time frame i selected

 

There's no time based selection in either query...

[kickstart]

Hi

 

How many entries are there on $emplists? Where are you specifying the date range you mention?

 

Never tried using somthing like COUNT(ID) as a column name.

 

It would seem you do not need the foreach:-

 

$prospc = mysql_query("SELECT agentname, Count(ID) AS IdCount FROM clients WHERE agentname IN ('".implode("','",$emplists)."') AND Status = 'Prospect' AND Agency = '$agency' GROUP BY agentname") or die(mysql_error());
$prospcount = mysql_fetch_assoc($prospc);
$prospects = $prospcount['IdCount'];

$insuredc = mysql_query("SELECT agentname, Count(ID) AS IdCount FROM clients WHERE agentname IN ('".implode("','",$emplists)."') AND Status = 'Insured' AND Agency = '$agency' GROUP BY agentname") or die(mysql_error());
$inscount = mysql_fetch_assoc($insuredc);
$insureds = $inscount['IdCount'];

 

All the best

 

Keith

[savagenoob]

Nevermind about the time based, need to used foreach to cycle through each employee, I'll include more.

 

$empget = mysql_query("SELECT * FROM members WHERE office = '$office' AND agency = '$agency'") or die(mysql_error());
                while($emp = mysql_fetch_array($empget)){
                $emplist[] = $emp['firstname'] . " " . $emp['lastname'];
                
                }
                
    foreach($emplist as $emplists){
                $prospc = mysql_query("SELECT Count(ID) FROM clients WHERE agentname = '$emplists' AND Status = 'Prospect' AND Agency = '$agency'") or die(mysql_error());
                $prospcount = mysql_fetch_assoc($prospc);
                $prospects = $prospcount['Count(ID)'];
                $insuredc = mysql_query("SELECT Count(ID) FROM clients WHERE agentname = '$emplists' AND Status = 'Insured' AND Agency = '$agency'") or die(mysql_error());
                $inscount = mysql_fetch_assoc($insuredc);
                $insureds = $inscount['Count(ID)'];
			$query = "SELECT Amount1RS, SUM(BrokerFee), SUM(TotalCollected), SUM(Commission), AVG(BrokerFee), SUM(CompanyFees), SUM(AmountDue), COUNT(ID) FROM accounting WHERE agency = '$agency' AND Date BETWEEN '$newdate1' AND '$date3' GROUP BY Amount1RS";
			$result = mysql_query($query);
			while($myrow = mysql_fetch_assoc($result)) 
             {

[mikosiko]

you have any reason to write 3 query?...

 

something like this should replace your 3 first querys (no tested completly)

SELECT a.agentname, a.status, count(a.status) AS Cstatus
FROM clients a JOIN members b 
    ON a.agentname = CONCAT_WS(' ', b.firstname, b.lastname) AND b.office = '$office'
WHERE a.status IN ('Prospect', 'Insured')
  AND a.agency = '$agency'
  GROUP BY a.agentname, a.status

 

your 4th query doesn't seems to have any relation with the previous ones, therefore I don't see why it should be in the loop

 

 

[mikosiko]

and a possible slightly different alternative could be

SELECT a.agentname, 
       SUM(IF(a.status='Prospect',1,0)) AS TProspect,
       SUM(IF(a.status='Insured',1,0))  AS TInsured
FROM clients a JOIN members b 
    ON a.agentname = CONCAT_WS(' ', b.firstname, b.lastname) AND b.office = '$office'
WHERE a.status IN ('Prospect', 'Insured')
  AND a.agency = '$agency'
  GROUP BY a.agentname

[fortnox007]

That last one looks pretty awesome!

[savagenoob]

OK I'm burnt doint this I think.  Even just doing a mysql_num_rows() is not working for some reason.

 

$empget = mysql_query("SELECT * FROM members WHERE office = '$office' AND agency = '$agency'") or die(mysql_error());
                while($emp = mysql_fetch_array($empget)){
                $emplist[] = $emp['firstname'] . " " . $emp['lastname'];
                
                }
                
    foreach($emplist as $emplists){
                echo $emplists;
                $proquery = "SELECT * FROM clients WHERE agentname = '$emplists' AND Status = 'Prospect' AND Agency = '$agency' BETWEEN '$newdate1' AND '$date3'";
                $prospc = mysql_query($proquery) or die(mysql_error());
                $prospcount = mysql_num_rows($prospc);
                
                echo $prospcount;
                $insquery = "SELECT * FROM clients WHERE agentname = '$emplists' AND Status = 'Insured' AND Agency = '$agency' BETWEEN '$newdate1' AND '$date3'";
                $insuredc = mysql_query($insquery) or die(mysql_error());
                $inscount = mysql_num_rows($insuredc);
                echo $insquery;
                echo $inscount;
}

Its returning 0 for both....

[savagenoob]

OMG nevermind...