Page 1 of 1
Problem with updating #dataTable#
Posted: Tue Feb 12, 2013 6:26 am
by JohnKlassen
Hi,
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' ");
}
The above code works except that the values of age_1 and age_2 don't change in the report. This is because I need to tie the values of these fields to the primary key. I tried to fix that by replacing the update statement above with the following code:
Code: Select all
nuRunQuery("UPDATE #dataTable# SET age_1 = '$age_1', age_2 = '$age_2' WHERE violations_id = '$violations_id' ");
If I use this new update statement in the PHP Code for a report, it does not generate the report.
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
Re: Problem with updating #dataTable#
Posted: Wed Feb 13, 2013 12:39 am
by admin
John,
Are you now just getting a blank screen?
If so, your php has a syntax error.
Steven
Re: Problem with updating #dataTable#
Posted: Wed Feb 13, 2013 3:05 am
by JohnKlassen
Steven,
The only difference between whether I get a blank screen or not when running this report is whether I add the 'Where' clause to the update statement.
If I use,
Code: Select all
nuRunQuery("UPDATE #dataTable# SET age_1 = '$age_1', age_2 = '$age_2' ");
I get a report.
If I use,
Code: Select all
nuRunQuery("UPDATE #dataTable# SET age_1 = '$age_1', age_2 = '$age_2' WHERE violations_id = '$violations_id' ");
I get a blank screen.
The 'while' loop points to the same primary key of violations_id:
Code: Select all
while ($violations_id = db_fetch_object($resultSet))
There is more of my code in the original post on this subject. I can't figure out what is wrong with the syntax. It is similiar to other update statements I have seen in samplenufinancial.
John
Re: Problem with updating #dataTable#
Posted: Wed Feb 13, 2013 3:15 pm
by alextouch
JohnKlassen wrote:
The 'while' loop points to the same primary key of violations_id:
Code: Select all
while ($violations_id = db_fetch_object($resultSet))
There is more of my code in the original post on this subject. I can't figure out what is wrong with the syntax. It is similiar to other update statements I have seen in samplenufinancial.
John
Just my 2 cents...
I think that $violations_id is an array, so you have to write something like:
WHERE violations_id = '$violations_id[x]'
where x is the number of the array's elements. It depends by how many columns you require in the query specified into $resultSet.
If your array has only one element, then you have to use $violations_id[0].
Hope this helps.
Alex
Re: Problem with updating #dataTable#
Posted: Thu Feb 14, 2013 4:04 am
by JohnKlassen
Alex,
I appreciate your help. Unfortunately, that did not fix the program. Since my original post, I have rewritten parts of the program but have not touched the update statement. Here is all of the PHP code for this report:
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)) {
$first_date = $violations_id->vio_date_entered_1;
$recent_date = null;
switch ($violations_id->vio_violation_number)
{
case '1' :
$recent_date = $violations_id->vio_date_entered_1;
break;
case '2' :
$recent_date = $violations_id->vio_date_entered_2;
break;
case '3' :
$recent_date = $violations_id->vio_date_entered_3;
break;
case '4' :
$recent_date = $violations_id->vio_date_entered_4;
break;
case '5' :
$recent_date = $violations_id->vio_date_entered_5;
break;
}
$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' ");
nuRunQuery("UPDATE #dataTable# SET age_1 = '$age_1', age_2 = '$age_2' WHERE violations_id = '$violations_id' ");
}
If you look at the code, the update statement that is commented out will generate the report. The update statement that is not commented out shows a blank screen when I run the report.
'violations_id' is the primary key for the violations table. The index is based on 'violations report'.
I have 6 reports ready to give to my client as soon as I figure out how to update #dataTable# using the primary key.
John
Re: Problem with updating #dataTable#
Posted: Thu Feb 14, 2013 4:23 am
by admin
John,
Alex is right
$violations_id is an array not a single variable.
Code: Select all
while ($violations_id = db_fetch_object($resultSet))
..gives you the whole row not just a field. In fact
$violations_id should be more accurately called
$row.
For you to access the primary key you will need to do this
$violations_id->violations_id
Steven
Re: Problem with updating #dataTable#
Posted: Thu Feb 14, 2013 5:32 am
by JohnKlassen
Steven,
Given that $violations_id is an array and not a single variable, what do I need to do to change my code to make the update table work? I did try to change it to
Code: Select all
nuRunQuery("UPDATE #dataTable# SET age_1 = '$age_1', age_2 = '$age_2' WHERE violations_id = '$violations_id[0]' ");
and I still get a blank screen.
Thanks,
John
Re: Problem with updating #dataTable#
Posted: Thu Feb 14, 2013 5:53 am
by JohnKlassen
Steven and Alex,
You pointed me in the right direction and now it works. I changed the code to the following:
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 ($row = db_fetch_object($resultSet)) {
$violations_id = $row->violations_id;
$first_date = $row->vio_date_entered_1;
$recent_date = null;
switch ($row->vio_violation_number)
{
case '1' :
$recent_date = $row->vio_date_entered_1;
break;
case '2' :
$recent_date = $row->vio_date_entered_2;
break;
case '3' :
$recent_date = $row->vio_date_entered_3;
break;
case '4' :
$recent_date = $row->vio_date_entered_4;
break;
case '5' :
$recent_date = $row->vio_date_entered_5;
break;
}
$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' WHERE violations_id = '$violations_id' ");
}
I modified the 'where' clause to refer to $row, used $row when referring to fields in the temp table, added $violations_id as a single variable pointing to the primary key and then referenced $violations_id in the 'where' clause.
Thanks for your help.
John
Re: Problem with updating #dataTable#
Posted: Thu Feb 14, 2013 6:37 am
by admin
.