Serialized SQL Queries

SavedQuerys are ones that can be reused throughout the application. They are between serialized objects and stored procedures.

Forms, Reports, Events and Classes use SavedQuerys to retrieve data from the database. They are plain SQL statements with or without parameters.

The serialized sqlSavedQuery objects are stored in the savedquery/ folder.

Example : Contents of the file : catalog.productInCategory.sq.xml

<?xml version="1.0"?>
  <savedquery>
    <idsavedquery><![CDATA[]]></idsavedquery>
    <qname><![CDATA[catalog.productInCategory]]></qname>
    <query><![CDATA[SELECT * FROM product WHERE idcategory=[currentcategory]]]></query>
    <qorder><![CDATA[]]></qorder>
    <qpos><![CDATA[]]></qpos>
    <tablenames><![CDATA[product]]></tablenames>
  </savedquery>

The saved query file contains the following tags :

• idsavedquery: uniqid of the savedquery used as the primary key when moved to a database, unused when reports are stored as XML files.
• qname: name of the query. This is the name used to create an instance of the saved query or to link it to forms and reports.
• query: sql statement of the query
• qorder: order sequence of the query if separate from the sql statement.
• qpos: limit sequence of the query if separate from the sql statement.
• tablesnames: name of the table used in the query.

Don't forget to put the main table name of your query in the table name field. It is used to generate default Form and Report templates.

To make your queries more flexible to external variables and changes based upon different contexts you can use parameters in a SavedQuery using the [globalvariablename] notation.

For example :

SELECT * FROM client WHERE idclient='[client]'

When the query is loaded, it will look for a global variable named “client” and replace [client] with the value of the $client variable before executing the query.

If you need some PHP code to process the value of a parameter you can also call a function. The parameters in saved Queries follow a similar logic with the default values in the registry. See the chapter function call for the default value in the Registry chapter. The functions for default values follow the same API and can be used unchanged for saved queries.

   [function_name:param1:param2:param4]

The sqlSavedQuery object is used by the DataObject, Report and ReportForm objects. But you may want to use them for your own objects or for a script inside a Web Page. The sqlSavedQuery object extends the sqlQuery object and adds a method to load an instance of the object.

Example of usage:

<?php
    $q_newclients = new sqlSavedQuery($conx, "new_clients") ; 
    if ($q_newclients->getQueryReady()) {
      $q_newclients->query() ; 
       while($q_newclients->fetch()) {
          echo "<br>".$q_newclients->getData("company") ; 
          echo "-".$q_newclients->getData("position") ; 
       }
    } else {
      echo "<br>the query is missing parameters.  It cannot be executed"; 
    }
?>