Count(*) different in php vs MySQL query
Hi,
My 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);