2. PHPRunner Tips and Tricks
  3. Guide 37 – In data export, delete columns without content
This topic is locked

Guide 37 – In data export, delete columns without content

 		6/2/2022 4:35:58 PM
PHPRunner Tips and Tricks
fhumanes author

Among the many support queries that I have and that I try to find a solution for, I got this one about wanting to eliminate the columns in the export, of those fields that do not have any information among the selected records, that is, that all their values are null.

The need arose because many fields are defined in the design of its data model and there are times when many of them are empty.

Objetive
In the export of the data of an entity, delete the information of the fields if all the values ​​of those fields in the selected records have the null value.

DEMO: https://fhumanes.com/export

Technical Solution

I have divided the solution into 2 parts:

  • Identify which fields, in all their contents, only have the null value.
    What I do is apply the SQL function " max() ". If the result is null, then all its values are null.


  • Once the fields have been identified, remove them from the export.
    I have looked in the documentation and in the generated code, where I could intervene to exclude the fields that I had identified in the previous step.



The resulting code is only in the " Before SQL query " event:

// Obtener la columnas que todos sus valores son nulos
$sql = "SELECT max(campo1) campo1, max(campo2) campo2, max(campo3) campo3, max(campo4) campo4 FROM export";
if ( $strWhereClause <> '' ) {
$sql .= " where ";
$sql .= $strWhereClause;
}

$rs = DB::Query($sql);
$data = $rs->fetchAssoc();
$_SESSION["Exclusion"] = [];
$array_key = array_keys($data);
foreach ($array_key as &$valor) {
if ( $data[$valor] == NULL ){
$_SESSION["Exclusion"][] = $valor;
}
}
// En variable de sesión "Exclusion", están las columnas que todos sus valores son nullos
// Eliminación columnas que todos sus valores son nulos de los campos a exportar
$length = count($pageObject->selectedFields);
for ($valor1 = 0; $valor1 < $length; $valor1++) {
foreach ($_SESSION["Exclusion"] as &$valor2) {
if ( $pageObject->selectedFields[$valor1] == $valor2 ) {
unset($pageObject->selectedFields[$valor1]);
}
}
}

Perhaps this example may be of little use, but it is important to know the information of the “ selectFields ” variable of the “ pageObject ” object , which seems to be common to all PHPRunner actions/pages and that has the list of columns that are going to be displayed in the output result.

As always, if you have any questions, let me know at my email fernandohumanes@gmail.com and I'll leave you the project in case you want to do tests on your PCs.