Skip navigation links

MySQL Forums :: PHP :: Count(*) different in php vs MySQL query


Advanced Search

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 Tim Elliott 03/22/2011 12:33PM
Re: Count(*) different in php vs MySQL query Peter Brawley 03/22/2011 01:30PM
Re: Count(*) different in php vs MySQL query Tim Elliott 03/23/2011 11:54AM
Re: Count(*) different in php vs MySQL query Peter Brawley 03/23/2011 01:17PM
Re: Count(*) different in php vs MySQL query Tim Elliott 03/23/2011 01:39PM


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.