Skip to content

Database & Queries

Running queries, reports and list grids against the project DB.

14 functions. All require apis/main_webservices.php to be included.

On this page: DoListSqlQuery · GetProjectTables · RecPay · SqlError · beautify · getRow · getRows · last_Insert_ID · myExecute · newMaxVal · report · sqlFld · stdCond · tbl

DoListSqlQuery()

Prototype

DoListSqlQuery( $form, $cTable, $redirectOnClick, $lExport=false, $query='', $print=false, $filterfld='', $filterval='', $aWhereForTbl='', $totals=true, $back='', $redir_edit='', $redir_del='', $title='', $repid='', $sumfields=array(), $redir_print='', $gridfilterinfo=array(), $acompid, $masterchild=false, $menu='yes', $averageflds=array() )

Description

  • Builds and renders a full list/grid report from a SQL query, wiring up export, totals, filters and row click-through.
  • The workhorse behind list screens — handles print, Excel export, per-row edit/delete/print redirects and column totals/averages.
  • Table tokens and #compid#/#yearid# in the query are expanded automatically.

Parameters

  • <$form> : form object
  • [$cTable] : table short name
  • [$redirectOnClick] : link page to redirect to, when any entry is clicked. Current entry ID (if present) will be added to the end of link.
  • [$lExport] : Allow to export the report. Defaults to false.
  • [$query] : Query to be adjusted for report directly.
  • [$print] : Allow to print report. Defaults to false.
  • [$filterfld] : Filter field, in case of table
  • [$filterval] : Filter value, in case of table
  • [$aWhereForTbl] : Where condition in case of table
  • [$totals] : Provide totals for report.
  • [$back] : Back link for going back.
  • [$redir_edit] : Redirect link if editing is to be provided.
  • [$redir_del] : Delete link if editing is to be provided.
  • [$title] : Title of report
  • [$repid] : Report ID. Very important. If passed, configuration options will be enabled.
  • [$sumfields] : Array of string field names for which totals should be done automatically.
  • [$redir_print] : Print redirection in case of custom print is required.
  • [$gridfilterinfo] : For internal program use.
  • [$acompid] : Company IDs for which automatic query is to be generated.
  • [$masterchild] : For internal program use.
  • [$menu] : For internal program use.
  • [$averageflds] : Array of string field names on which average has to be done automatically.

Returns

  • String — the prepared query (after parameter adjustment); the grid is rendered as a side effect onto $form.

Return example

DoListSqlQuery($form, '', '', true, $cSql, true, '', '', '', true);
$form->render();   // grid with export + totals

:material-file-code: Source: apis/core.php

GetProjectTables()

Prototype

GetProjectTables( $cProject )

Description

  • Lists all master (mst_) and transaction (trn_) tables defined in a project.
  • Use for schema tooling, migrations and bulk operations across modules.

Parameters

  • $cProject — string name of Project.

Returns

  • Array — names of the project's mst and trn tables.

Return example

$tables = GetProjectTables('shreeart');
// ['mst_item','trn_salesorder', ...]

Usage example

$aTables = GetProjectTables( 'demo' );
returns: { 'mst_sample', 'mst_item', 'trn_sales', 'trn_salesg' }

:material-file-code: Source: apis/core2.php

RecPay()

Prototype

RecPay( $cTable )

Description

  • Returns the receipt/payment configuration settings associated with a table.
  • Drives how a document maps to receipts vs payments in accounting.

Parameters

  • <$cTable> : string : Table name to be queried

Returns

  • Array — the receipt/payment settings for the table.

Return example

$cfg = RecPay('trn_payment');
// ['type'=>'PAY', 'account'=>..., ...]

!!! note If there are no settings available, an array with empty values is returned.

:material-file-code: Source: apis/core.php

SqlError()

Prototype

SqlError()

Description

  • Returns the last SQL error raised by the database layer.
  • Call right after a failed myExecute() to log or display the cause.

Returns

  • String — the most recent SQL error message (empty if none).

Return example

if (!$res = myExecute($sql)) {
    log_info(SqlError(), '.sql.txt');
}
// e.g. "Unknown column 'foo' in 'field list'"

Usage example

$error = SqlError();

:material-file-code: Source: apis/core2_a.php

beautify()

Prototype

beautify($query)

Description

  • Returns a cleanly formatted version of a SQL query (decoded and re-indented).
  • Use for debugging/logging so generated SQL is readable.

Parameters

  • <$query>: Merci Encoded Query

Returns

  • String — the prettified SQL query.

Return example

echo beautify($cSql);
// nicely indented SELECT ... FROM ... JOIN ...

Usage example

$q = beautify( 'SELECT * FROM #fasmast' );

returns 'SELECT * FROM pj_projectname_fasmast'

:material-file-code: Source: apis/core2_a.php

getRow()

Prototype

getrow($res)

Description

  • Fetches the current row from a result resource and advances the cursor to the next row.
  • All column keys are returned in lower case for consistency.

Parameters

  • <$res>: $res as returned from myExecute(...)

Returns

  • Associative array for the current row, or null/false when the cursor is exhausted.

Return example

$res = myExecute("SELECT id, orderno FROM #trn_salesorder#");
while ($row = getrow($res)) {
    echo $row['orderno'];   // keys are lower-case
}

Usage example

 while ($row = getrows($res)){

    ...

 }

:material-file-code: Source: apis/core2_a.php

getRows()

Prototype

getrows($res)

Description

  • Fetches all rows from a result resource returned by myExecute() in one call.
  • Convenient when you want the entire result set as an array to loop/transform.

Parameters

  • <$res>: $res as returned from myExecute(...)

Returns

  • Array of associative rows (MYSQLI_ASSOC); empty/null when there are no rows.

Return example

$rows = getrows(myExecute("SELECT * FROM #mst_item#"));
// [ ['id'=>1,'itemname'=>'YARN A'], ['id'=>2,'itemname'=>'YARN B'] ]

Usage example

$rows = getrows($res);

:material-file-code: Source: apis/core2_a.php

last_Insert_ID()

Prototype

NextYearID()

Description

  • Returns the auto-increment id generated by the most recent INSERT.
  • Call immediately after an insert to capture the new primary key.

Parameters

  • none

Returns

  • Integer — the last inserted id on the current connection.

Return example

myExecute("INSERT INTO #log# (msg) VALUES ('x')");
$id = last_Insert_ID();
// e.g. 5012

:material-file-code: Source: apis/core2_a.php

myExecute()

Prototype

myExecute( $cQuery, $lShowError=true, $lServer=false, $lWarnDuplicate=true, $repltblname=true, $replcompid=true )

Description

  • Executes a SQL statement against the current project database and returns the result resource.
  • Expands #tablename# tokens to real pj_<client>_<table> names and #compid#/#yearid# to the active context.
  • Pass $lServer=true only to target the master/license DB; the default false targets the project DB.
  • Reconnects automatically if the connection dropped.

Parameters

  • $cQuery — Sql Query
  • $lShowError — Throw/Display error if an error is encountered.
  • $lServer — Always pass false
  • $lWarnDuplicate — Warn if duplicate insert/update is performed
  • $repltblname — Replace table names formed using #
  • $replcompid — Replace #compid# with GetCompanyID(), #yearid# with GetYearID()

Returns

  • A mysqli result object for SELECTs (iterate with getrow()/getrows()), or a truthy/affected handle for writes. Falsy on error — inspect with SqlError().

Return example

$res = myExecute("SELECT id, name FROM #fasmast# WHERE compid = #compid#");
while ($row = getrow($res)) {
    echo $row['name'];
}
$res->close();

Usage example

myExecute( 'UPDATE xyztable SET amount=100 WHERE id=200' );
$res = myExecute( 'SELECT * FROM myTable' );
if ($res){
   $res->num_rows    --> returns number of rows effected or present in query
   while ($row = getrow($res)){
       ...
       ...
   }
   $res->close();
 }

:material-file-code: Source: apis/core2_a.php

newMaxVal()

Prototype

NewMaxVal( $field, $table, $where='' )

Description

  • Computes the next available maximum value for a field that is not already used in the table.
  • Use to generate the next running number under an optional WHERE scope.

Parameters

  • $field — Field name of which max value to be computed
  • $table — table name
  • [$where]: any condition to be attached WITHOUT WHERE keyword.

Returns

  • The new maximum value (typically numeric) safe to assign to a new row.

Return example

$next = NewMaxVal('voucherno', 'trn_payment', "compid=#compid#");
// e.g. 1043

Usage example

$nNewValue = newMaxVal( 'serial', 'trn_purchase' );

:material-file-code: Source: apis/core2_a.php

report()

Prototype

report( $title='', $cSql, $sumfields=array(), $back='' )

Description

  • Activates the MERCI report engine and renders a report on screen from a SQL query.
  • Supply a title, the SQL, optional sum fields to total, and an optional back link.

Parameters

  • $title — Title of report
  • $cSql — SQL Query for report
  • $sumfields — array of fields to be summed
  • [$back]: Back Link for back button. Normally addrHomeFolder() . yourphpfile.php

Returns

  • Nothing — outputs the rendered report directly.

Return example

report('Stock Report', $cSql, ['qty','amount'], addrHomeFolder().'menu.php');
// (renders to screen)

Usage example

report( 'MY REPORT', 'SELECT * FROM fasmast', array(), '' );

:material-file-code: Source: apis/core2.php

sqlFld()

Prototype

SqlFld($cTable, $cFld, $cond='1=1', $usecache=false, $yrcocond=true)

Description

  • Fetches a single field value from a table for a given condition (a lightweight scalar lookup).
  • Optional caching and year/company scoping are supported via its flags.

Parameters

  • <$cTable>: Table name
  • : Field name whose value is to be fetched
  • <$cond>: Condition to be applied
  • [$usecache]: Use Caching for fetching and storing value
  • [$yrcocond]: Apply Company & Year Conditions

Returns

  • The value of the requested field from the first matching row (blank if none).

Return example

$name = sqlfld('fasmast', 'account', "id=52");
// e.g. 'ACME Textiles'

Usage example

$gstin = sqlFld('fasmast','gstin','id=123');

:material-file-code: Source: apis/core2_a.php

stdCond()

Prototype

stdCond($alias='',$where=false,$yearid=0, $compid=0,$tbl='',$stkfromyrid=0,$compidreq=true,$stkcntallyr=false)

Description

  • Builds the standard year/company (and optional stock) WHERE condition for the current context.
  • Use to consistently scope queries to the active company/year, including alias prefixes and stock-year nuances.

Parameters

  • [$alias]: main table alias if available. Leave blank if no alias is used.
  • [$where]: set to TRUE if WHERE keyword is to be inserted by this function
  • [$yearid]: Leave 0 to compute automatically, currenty year id
  • [$compid]: Leave 0 to compute automatically, currenty company id
  • [$tbl]: Leave blank. If table is passed, function checks if parcomp field is present and sets condition accordingly
  • [$stkfromyrid]: Stock starting from the specified $stkfromyrid company
  • [$compidreqd]: Leave TRUE to set compid in (...)
  • [$stkcntallyr]: Undocumented feature. Leave false

Returns

  • String — a SQL condition fragment ready to drop into a WHERE clause.

Return example

$cond = stdCond('a');
$sql = "SELECT * FROM #trn_stock# a WHERE $cond";
// e.g. "a.compid=1 AND a.yearid=3"

Usage example

$q = "SELECT * FROM #fasmast " . stdCond();

:material-file-code: Source: apis/core2_a.php

tbl()

Prototype

tbl( $cTable, $lYearID = 0, $lCompID = 0 )

Description

  • Expands a short table name to its full physical name in the project database (pj_<client>_<table>).
  • Marked OBSOLETE in favour of #tablename# tokens, but still the underlying resolver.
  • Optional year/company arguments target a specific period's table.

Parameters

  • $cTable — short table name
  • [$lYearID]: Year ID. Defaults to current year.
  • [$lCompID]: Company ID. Defaults to current company.

Returns

  • String — the complete physical table name in the database.

Return example

$full = tbl('trn_salesorder');
// e.g. 'pj_shreeart_trn_salesorder'

Usage example

$tablename = tbl('trn_sales');
returns: pj_myproject_2_3_trn_sales

:material-file-code: Source: apis/core2_a.php