Count(*) different in php vs MySQL query

Hi,

SELECT query includes Count() & gives 244 when executed in MySQL which is correct. However when I run the same query in php all results are the same except Count() gives 127. I know php has a Count function for arrays but the Count is part of a MySQL query enlosed with " ". Any idea what’s going on here?

thanks, Tim

original query

SELECT
‘2011-03-01’,
COUNT(*) AS UNITS,
SUM(units.MktRent) AS TOTMKTRNT,
SUM(units.MktRent)/DAYOFMONTH(LAST_DAY(‘2011-03-01’)) AS MKTRNT,
SUM(units.MktRent - v_leases.AptRent)/DAYOFMONTH(LAST_DAY(‘2011-03-01’)) AS LTL,
SUM(v_leases.AptRent)/DAYOFMONTH(LAST_DAY(‘2011-03-01’)) AS APTRNT,
SUM(v_leases.PetRent)/DAYOFMONTH(LAST_DAY(‘2011-03-01’)) AS PETRNT,
SUM(v_leases.UtilFee)/DAYOFMONTH(LAST_DAY(‘2011-03-01’)) AS UTIL,
SUM(v_leases.Charge1)/DAYOFMONTH(LAST_DAY(‘2011-03-01’)) AS CHG1,
SUM(v_leases.Charge2)/DAYOFMONTH(LAST_DAY(‘2011-03-01’)) AS CHG2
FROM units LEFT OUTER JOIN v_leases
ON (units.UnitID = v_leases.UnitID)
AND (((v_leases.LeaseStart <= ‘2011-03-01’)
AND (v_leases.LeaseEnd >= ‘2011-03-01’)
AND (ISNULL(v_leases.MoveOut)
OR (v_leases.MoveOut >= ‘2011-03-01’)))
OR ((v_leases.LeaseStart <= ‘2011-03-01’)
AND (v_leases.LeaseEnd <= ‘2011-03-01’)
AND (ISNULL(v_leases.MoveOut)
OR (v_leases.MoveOut > ‘2011-03-01’))
AND (ISNULL(v_leases.LeaseStatus)
OR (v_leases.LeaseStatus <> ‘Renewed’))));

query in php script (in while loop & replace date with variable $selectdate )

while($nextdate <= $enddate){

$selectdate = date(“Y-m-d”,$nextdate);

$query = “INSERT INTO history rent roll (RRDate, Units, TotMktRent, RRMktRent, RRLTL, RRAptRent, RRPetRent, RRUtilFee, RRCharge1, RRCharge2)
SELECT
‘$selectdate’,
COUNT(*),
SUM(units.MktRent),
SUM(units.MktRent)/DAYOFMONTH(LAST_DAY(’$selectdate’)),
SUM(units.MktRent - v_leases.AptRent)/DAYOFMONTH(LAST_DAY(’$selectdate’)),
SUM(v_leases.AptRent)/DAYOFMONTH(LAST_DAY(’$selectdate’)),
SUM(v_leases.PetRent)/DAYOFMONTH(LAST_DAY(’$selectdate’)),
SUM(v_leases.UtilFee)/DAYOFMONTH(LAST_DAY(’$selectdate’)),
SUM(v_leases.Charge1)/DAYOFMONTH(LAST_DAY(’$selectdate’)),
SUM(v_leases.Charge2)/DAYOFMONTH(LAST_DAY(’$selectdate’))
FROM units LEFT OUTER JOIN v_leases
ON (units.UnitID = v_leases.UnitID)
AND (((v_leases.LeaseStart <= ‘$selectdate’)
AND (v_leases.LeaseEnd >= ‘$selectdate’)
AND (ISNULL(v_leases.MoveOut)
OR (v_leases.MoveOut >= ‘$selectdate’)))
OR ((v_leases.LeaseStart <= ‘$selectdate’)
AND (v_leases.LeaseEnd <= ‘$selectdate’)
AND (ISNULL(v_leases.MoveOut)
OR (v_leases.MoveOut > ‘$selectdate’))
AND (ISNULL(v_leases.LeaseStatus)
OR (v_leases.LeaseStatus <> ‘Renewed’))))”;

$result = mysql_query($query);

$nextdate = strtotime("+1 day", $nextdate);

Sponsor our Newsletter | Privacy Policy | Terms of Service