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