How to modify Temp table data for reporting

[icoso]

Can anyone help with this?

Im using a PHP Procedure to run an SQL statement the I need to modify some of the fields in the retrieved data. I don't care if its the data in memory or ifs its in data in a temporary table, I just to modify it using PHP BEFORE calling the report.

Code: Select all

$s = "
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name, 9999 as last4SSN FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2020-01-01' AND '2020-03-01';
";

//This will create my unencryption function to modify a couple of fields that are stored as encrypted data int he table but I want to print on my report unencrypted.
include_once(__DIR__ . "/../myunencryptfunction.php");

//This runs the SQL query against my table to retrieve the data  I need.  
$t = nuRunQuery($s);

//This is where Im trying to change the two SSN fields which calls my unencryption function to do so.  I know this works because Im seeing the debug messages show the correct data. 

while($r = db_fetch_array($t)){
    nuDebug('Encrypted Primary SSN= ' . $r['cust_prissn'].'  Spouse SSN= '. $r['cust_secssn']);
    $newcustprissn = unencryptssn($r['cust_prissn']);
    $newcustsecssn = unencryptssn($r['cust_secssn']);
    nuDebug('Encrypted Primary SSN= ' . $newcustprissn.'  Spouse SSN= '. $newcustsecssn);

// This is where I assume I'd be able to  CHANGE the two fields in the resulting data from my query. and from the db_fetch_array($t) command.  THIS DOES NOT WORK. 
    $r['cust_prissn'] = $newcustprissn;
    $r['cust_secssn'] = $newcustsecssn;
}

When I run the above, nothing shows up on the report. So then I tried to do this same thing by creating a Temporary Table, using This command: $t = nuRunQuery("CREATE TABLE #TABLE_ID# $s");
instead of the original nuRunQuery. HOWEVER, The debug messages and the field assignments do not appear work, my report runs and then displays the original encrypted SSNs. ITs like the while loop doesn't execute. Why?

Am I not using the correct function in my while loop when creating a Temporary table to manipulate the data? What should I be using?
How do I.
1. Write the for loop or while loop to get each record from the temporary table and assign the encrypted cust_SSN field to a variable?
2. Call my unencryptSSN(cust_SSN) function? to unecrypt the SSN and return it to a variable?
3. Write the UPDATE statement to put it back in the temporary table in the last4SSN field?
4. delete this temporary table AFTER the report has run?

[kev1n]

I think this could work:

Code: Select all

$sql = " SELECT .... ";

$temp = "#TABLE_ID#";

$t = nuRunQuery("CREATE TABLE ". $temp . " " . $sql);

while($r = db_fetch_array($t)){

	// Manipulate the fields here by running update queries

	nuRunQuery("Update $temp SET ....");

}

[icoso]

Thanks Kev1n,

Do I need a where clause in the SQL to specify the TxCust_id=$tcmyrow['TxCust_id']"?

If the TxCust_id is the first field in the table that was created by nuBuilder when I first created the table, It has a unique code in it for each record.

How do I specify or refer to the the $tcmyrow['TxCust_id'] in nuBuilder terms?

Should my SQL look like:

Code: Select all

nuRunQuery("Update $temp SET 'cust_prissn'=$newssn, 'cust_secssn'=$newcustsecssn where 'TxCust_id'="#TxCust_id#" );

[kev1n]

Like this?

Code: Select all

nuRunQuery("UPDATE $temp SET `cust_prissn`= $newssn, `cust_secssn` = $newcustsecssn WHERE `TxCust_id` = ? ',[ $tcmyrow['TxCust_id'] ]);

[icoso]

Now Im not getting any results.

When using this wiht the same SQL statement I get my one row.

Code: Select all

$s = "
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name, 9999 as last4SSN FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2020-01-01' AND '2020-03-01';

";

$t = nuRunQuery($s);

When using this:

Code: Select all

$s = "
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name, 9999 as last4SSN FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2020-01-01' AND '2020-03-01';
";

$temp = "#TABLE_ID#";
$t = nuRunQuery("CREATE TABLE ". $temp . " " . $sql); 

I get no rows.... Any idea why?

[kev1n]

That's rather strange. It works for me when I create a basic report with this SQL:

Code: Select all

$s = " SELECT `sob_all_id`, `sob_all_label` FROM `zzzzsys_object` ";

$t = "#TABLE_ID#";

nuRunQuery("CREATE TABLE ". $t . " " . $s);

[kev1n]

Does it work for you if you create a report as shown in the attached video?

[icoso]

Ive figured out what the issue was in this little part regarding why Im getting DB error and/or no results.

Code: Select all

$s = "
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name, 9999 as last4SSN FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2020-01-01' AND '2020-03-01'
";

//This will create my unencryption function to modify a couple of fields that are stored as encrypted data in the table but I want to print on my report unencrypted.

include_once(__DIR__ . "/../myunencryptfunction.php");

//This runs the SQL query against my table to retrieve the data  I need. 
//$t = nuRunQuery($s);
//$t = nuRunQuery("CREATE TABLE #TABLE_ID# $s");

$temp = "#TABLE_ID#";
$t = nuRunQuery("CREATE TABLE ". $temp . " " . $s); 

nuDebug('Number of rows='. db_num_rows($t));

//This is where Im trying to change the two SSN fields which calls my unencryption function to do so.  You HAVE to run a new SQL statement on the Tempoary table you can select all the records here. 

$tempsql = "
SELECT * From #TABLE_ID#";
$t = nuRunQuery($tempsql);

while($r = db_fetch_array($t)){
    nuDebug('Encrypted Primary SSN= ' . $r['cust_prissn'].'  Spouse SSN= '. $r['cust_secssn']);
    $newcustprissn = unencryptssn($r['cust_prissn']);
    $newcustsecssn = unencryptssn($r['cust_secssn']);
    nuDebug('Encrypted Primary SSN= ' . $newcustprissn.'  Spouse SSN= '. $newcustsecssn);
}

It only took 3 days to figure this one out. Now on to #3 & #4.

3. Write the UPDATE statement to put it back in the temporary table in the last4SSN field?
4. delete this temporary table AFTER the report has run?

ANy suggestions? How do I access this the data in this Temporary table? How do I update the data in this temporary table?

[kev1n]

3. See above: https://forums.nubuilder.cloud/viewtopic. ... 866#p23355
4. nuBuilder will delete the temp table automatically.

[icoso]

FINALLY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Here's the code that works!
NOTE:The Report is GOING to use the Temporary table that was created NOT the 2nd query, not sure why but it does... For now I just hard coded the dates in my Select for testing. I'll use the dates from a form that calls this report and pass in the dates. (see other posts on how to do that....)

Code: Select all

$s = "
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name, 9999 as last4SSN FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2020-01-01' AND '2020-03-01'
";

//This file contains my unencryption function that is used to modify a couple of fields that are stored as encrypted data in the table but I want to print on my report unencrypted.  I call this function in the while loop.
include_once(__DIR__ . "/../myunencryptfunction.php");

//This runs the SQL query against my table to retrieve the data  I need.  It creates a temporary table so that I can manipulate the data.   Im saving the TABLE_ID here to use later.
$tabid = "#TABLE_ID#";
$t = nuRunQuery("CREATE TABLE ". $tabid . " " . $s); 

// This is just a debug message so I can view whats going on.  You can delete this. 
nuDebug('TempTable='.$tabid.'  Number of rows='. db_num_rows($t));

// After creating the temporary table that has all of my data in it including the encrypted fields,  I have to run a new query and retrieve all of the records in order to use the db_fetch_array($t) in the while loop.  I could not get this to work when trying to access the table directly.   Maybe I was using the wrong database function.  

$tempsql = "SELECT * From $tabid";
$t = nuRunQuery($tempsql);

//This is where Im changing the two SSN fields which calls my unencryption function from my php file above to do so.  
while($r = db_fetch_array($t)){
    nuDebug('Encrypted Primary SSN= ' . $r['cust_prissn'].'  Spouse SSN= '. $r['cust_secssn']);   // just a debug message can be deleted.
    $newcustprissn = unencryptssn($r['cust_prissn']);                                                            // call my function with the encrypted ssn field from the database and assign it to a variable.
    $newcustsecssn = unencryptssn($r['cust_secssn']);                                                           // call my function with another encrypted ssn field from the database and assign it to a variable.
    $TxCustId = $r['TaxCustomers_id'];                                                                                // grab the main unique index of the record to use later in updating the temporary table the report will use.


// This is where I assumed I'd be able to  CHANGE the two fields in the resulting data from my query. and from the db_fetch_array($t) command.  This ONLY changes the data in memory, NOTE the report DOES NOT use this.
    $r['cust_prissn'] = $newcustprissn;
    $r['cust_secssn'] = $newcustsecssn;

//  assign my update SQL statement to a variable to use  below.  I HAD TO put single quotes around the variables in the UPDATE statement, otherwise I was getting some odd SQL errors and odd data stored in the temp table. 
$updsql= "UPDATE $tabid SET `cust_prissn`= '$newcustprissn', `cust_secssn` = '$newcustsecssn' WHERE `TaxCustomers_id` = '$TxCustId'";

// THis puts the data into the temporary table so that my report will print the unencrypted SSN fields.
nuRunQuery($updsql);

Hope this helps someone else!!!! Sorry, Im not posting my encryption routine. There are SEVERAL methods on how to do encryption. The problem you'll run into is generating the same results in the encryption and unencryption routines EVERY time using the same data. Which may or may not work. Another issue is the SALT you use. If you use the exact same SALT every time with the data, you're not "encrypting" very well. I prefer to use a variable SALT, but in this case and in using these SSN fields as search fields it becomes difficult to generate a unique SALT. Basically you have to write your OWN encryption routine and use some standard encrypting functions too and then customize it to fit your needs. The encryption routine I use can be used for ANY type of data, text or numeric (not graphics), not just for SSN's. I can pass it any string to encrypt and decrypt.