Function “get_gresult()”

Estimated reading: 16 minutes

array get_gresult(numeric $gtabid, numeric $utyp, array $filter, array $gsr, array $verkn, array $onlyfield=null, numeric $single=null, array $extension=null, numeric $pointer=null, bool $lmb_query=null, numeric $detailmode=null)

Function for table queries (./limbas_src/gtab/gtab.lib)

Summary of the parameters:

Specification of the target table with $gtabid.
Changing the number of results, displaying versioned/archived records, sorting the results using $filter.
Filtering the result set using $gsr.
Filtering for linked records using $verkn.
Selection of result fields using $onlyfield.
Query for a single record using $single.
Extending SQL-queries using $extension.
Pagination for queries using $pointer.
SQL Query in global variable using $lmb_query.
Level of detail of the output using $detailmode.

Parameters:

$gtabid

Valid Table ID: The query is applied to the appropriate table.
Invalid Table ID: The query will return an error message.

$utyp

For $utyp, the following values are valid:

1: Default
0: Setting for special cases, which are not further explained here.

$filter

null: All data records that match the criteria of the other transfer parameters are included in the return value
Valid settings in the $filter-array affect the internal result set of the database or the data records in the return value:

Expanding the internal result set

$filter["nolimit"][$gtabid] = $nolimit;

For $nolimit, the following values are valid:

1: The number of data records in the internal result set of the database is unlimited.
0: The number of data records in the internal result set of the database is limited by the value of the environment variable ““resultspace”.

Pagination

$filter["anzahl"][$gtabid] = $anzahl;

For $anzahl, the following values are valid:

‘all’: All data records from the internal result set of the database are included in the return value.
Number greater than 0: “$anzahl” entries from the internal result set of the database are included in the return value.
Default: If there is no value for this setting, the value of the setting “max. hits” is taken from the User Data of the currently logged-on user.

$filter["page"][$gtabid] = $page;

For $page, the following values are valid:

Number less than or equal to 1: The data records, that are included in the return value, are selected from the internal result set of the database without Offset.
Number greater than 1: The data records, that are included in the return value, are selected from the internal result set of the database with the Offset “$page * $anzahl “. If this Offset is greater than the number of data records of the internal result set, no Offset is used.

Sorting

$filter["order"][$gtabid][$ordernumber] = array($gtabid,$tabellenfeldId,$order);

With the ascending $ordernumber further sort levels can be specified. The $ordernumber usually begins with 0.

For $order, the following values are valid:

‘ASC’: he data records in the internal result set of the database are sorted in ascending order according to the contents of the table field which is specified by the table ID “$tabellenfeldId”.
‘DESC’: The data records in the internal result set of the database are sorted in descending order according to the contents of the table field which is specified by the table ID “$tabellenfeldId”.

To sort by further table fields, when values in the 1st sort are identical, several such commands may be used.

Filtering for data record attributes

archived

$filter["unhide"][$gtabid] = $unhide;

For $unhide, the following values are valid:

-1: Records are not filtered
0: Only records that are not archived are returned
Other value: Only archived records arre returned

versioned

$filter["viewversion"][$gtabid] = $viewversion;

For $viewversion, the following :

0: Setting without effect
Number not equal 0: Only archived records are included in the return value

locked

$filter["locked"][$gtabid] = $locked;

For $locked, the following values are valid:

0: Setting without effect
Number not equal 0: Only locked data records are included in the return value

with resubmission

$filter["gfrist"][$gtabid] = $gfrist;

For $gfrist, the following values are valid:

0: Setting without effect.
Number not equal to 0: Only data records, whose ‘Resubmission time’ is expired, are included in the return value.

Linked record-IDs

$filter["relationval"][$gtabid] = 1;

The filter “relationval” causes the return value to be the IDs of linked records obtained as an array rather than the number of linked records.

Filtering for validity

$filter["validity"][$gtabid] = $date;

Über den Filter “validity” kann eine mit dem Parameter “validity” gekennzeichnete Tabelle nach einem Datum gefiltert und somit nur die in diesem Zeitraum gültigen Datensätze angezeigt werden. NULL Werte in den entsprechenden von/bis Feldern werden als gültig betrachtet. Folgende Werte sind zulässig:

Using the filter “validity”, a table marked with the parameter “validity” can be filtered for a date. This only returns records that are within the valid time frame.

[date] (valid records of the specified date are returned)
‘all’ (valid and invalid records are returned)
‘allfrom’ (valid and future records are returned)
‘allto’ (valid and past records are returned)
‘allactive’ (all records of the last version within a version strand are returned)

Other

Further settings of the $filter-Array are meant for special cases (not explained here).

$filter["report"][$gtabid] = $report;
$filter["hidelocked"][$gtabid] = $hidelocked;
$filter["nosverkn"][$gtabid] = $nosverkn;
$filter["alter"][$gtabid] = $alter;
$filter["groupheader"][$gtabid]
$filter["groupheaderKey"][$gtabid]
$filter["tabulatorKey"]
$filter["gwidth"][$gtabid]
$filter["ext_RelationFields"]
$filter["formrelation"]
$filter["getlongval"]
$filter["report"]

$gsr

null: All data records, that match the criteria of the other transfer parameters are included in the return value.
Through valid settings in the $gsr-array, filters can be defined which cause only data records with specific content to be taken into account in the return value.

A ‘gsr filter’ is defined by an operation and an operand. When evaluating, the operation with the contents of the corresponding data field and the operand is executed. A true/false value arises as a result. Several ‘gsr filters’ can be combined by AND / OR. If the end result is ‘false’, the data record is not included in the return value..

With the following constructs, operation and operand of the filter can be used (the variable “$laufendeNr” can be selected here, but it must be identical for each of the two or three assignments of a filter):

Texts

Text operation without case sensitivity

$gsr[$gtabid][$tabellenfeldId][$laufendeNr] = $operand;
$gsr[$gtabid][$tabellenfeldId]['txt'][$laufendeNr] = $operationID;

The following values are available for the $operationID:

1: Check whether the text specified in the operand is in the contents of the data field (%$ %) (default, if only the operand is specified)
2: Check whether the text specified in the operand is identical to the content of the data field (==)
3: Check whether the content of the data field begins with the text specified the in the operand ($%)
4: Check whether the content of the data field sounds similar to the specified text ( metaphone )
5: Check whether the content of the data field ends with the specified text ( %$ )
7: Check whether the content of the data field is empty
8: Check whether the content of the data field is not empty

When using Postgres Indexing

9: Check whether all words are featured in the text
10: Check whether one word is featured in the text
11: Check whether the sentence is featured in the text

Case Sensitive Text operation

$gsr[$gtabid][$tabellenfeldId]['cs'][$laufendeNr] = 1;

Text operation for texts from files or data fields of the type long using index search

$gsr[$gtabid][$tabellenfeldId][$laufendeNr] = $operand;
$gsr[$gtabid][$tabellenfeldId]['string'][$laufendeNr] = $operationID;

The following values are available for the $operationID:

1: Check whether the text specified in the operand in the content of the data field or the referenced file is included taking into account case sensitivity. This operation is only possible when indexing all words in the order of occurrence (environment variable “indize_level” = 2).

Numbers

Numerical operation

$gsr[$gtabid][$tabellenfeldId][$laufendeNr] = $operand;
$gsr[$gtabid][$tabellenfeldId]['num'][$laufendeNr] = $operationID;

The following values are available for the $operationID:

1: Check whether the value specified in the operand is equal to the contents of the data field ( = )
2: Check whether the value specified in the operand is greater than the content of the data field ( > )
3: Check whether the value specified in the operand is less than the content of the data field ( < )
4: Check whether the value specified in the operand is less than or equal to the content of the data field ( <= )
5: Check whether the value specified in the operand is greater than or equal to the content of the data field ( >= )
6: Check whether the value specified in the operand is not equal to the contents of the data field ( != )
7: Check whether the data field is empty (NULL)
8: Check whether the data field is not empty (NOT NULL)

Date

$gsr[$gtabid][$tabellenfeldId][$laufendeNr] = $operand;
$gsr[$gtabid][$tabellenfeldId]['num'][$laufendeNr] = $operationID;

The following formats are supported for $operand (designation: examples)

Calendar week: KW3, KW 4 2020, cw 14
Only year: 1976, 2020
Year with month 12.1976, 4.2020
Full date (with or without time) (formats supported by strtotime())

The following formats are supported for the $operationID: See Numbers.

Negation

Negating a query

$gsr[$gtabid][$tabellenfeldId][$laufendeNr] = $operand;
...
$gsr[$gtabid][$tabellenfeldId]['neg'][$laufendeNr] = 1;

Multiple filters (and/or)

When multiple filters are applied to a data field, it must be defined as follows whether they are linked by ‘AND’ or ‘OR’:

$gsr[$gtabid][$tabellenfeldId]['andor'][$laufendeNr] = $operationID;

The following values are available for the $operationID:

1: The new filter is linked with the current filter by ‘AND’ (default)
2: The new filter is linked with the current filter by ‘OR’

For several links with alternating link types (‘AND’/‘OR’) the priority depends on the database used. The environment variable “searchcount” defines how many filters per data field can be set.

When a filter is applied to multiple data fields, it must be defined as follows whether they are linked by ‘AND’ or ‘OR’. (Switching between AND / OR is not possible here):

$gsr[$gtabid]['andor'] = $operationID;

The following values are available for the $operationID:

1: The results of the filter are linked with ‘AND’ (default)
2: The results of the filter are linked with ‘OR’

$verkn

null or 0: All data records that match the criteria of the other transfer parameters are included in the return value.
Array, that is generated using the function init_relation(): In the return value only data records, which are linked with the table field specified by the transfer parameters init_relation() are taken into account.
Invalid value: The return value contains no data records.

$onlyfield

null: The data records in the return value include all table fields.
$onlyfield[$gtabid] = array($tabellenfeldId1, $tabellenfeldId2, …): The data records in the return value include only the listed table fields.
If an array() is transferred with invalid data for this query, the return value contains no records
If only the string ‘ID’ is transferred, the return value contains only system-specific information, and the ID of the data records.

$single

This parameter is intended for use in tables (not for queries). It should be used only if $pointer= null / 0. If this value is set, the $verkn parameter is ignored.

null or 0: All data records that match the criteria of the other transfer parameters are included in the return value.
Valid record ID: The return value contains only the data record with the specified record ID, provided it matches the criteria of the other transfer parameters.
Invalid record ID: The return value contains no data records.

$extension

The generated SQL statement can be individually extended via this parameter.

The following extensions are possible:

select
- Expands the “SELECT” component of the SQL statement. Custom fields or aliases can be added. $extension[‘select’][0] = “TABLENAME.FIELDNAME“;
from
- Expands the “FROM” component of the SQL statement. Custom tables or aliases can be added. $extension[‘from’][0] = “TABLENAME“;
where
- Expands the “WHERE” component of the SQL statement. $extension[‘where’][0] = “TERM“;
order
- replaces the internal sorting $extension[‘order’][0] = “FIELDNAME“;
ojoin
- Expands the “OUTER JOIN” component of the SQL statement. Custom LEFT/RIGHT (OUTER) JOINS can be added. $extension[“ojoin”][LEFT TABLENAME][RIGHT TABLENAME][0] = “CONDITION“; // LEFT (OUTER) JOIN $extension[“ojoin”][LEFT TABLENAME][RIGHT TABLENAME][‘LEFT’][0] = “CONDITION“; // LEFT (OUTER) JOIN $extension[“ojoin”][LEFT TABLENAME][RIGHT TABLENAME][‘RIGHT’][0] = “CONDITION“; // RIGHT (OUTER) JOIN
distinct
- Extends query for ignoring duplicate results. $extension[“distinct”] = “DISTINCT”;

Example 1 (select, from, where, order)

All employees that have a company car should be displayed with the car brand, sorted by the brand:

require_once('../limbas_src/gtab/gtab.lib');

//Additionally take the field "BRAND" out of the table "COMPANYCARS"
$extension['select'][0] = "COMPANYCARS.BRAND";
$extension['from'][0] = "COMPANYCARS";

// Only display matching employees/cars
$extension['where'][0] = "EMPLOYEE.LICENSEPLATE = COMPANYCARS.LICENSEPLATE";

// Sort alphabetically by brand
$extension['order'][0] = "BRAND";

// Only show name of employee (Field-ID: 1) and brand
$onlyfield[52] = array(1, 'BRAND');

// call get_gresult
$gresult = get_gresult(52, 1, $filter, null, null, $onlyfield, null, $extension);

Result (extract):

...
[1] => Array
     (
          [0] => August Spieldose
          [1] => Maximilian Oberhauser
          [2] => Alfred Unterberger
     )

[BRAND] => Array
     (
          [0] => Fiat
          [1] => Mercedes
          [2] => Opel
     )
...

Example 2 (select, ojoin, order)

Now we want to display all employees with their car brand (regardless of whether they own a company car or not), sorted by the car brand:

require_once('../limbas_src/gtab/gtab.lib');

// Additionally take the field "BRAND" out of the table "COMPANYCARS"
$extension['select'][0] = "COMPANYCARS.BRAND";

// Take all employees
$extension['ojoin']['EMPLOYEES']['COMPANYCARS'][0] =  "EMPLOYEE.LICENSEPLATE = COMPANYCARS.LICENSEPLATE";

// Sort by brand
$extension['order'][0] = "BRAND";

// Only show name of employee (Field-ID: 1) and brand 
$onlyfield[52] = array(1, 'BRAND');

// call get_gresult
$gresult = get_gresult(52, 1, $filter, null, null, $onlyfield, null, $extension);

Result (extract):

...
[1] => Array
     (
          [0] => August Spieldose
          [1] => Maximilian Oberhauser
          [2] => Alfred Unterberger
          [3] => Salamir Solemio
     )

[BRAND] => Array
     (
          [0] => Fiat
          [1] => Mercedes
          [2] => Opel
          [3] => 
     )
...

Example 3 (select, ojoin using a link table)

All customers should be returned together with their contacts (if there are any).

require_once('../limbas_src/gtab/gtab.lib');

$table_id_kunden = 4;

// To return the field NAME of the table CONTACTS, it has to be inserted into the SELECT statement.
// To prevent confusion between the field NAME in CUSTOMERS and the field NAME CONTACTS, the alias CONTACTNAME will be added.
$extension['select'][] = "CONTACTS.NAME AS CONTACTNAME";

// Now the relation between CUSTOMERS -> VERK_17e64eb8914c6 -> CONTACTS will be taken into account.
// The relation VERK_17e64eb8914c6 -> CONTACTS can e.g. be made by using an INNER JOIN.
// Then the table CUSTOMERS can be joined with it using an LEFT (OUTER) JOIN.

// For simplicity, the alias VERKN will be added for the link table

$extension['ojoin']['CUSTOMERS']['(CONTACTS INNER JOIN VERK_17e64eb8914c6 VERKN ON VERKN.VERKN_ID = CONTACTS.ID)'][] =  "CUSTOMERS.ID = VERKN.ID";

// Only the following field should be returned:
// - the name of the customer (NAME has ID 2 in the table CUSTOMERS)
// - the name of the contact  (the alias CONTACTNAME was assigned previously)
$onlyfield[$table_id_customers] = array(2, 'CONTACTNAME');

// get_gresult will be called using the ID of the table CUSTOMERS (4) and additional parameters
$gresult = get_gresult($table_id_customers, 1, $filter, null, null, $onlyfield, null, $extension);

Result (extract):

...
[2] => Array
     (
          [0] => Alfreds Futterkiste
          [1] => Alfreds Futterkiste
          [2] => Alfreds Futterkiste
          [3] => Antonio Moreno Taquería
          [4] => Around the Horn
          [5] => Blauer See Delikatessen
          [6] => Blondel père et fils
          [7] => Bólido Comidas preparadas
          [8] => Bon app'
          [9] => Bottom-Dollar Markets
          [10] => B's Beverages
     )

[CONTACTNAME] => Array
     (
          [0] => Unterberger
          [1] => Oberhauser
          [2] => Summerey
          [3] => Ronzilius
          [4] => Sundoni
          [5] => Spieldose
          [6] => Unterseer
          [7] => Ibins
          [8] => Fresinger
          [9] => 
          [10] => 
     )
...

$pointer

This parameter is intended for use in queries. It should be used only if $single = null / 0.

null or 0: all the data records that match the criteria of the other transfer parameters are included in the return value.
Valid relative position of a data record in the internal result set of the database: The return value contains only the data record which is located in the internal result set of the database at the given position, provided the data record meets the criteria of the other transfer parameters.
Invalid relative position of a data record in the internal result set of the database: The return value contains no data records.

$lmb_query

If this parameter is “true”, the underlying SQL query used to perform the query is stored in the global variable $GLOBALS[‘lmb_query’].

$detailmode

This parameter defines the level of detail at which the output is formatted. If it isn’t set, the output corresponds to that of the database, with a few exceptions. Otherwise, the output is processed differently depending on the given type. For example links or multiselect fields are left out and numerical fields are formatted. Using detailmode can influence the query’s performance

typ 0/false = default
typ 3 = return for html export (with htmlentities, returns only simple text)
typ 4 = return for soap export with arrays (with htmlentities, returns many details as associated array)
typ 5 = return for raw export with arrays (returns multiple results as simple array)
typ 6 = return for rest api

Return Value:

Some of the possible values of the returned array are explained as follows. Which value is set, depends upon the input parameters.

[$gtabid][res_count]
- Number of data records in the result set of the database, determined by the query
[$gtabid][need_time]
- Time needed for the SQL query in the database
[$gtabid][max_count]
- Number of data records in the result set of the database, determined by the query
[$gtabid][res_next]
- Specifies the position of the first data record of this return value in the internal result set of the database
[$gtabid][res_viewcount]
[$gtabid][LOCK][SELF][]
[$gtabid][LOCK][TIME][]
[$gtabid][LOCK][USER][]
for all ($anzahl) determined data records:
- for all table fields in the table:
  - [$gtabid][$tabellenfeldid][0..($anzahl-1)”]
    - table contents
- [$gtabid][id][0..($anzahl-1)”]
  - Unique ID of the data record assigned by LIMBAS (see also Auto-ID)
- [$gtabid][ERSTDATUM][0..($anzahl-1)”]
  - Creation date of the data record (see also Post-Date)
- [$gtabid][EDITDATUM][0..($anzahl-1)”]
  - Last modification date of the data record (see also Edit-Date)
- [$gtabid][EDITUSER][0..($anzahl-1)”]
  - Name of the user that performed the last modification of the data record (see also Edit-User)
- [$gtabid][ERSTUSER][0..($anzahl-1)”]
  - Name of the user who created the data record (see also Post-User)
- [$gtabid][DEL][0..($anzahl-1)”]
  - 0: The data record is not archived.
  - 1: The data record is archived.
- [$gtabid][INUSE_TIME][0..($anzahl-1)”]
- [$gtabid][INUSE_USER][0..($anzahl-1)”]
- [$gtabid][IS_OWN_USE][0..($anzahl-1)”]
- [$gtabid][VID][0..($anzahl-1)”]
- [$gtabid][VPID][0..($anzahl-1)”]
- [$gtabid][VACT][0..($anzahl-1)”]
- [$gtabid][VDESC][0..($anzahl-1)”]
- [$gtabid][IS_OWN_USER][0..($anzahl-1)”]

Example

ID Definitionen

$ID = 1;
define("CUSTOMER_TAB",5);
define("CONTACT_TAB",3);
define("CONTACT_LASTNAME",1);
define("CONTACT_NAME",2);
define("CUSTOMER_LASTNAME",1);
define("CUSTOMER_NAME",2);
define("CUSTOMER_STREET",8);
define("CUSTOMER_CONTACTS",6);

Output of data records of the ‘Customer’ table with ID = 1 and the data fields ‘Name’, ‘First name’ and ‘Street’:

$gresult = get_gresult(CUSTOMER_TAB,1,null,null,null,array(CUSTOMER_TAB=>array(CUSTOMER_LASTNAME,CUSTOMER_NAME,CUSTOMER_STREET)),$ID);
$result_name = $gresult[CUSTOMER_TAB][CUSTOMER_LASTNAME][0];
$result_firstname = $gresult[CUSTOMER_TAB][CUSTOMER_NAME][0];
$result_street = $gresult[CUSTOMER_TAB][CUSTOMER_STREET][0];

Output of the linked contacts for that customer:

$relation_setting = set_verknpf(CUSTOMER_TAB,CUSTOMER_CONTACTS,$ID,null,null,1,1);
$relation_setting["relext"] = null;
$gresult = get_gresult(CONTACT_TAB,1,null,null,$relation_setting,array(CONTACT_TAB=>array(CONTACT_TAB_LASTNAME,CONTACT_TAB_NAME)));
$result_name = $gresult[CONTACT_TAB][CONTACT_LASTNAME][0];
$result_firstname = $gresult[CONTACT_TAB][CONTACT_NAME][0];

Function “get_gresult()”

Parameters:

$gtabid

$utyp

$filter

Expanding the internal result set

Pagination

Sorting

Filtering for data record attributes

archived

versioned

locked

with resubmission

Linked record-IDs

Filtering for validity

Other

$gsr

Texts

Numbers

Date

Negation

Multiple filters (and/or)

$verkn

$onlyfield

$single

$extension

Example 1 (select, from, where, order)

Example 2 (select, ojoin, order)

Example 3 (select, ojoin using a link table)

$pointer

$lmb_query

$detailmode

Return Value:

Example

Function “get_gresult()”

CONTENTS