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:
-#
$select,
-#
$from,
-#
$where,
-#
$groupby,
-#
$orderby,
-#
$limit,
-#
$having,
-#
$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;
}