I am working on a report where I create #dataTable# by selecting the fields in another table. I then alter #dataTable# by adding some additional fields. After that I set up a while loop in which I do some calculations and then update the additional fields in #dataTable#. Here is a sample of some of the code:
Code: Select all
nuRunQuery(
<<<EOSQL
CREATE TABLE #dataTable#
SELECT violations.*
FROM violations
WHERE vio_resolution = '0'
ORDER BY vio_violation, vio_building_unit
EOSQL
);
// Routine to calculate aging
nuRunQuery("ALTER TABLE #dataTable# ADD age_1 int(5), ADD age_2 int(5) ");
$resultSet = nuRunQuery("SELECT * from #dataTable# ");
$today = date("Y-m-d");
while ($violations_id = db_fetch_object($resultSet)) {
..............
$age_1 = round(abs(strtotime($today)-strtotime($first_date))/86400);
$age_2 = round(abs(strtotime($today)-strtotime($recent_date))/86400);
nuRunQuery("UPDATE #dataTable# SET age_1 = '$age_1', age_2 = '$age_2' ");
}
Code: Select all
nuRunQuery("UPDATE #dataTable# SET age_1 = '$age_1', age_2 = '$age_2' WHERE violations_id = '$violations_id' ");
How do I need to format the update statement to make it work?
BTW, I am using the PHP code in the 'Debtors Aged Trial Balance Report' from samplenufinancial as a guideline for this report.
Thanks,
John