How to test PHP code in the procedures

[icoso]

Is there a way to run the PHP code in a procedure and see the results? When I try to use Run Procedure on this code I get nothing.

Code: Select all

$s = "
SELECT TaxCustomers.*, CONCAT(cust_lastname, ', ', cust_firstname) AS full_name FROM TaxCustomers WHERE TaxCustomers.tax_billdate BETWEEN '2021-01-01' AND '2021-03-01';
";
$t = nuRunQuery("$s");
while($r = db_fetch_array($t)){
    nuDebug('Encrypted Primary SSN= ' . $r['cust_prissn'].'  Spouse SSN= '. $r['cust_secssn']);
}

Im trying to see how the PHP functions: db_fetch_array($t), db_fetch_object($object1) , db_fetch_row($object1) differ. Im needing to do this for a report so that I can run a function to unencrypt the cust_prissn & cust_secssn fields and then save them back in the temporary table that is generated by the SQL statement, before the report is displayed.

I can do all of this outside of nuBuilder, running my own PHP code, Im trying to figure out how to do it inside of nuBuilder using nuBuilder functions.

[icoso]

UPDATE to Above^ I got this working:

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

";

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

$t = nuRunQuery($s);

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);
    $r['cust_prissn'] = $newcustprissn;
    $r['cust_secssn'] = $newcustsecssn;
}

This above works and I see the unencrypted SSNs in the debug messages but nothing shows up on the report. I know my call to unencryptssn() works because of my debug messages. However, If I use this to run the query instead:

$t = nuRunQuery("CREATE TABLE #TABLE_ID# $s");
The debug messages and the field assignments do not appear work AND my report displays the original encrypted SSNs. Any idea why? what should I do to update the data in the temporary table?

[kev1n]

How do you run the procedure?

And if you add additional nuDebug(), do you see an output?

Code: Select all

nuDebug('First Line');

$s = "
	SELECT taxcustomers.*,
		   Concat(cust_lastname, ', ', cust_firstname) AS full_name
	FROM   taxcustomers
	WHERE  taxcustomers.tax_billdate BETWEEN '2021-01-01' AND '2021-03-01'; 
";


$t = nuRunQuery($s);

nuDebug('Number of rows', db_num_rows($t));

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

PS: Please use code tags. they help make your code easier to read for others! (Select the your code and click the Code button in the toolbar.

[icoso]

How do you run the procedure?

PS: Please use code tags. they help make your code easier to read for others! (Select the your code and click the Code button in the toolbar.

Did you see my 2nd post/ update on this? Why would the debug messages not display anything when using the create table in the nurunquery? How do I make changes to the data in the temporary table?

How would 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?

This is great: https://wiki.nubuilder.cloud/ ... orts#Table

It shows you how to create a temp table. But then nothing! How do I access, and/or modify what's in the temporary table once I've created it? My normal way of getting data from a table using PHP would be to use a query:

Code: Select all

  
    $db = mysqli_connect("localhost","myusername","mypassword","mytablename");
   $tcsql = "SELECT * FROM TaxCustomers"; 
   $tcresult = mysqli_query($db,$tcsql);                                               //  (where the $db is the database to open.  HOW DO I GET THE $db within nuBuilder for a temporary table???)

then I would  use: 
   $tcmyrow = mysqli_fetch_array($tcresult, MYSQLI_ASSOC);  
do {
   $newssn=$tcmyrow['cust_prissn'];
   $unencssn = unencryptSSN($newssn);
  $s = "UPDATE  #TABLE_ID# SET 'cust_prissn'=$newssn WHERE TxCust_id=$tcmyrow['TxCust_id']";
  $updresult = mysqli_query($db,$s);                                                   //  (where the $db is the database to open.  HOW DO I GET THE $DB within nubuilder???)
} while ($tcmyrow = mysqli_fetch_array($tcresult, MYSQLI_ASSOC)); 

How do I do this using nuBuilder functions to access the temporary table? What functions do I use, how do I use them to access the temporary table?

[icoso]

Anyone have any suggestions????

[apmuthu]

file_put_contents()

[icoso]

file_put_contents()

Im not writing to a file. Im writing to a temporary database. How would this be helpful?

[apmuthu]

For debugging purposes.