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:

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

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() :

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

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():

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

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.

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

$boucle->where[]= $where;
}

Author Mark Baber Published : Updated : 02/06/10

Translations : English, français