Problem with updating #dataTable#

[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

[admin]

John,

Are you now just getting a blank screen?

If so, your php has a syntax error.

Steven

[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

[alextouch]

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

[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

[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

[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

[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

[admin]

.