Get Started
Updated on 21. February 2024

Function “get_gresult()”

Geschätzte Lektüre: 16 Minuten 
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.

  • 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”;

$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)”]
Share this Doc

Function “get_gresult()”

Or copy link

CONTENTS