Useful functions

This contains a list of useful functions, there are of course lots more, but these are the main ones that has to be used.

The functions are listed with other functions defined in the same file.

metadata.php

This is a low level file, and all it's functions are usually wrapped in one of the files below. There are cases when it's useful to access this directly though.

When this is included, and it always is unless you're writing a top level file, you will have a class instance in $metaInformation. The class definition is shown below.

 MetaInformation {
  findMiddleTable($sourceTable,$targetTable);
  getPrimKeyWhere($table,$data);
  getFieldType($table,$field);
  getReferenceTable($table,$field);
  getReferencedTables($table);
  getReferesToTable($table);
  quoteValue($table,$field,$value);
  getColumnArray($table);
  getDisplayArray($table,$field);
  getExtraArray($table,$extra);
  getPrimaryKey($tablename);
}

findMiddleTable will find, if any, the table connecting the source and target tables.

getPrimKeyWhere will return a column=value string where column is the primary key of the table and value is taken from the $data array.

getFieldType will return the type of the field.

getReferenceTable will return the name of the table that the field references.

getReferencedTables will return an array of all tables that are referenced from this table.

getReferersToTable will return an array of all tables that are refers to this table.

quoteValue will return a string with correct quoting depend on the type of the field.

getColumnArray will return an array of names and types for the table.

getDisplayArray will return the display properties of the table.field combination.

getExtraArray will return extra information stored.

getPrimaryKey will return the name of the primary key for the table.

All these are there to help write code that will work on any data, regardless of future changes to the structure.

2lib/selecthelp.php

Here's all functions useful for getting data from the database, either to handle or to simply display to the user.

$row=selectOne($SQL) will return the first record returned by the $SQL statement.

$value=selectOneValue($SQL,$columnname) will return the content of one column in the returned array.

2lib/template.php

This contains two functions and a class to handle templates. templateRow takes two arguments, an array with values and the template text itself. templateQuery also takes two arguments, an SQL statement and the template text. templateQuery will run the query and call templateRow for each resulting row returned.

templateRow($row,$template)

This can be used for simple templates where you only want to do a simple page. See the Template system section to see what can be included in templates.

Example 10.1. templateRow example

templateRow(array("name"=>"Olof Tjerngren","email"=>"olof@two-spot.org"),"Name: %name%<br>Email: %email%<br>"); will return a string like this:

Name: Olof Tjerngren

Email: olof@two-spot.org


templateQuery($query,$template)

This is very similar to templateRow, but takes a query as the first argument, if there is only one match it will work similar to templateRow, if more than one row matches it will repeat the template for each row returned by the query. See the Template system section to see what codes can be used in a templates. Templates can be changed both by the system administrator and used directly in code.

Example 10.2. templateQuery example

templateQuery("select * from cust_entity","Primary key: %cust_no% Name: %name%<br>"); If there are two rows in the cust_entity tables with known content this will be returned:

Primary key: 1 Name: Test 1

Primary key: 2 Name: Test 2


share/updatehelp.php

Update and insert functions are defined here.

list($ok,$text)=doUpdate($table,$row) will execute an update statement on $table with data stored in the $row array. $ok will be true if the update went OK and $text will contain a textual description of what happed, good or bad.

list($ok,$text,$keyvalue)=doInsert($table,$row) will execute an insert statement on $table with data stored in the $row array. $ok will be true if it went OK and $text will contain a text that says what happened. $keyvalue will return the generated primary key if it was set as auto increment or 0 if it isn't.

So a simple update of a field can be this:

$row=selectOne("select * from cust_entity where cust_no=1");

$row["country_code"]="NOR";

list($ok,$text)=doUpdate("cust_entity",$row);

echo $text;

That will fetch the customer with cust_no = 1, set the country_code to NOR and save it back again. Silly example...

share/deletehelp.php

Delete is taken care of separately, it will require a special require command and is named differently.

list($ok,$message)=deleteRec($table,$row) will issue a delete statement to remove the record. If a single value is used for primary key it is sufficient to have that value in the $row array. If it's a multi column primary key all of them have to be supplied. $message can be shown to the user, it will contain a message from the delete operation.

share/formhelp.php

Here are some useful form functions that can come in handy, addRecordLink, editRecordLink and deleteRecordLink will all return a <a> link text that can be used for the obvious reason. They will all open a window that shows the data and ask for confirmation before updating, adding or deleting.

share/fieldhandlersclass.php

Every function used in forms/ is defined here. The basic function to use in forms is inputLayout($row,$tablename,$fieldname). Used in the form <?=inputLayout($row,$tablename,$fieldname)?> in a form file it will show the field with current value if editing in a useful to edit format. The partner function inputLayoutRO takes the same argument but will always show the value as read only, otherwise the inputLayout will show the appropriate form depending on if you're adding a new value, editing an existing value or confirming a delete.

share/graphlib.php

This file holds a low level class to generate a flot graph. It will create a string with html and a script tag that generate a graph based on a sql query.

$tst=new Flot("select if(country_code='','Unknown',country_code) country_code, ".
              "count(*) cnt from cust_entity where cust_type='c' group by country_code");
$tst->setColumns("country_code","cnt");
$tst->setSize(400,200);
$tst->setBar();
echo "<h1>Customers per country</h1>".$tst->getFlotString();

The sample above shows how to create a bar graph with country code on the X axis and number of customers in each on the Y axis.