2. PHPRunner General questions
  3. [SOLVED] Reference current recordID in Code Snippet for custom sql query & output
This topic is locked
[SOLVED]

Reference current recordID in Code Snippet for custom sql query & output

 		9/5/2021 6:28:07 PM
PHPRunner General questions
1NET author

I need to display the percentage of information completed in each child table on my master table list page.

ie:

img alt

I am able to create this percentage figure directly in phpMyAdmin:

Select
((IF(1_completed !='0', 1, 0) +
IF(2_completed !='0', 1, 0) +
IF(3_completed !='0', 1, 0) +
IF(4_completed !='0', 1, 0) +
IF(5_completed !='0', 1, 0) +
IF(6_completed !='0', 1, 0) +
IF(audit_programs_completed !='0', 1, 0) +
IF(matters_for_the_completion_memo_completed !='0', 1, 0) +
IF(management_letter_points_raised_completed !='0', 1, 0) +
IF(audit_differences_raised_completed !='0', 1, 0) +
IF(reviewer IS NOT NULL, 1, 0) +
IF(date IS NOT NULL, 1, 0) ) / 12 * 100) as percentage from audit_analytical_review where auditID='2';

I need to create a code snippet that references the current record's ID. I have created this so far which "SHOULD WORK" in theory

$rs = DB::Query("select
((IF(1_completed !='0', 1, 0) +
IF(2_completed !='0', 1, 0) +
IF(3_completed !='0', 1, 0) +
IF(4_completed !='0', 1, 0) +
IF(5_completed !='0', 1, 0) +
IF(6_completed !='0', 1, 0) +
IF(audit_programs_completed !='0', 1, 0) +
IF(matters_for_the_completion_memo_completed !='0', 1, 0) +
IF(management_letter_points_raised_completed !='0', 1, 0) +
IF(audit_differences_raised_completed !='0', 1, 0) +
IF(reviewer IS NOT NULL, 1, 0) +
IF(date IS NOT NULL, 1, 0) ) / 12 * 100) AS percentage from audit_analytical_review
WHERE audit_analytical_review.auditID = "GET CURRENT RECORD ID FOR THIS LINE IN THE LIST TABLE");

while( $data = $rs->fetchAssoc() )
{
echo $data["label"];
}

Any assistance would be greatly appreciated
admin 9/6/2021

I highly recommend check this Taming the beast article that explains the differents between events, buttons, code snippets, 'View as' Custom etc.

The thing with code snippets is that they will be executed once on the page load meaning you cannot access any specific record data (one snippet, many records, won't work). What you can do though is to use 'View as' Custom where you can access any field value that belongs to the same record.
1NET author 9/9/2021

I have looked at the "Taming the beast" article, but the link from that page

VIEW AS: CUSTOM
Use View As type Custom to modify the way field displayed on List/View/Print pages. View as: Custom provides access to any field value within the same record, which you can reference with the following syntax: $data["FieldName"]

More info on "View as: Custom"
https://xlinesoft.com/phprunner/docs/_view_as__settings_custom.htm
does not exist anymore

So no advanced information is available hence me having to turn to the forum for help.
admin 9/9/2021

If you Google "phprunner view as custom" it will take right to the page in question.

1NET author 9/18/2021

I was able to solve this myself.
Certainly i've created a new database column called AR_status. Even though I don't need to update the stored value in the database it sits on the list page as a "dummy" field that can be set to "View as Custom" - Thanks to admin for the initial direction.

My issue has amplified as the math behing showing the percentage of completeness of a single row/record based on a custom set of fields and not all fields in the db table never gave the correct result.

My database table has several fields that are updated by a checkbox updating the field as either 1 or 0, the other fields I wanted to use contained text data so the query was based around the MySQL function "case".

here is the final code for my table

$currentAuditID = $data["auditID"];

$sql = "select
(
1_completed
+
case 1_performed_by when '' then 0 else 1 end
+
2_completed
+
case 2_performed_by when '' then 0 else 1 end
+
3_completed
+
case 3_performed_by when '' then 0 else 1 end
+
4_completed
+
case 4_performed_by when '' then 0 else 1 end
+
5_completed
+
case 5_performed_by when '' then 0 else 1 end
+
6_completed
+
case 6_performed_by when '' then 0 else 1 end
+
audit_programs_completed
+
matters_for_the_completion_memo_completed
+
management_letter_points_raised_completed
+
audit_differences_raised_completed
+
case reviewer when '' then 0 else 1 end
+
case date when '' then 0 else 1 end
)
* 100 / 18 AS percentage from audit_analytical_review where auditID=$currentAuditID";

$rs = CustomQuery($sql);
$data1 = db_fetch_array($rs);
$percent = round($data1["percentage"]);
$value = "$percent %";