Page 1 of 1
How to test PHP code in the procedures
Posted: Wed Mar 03, 2021 7:32 pm
by 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.
Re: How to test PHP code in the procedures
Posted: Wed Mar 03, 2021 8:19 pm
by 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?
Re: How to test PHP code in the procedures
Posted: Wed Mar 03, 2021 8:27 pm
by 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.
Re: How to test PHP code in the procedures
Posted: Wed Mar 03, 2021 9:05 pm
by icoso
kev1n wrote: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?
Re: How to test PHP code in the procedures
Posted: Fri Mar 05, 2021 2:28 pm
by icoso
Anyone have any suggestions????
Re: How to test PHP code in the procedures
Posted: Fri Mar 05, 2021 6:44 pm
by apmuthu
file_put_contents()
Re: How to test PHP code in the procedures
Posted: Sat Mar 06, 2021 1:53 pm
by icoso
apmuthu wrote:file_put_contents()
Im not writing to a file. Im writing to a temporary database. How would this be helpful?
Re: How to test PHP code in the procedures
Posted: Mon Mar 08, 2021 1:15 pm
by apmuthu
For debugging purposes.