Common elements

Within the set of sql_* functions, certain parameters are systematically available and are used to denote the same information. These parameters are all described here, principally so that they are not repeated ad infinitum in multiple articles:

  • $serveur (or $connect) is the name of the SQL connection file (in the config/ directory. When not defined or empty, then the connection file defined during SPIP installation will be used. Normally it is the penultimate (last but one) parameter for the SQL abstraction functions.
  • $options equals true by default and is used to specify an optional character with its usage. This parameter is normally the last for the SQL abstraction functions. It may have the following values:
    • true: any function in the SQL API and not found in the SQL instruction set of the requested server will cause a fatal error.
    • 'continue': no fatal error if the function is not found.
    • and false: the SQL set function does not run the query which has been calculated, but should return it instead (we therefore obtain a text string that is a valid SQL query for the database manager requested).

Some other parameters are often present from one function to another, particularly so for all functions which are similar to sql_select() by reusing all or some of its parameters:

  • $select, table of SQL columns to be retrieved,
  • $from, table of SQL tables to be used,
  • $where, table of column constraints where each element in the table will be combined with a logical AND,
  • $groupby, table of groupings of the results,
  • $orderby, table defining the ordering of the results,
  • $limit, string indicating the maximum number of results to return,
  • $having table of post-constraints for the aggregation functions.

For functions used to modify content, there is another common parameter:

  • $desc, which is a table of column descriptions for the SQL table employed. If it is omitted, the description will be automatically calculated if the calling functions have need of it.

Coding principles

A large number of parameters are tolerant in respect of the type of argument which is passed to them, often accepting tables or text strings. This is the case, for example, for the sql_select() parameters. Its first parameter is $select, which corresponds to the list of SQL columns to be retrieved. Here are the 4 functional coding methods for this parameter:

// 1 element
sql_select('id_article', 'spip_articles');
sql_select(array('id_article'), 'spip_articles');
// 2 elements
sql_select('id_article, titre', 'spip_articles');
sql_select(array('id_article', 'titre'), 'spip_articles');

Out of convention, which imposes no obligations on anyone, we generally prefer to use the tabular (array) form whenever there is more than one element, a coding method which is easier to analyse by the functions which translate these abstracted coding methods into SQL queries.

Table prefixes

The sql_* functions replace the names of SQL tables in the query that start with "spip_" (such as "spip_articles") with the table prefix that is used in your own database. As such, the following call will work regardless of the table prefix actually used:

sql_select('titre', 'spip_articles', 'id_article=' . sql_quote(1));

Author Mark Baber Published : Updated : 12/03/23

Translations : English, français