Coding with SPIP 3.0
SPIP3.0 Technical Documentation

> > > > sql_get_select

sql_get_select

The sql_get_select() function returns the query for the requested selection. This is an alias for the sql_select() function but which passes the $option argument set to false, so that the SQL query is returned rather than being executed.

It accepts the same arguments as sql_select() except for the last, which is provided by the function:

  1. $select,
  2. $from,
  3. $where,
  4. $groupby,
  5. $orderby,
  6. $limit,
  7. $having,
  8. $serveur

It is applied as shown in this example:

  1. $request = sql_get_select('column', 'table');
  2. // returns "SELECT column FROM table" (for a MySQL database)

Download

This function therefore returns a SQL query which is valid for the database manager in use. As this query is clean, it can be directly used by the sql_query() function, but more often than not, it is used to create more complex queries in conjunction with sql_in() :

  1. // list of identifiers
  2. $ids = sql_get_select('id_table', 'tableA');
  3. // selection based on that prior selection
  4. $results = sql_select('titre', 'tableB', sql_in('id_table', $ids)));

Download

Example

To find out the titles of the sections which have article identifiers greater than 200, one of the possible methods (we could also use a join) is to use sql_get_select():

  1. // create the selection query to find the list of sections
  2. $ids = sql_get_select('DISTINCT(id_rubrique)', 'spip_articles', array('id_article > 200'));
  3. // select the titles of those sections
  4. $res = sql_select('titre', 'spip_rubriques', sql_in('id_rubrique', $ids));
  5. while ($r = sql_fetch($res)) {
  6. // display each title.
  7. echo $r['titre'] . '<br />';
  8. }

Download

Considerably more complicated, we could search for examples in certain criteria functions, for example with the {noeud} criteria of the "SPIP Bonux" plugin which creates a sub-query to retrieve the list of objects which have child records.

  1. function critere_noeud_dist($idb, &$boucles, $crit) {
  2. // [...]
  3. // this construction with IN will make the compiler request
  4. // the use of the sql_in() functions
  5. $where = array("'IN'", "'$boucle->id_table." . "$primary'", "'('.sql_get_select('$id_parent', '$table_sql').')'");
  6. if ($crit->not)
  7. $where = array("'NOT'", $where);
  8.  
  9. $boucle->where[]= $where;
  10. }

Download

  • Author :
  • Published :
  • Updated : 02/06/10
  • Translations : English, français