nuBuilder Forum

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

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

}

Code: Select all

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

Code: Select all

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

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

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

Code: Select all

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

$t = "#TABLE_ID#";

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

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

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