MySQL Forums
Forum List  »  PHP

Count(*) different in php vs MySQL query
Posted by: Tim Elliott
Date: March 22, 2011 12:33PM

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

Options: ReplyQuote


Subject
Written By
Posted
Count(*) different in php vs MySQL query
March 22, 2011 12:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.