Quick links: Content - sections - sub sections
EN FR

XML methods are useful and pratical inlost of case. Anyhow, they have some limits and are useless for complex queries.

One could for sure code those complex queries in classic PHP using jDb directly. However, if your datasets associated are already mapped through a DAO, it would be sensible to insert such queries in the DAO factory.

Declare a PHP method

It is somewhat simple. You have to declare in your XML factory a method of type "php":


  <method type="php" name="foo">
    <parameter name="bar" />
    <body><![CDATA[   
        php code
    ]]></body> 
  </method>

XML tags used in classic methods can not be used here (conditions, order...) as you have to code directly your SQL query. However, you still have to define a method name and you can use parameter.

factory internal API

You can almost do everything in your PHP code but remember to:

  • Respect the DAO pattern. You have to return record objects as defined by the DAO. Record class name is defined by _DaoRecordClassName property
  • Use _conn jDbConnection property to execute queries.
  • Use other helpers methods and properties.

Code pattern

There are 2 ways to code PHP methods:

  1. either hardcoding queries
  2. either coding queries by using factory and record informations (table names, field names, field types, and so on)

The first one performs a bit better and is simpler. But the latter is more maintainable as the generated query parts are updated when datasources or properties are modified. It is up to you to choose which one is your favourite.

Below is a list of properties and methods, you should use for the more maintainable way of coding PHP methods:

  • $this->_primaryTable : primary table alias,
  • $this->_tables : table informations,
  • $this->getProperties() : detailed record properties informations,
  • $this->getPrimaryKeyNames() : primary key properties.

On a record object, getProperties() and getPrimaryKeyNames() are also defined.

For details, see reference documentation.

Prepare values

Each value passed as parameter has to be "filtered" or "prepared" before being injected in a SQL query. This prevents security problems of SQL injection.

_prepareValue() method does this. It accepts two arguments : a value and a data type (which refers to datatype attribute of a property tag).


  <method type="php" name="updateLabel">
    <parameter name="id" />
    <parameter name="label" />
    <body><![CDATA[
        $sql = 'update products set label=' . $this->_prepareValue($label,'string');
        $sql.= ' where product_id='.$this->_prepareValue($id,'integer');
        $this->_conn->exec($sql);
    ]]></body> 
  </method>

Or else


  <method type="php" name="updateLabel">
    <parameter name="id" />
    <parameter name="label" />
    <body><![CDATA[
        $sql = 'update '.$this->_tables[$this->_primaryTable]['realname'];
        $sql.= ' set label=' . $this->_prepareValue($label,'string');
        $sql.= ' where product_id='.$this->_prepareValue($id,'integer');
        $this->_conn->exec($sql);
    ]]></body> 
  </method>

SELECT queries

Usually, if you code a SELECT query, you should return every field declared by property tags and thus respect join statements. Moreover objects returned should be of dao record type. Some helpful properties simplifies the task greatly:

  • _selectClause : defines SELECT clause with all declared fields.
  • _fromClause : defines FROM clause with all table names and external joins.
  • _whereClause : defines WHERE clause with inner joins.

A method which return results would look like:


  <method type="php" name="findByPrice">
    <parameter name="price" />
    <body><![CDATA[
        $sql = $this->_selectClause.$this->_fromClause.$this->_whereClause;
        $sql .= ($this->_whereClause == ''?' WHERE ':' AND ');
        $sql .= ' price = '. $this->_prepareValue($price,'float');
        $sql .= ' ORDER BY label ASC';

        $rs = $this->_conn->query($sql);
        $rs->setFetchMode(8,$this->_DaoRecordClassName);
        return $rs;
    ]]></body> 
  </method>

Note: this method could very well be declared in full XML but it is provided here for example.

  • _whereClause can be empty and therefore has to be tested
  • Value has been filtered by _prepareValue(), query is then "safe"
  • It uses $this->_conn to execute query. A jDbRecordSet is returned
  • Mandatory : setFetchMode(), sets the class name of record objects returned. _DaoRecordClassName is passed.
  • the record set is returned directly : jDbRecordSet implements Iterator, and as a result can be iterated in a foreach loop. No need of an intermediate list.

Single record query

It is not so different from the above:


  <method type="php" name="findByLabel">
    <parameter name="label" />
    <body><![CDATA[
        $sql = $this->_selectClause.$this->_fromClause.$this->_whereClause;
        $sql .= ($this->_whereClause == ''?' WHERE ':' AND ');
        $sql .= ' price = '. $this->_prepareValue($label,'string');

        $rs = $this->_conn->query($sql);
        $rs->setFetchMode(8,$this->_DaoRecordClassName);

        $record = $rs->fetch();
        return $record;
    ]]></body> 
  </method>

Note the use of fetch().

Other queries

As for UPDATE, DELETE, INSERT queries, you obviously won't use _selectClause, _fromClause and _whereClause.

Still another helper method can be used for UPDATE et DELETE: _getPkWhereClauseForNonSelect(). It generates a WHERE condition on the primary keys.


  <method type="php" name="updateLabel">
    <parameter name="id" />
    <parameter name="label" />
    <body><![CDATA[
        $sql = 'update products set label=' . $this->_prepareValue($label,'string');
        $keys = array_combine($this->getPrimaryKeyNames(), array($id));
        $sql.= $this->_getPkWhereClauseForNonSelect($keys);

        $this->_conn->exec($sql);
    ]]></body> 
  </method>

If there is more than one primary key, _getPkWhereClauseForNonSelect is actually interesting.