Jump to content

foreach question


savagenoob

Recommended Posts

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)'];
}

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)) 
             {

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.