The sql_select()
function selects content form the database and returns an SQL resource when successful or false
in the event of an error.
It accepts up to 9 parameters, the first 2 being mandatory, and sequenced in the same descriptive order as a standard SQL query. Each parameter will (preferably) accept an array as input data, but will also accept character strings with elements separated by commas:
-
$select
, -
$from
, -
$where
, -
$groupby
, -
$orderby
, -
$limit
, -
$having
, -
$serveur
, -
$option
.
The sql_select()
function is often coupled with an sql_fetch()
, such as shown here below:
// selection
if ($resultats = sql_select('column', 'table')) {
// loop on the results
while ($res = sql_fetch($resultats)) {
// use the results
// $res['column']
}
}
The $select
and $from
parameters accept the declaration of aliases. This offers the following type of construction:
if ($r = sql_select(
array(
'a.column AS colA',
'b.column AS colB',
'SUM(b.number) AS btotal'
),
array(
'tableA AS a',
'tableB AS b'
))) {
while ($ligne = sql_fetch($r)) {
// we now have access to:
// $ligne['colA'] $ligne['colB'] $ligne['btotal']
}
}
Example
Select the root sections (id_parent=0) in the "spip_rubriques" table sorted by rank [1], then in alphanumeric order, and request all of the columns (total selection with ’*’) :
$result = sql_select('*', "spip_rubriques", "id_parent=0", '', '0+titre,titre');
while ($row = sql_fetch($result)){
$id_rubrique = $row['id_rubrique'];
// ...
}
Select cats but not dogs (in the title) for articles in sector 3:
$champs = array('titre', 'id_article', 'id_rubrique');
$where = array(
'id_secteur = 3',
'titre LIKE "%chat%" ',
'titre NOT LIKE "%chien%"'
);
$result = sql_select($champs, "spip_articles", $where);
Select the titles and extensions recognised for documents, and store the result in a table:
$types = array();
$res = sql_select(array("extension", "titre"), "spip_types_documents");
while ($row = sql_fetch($res)) {
$types[$row['extension']] = $row;
}
This selection could also be written as:
$res = sql_select("extension, titre", "spip_types_documents");
Select the documents linked to a section, with the title of the section in question, and sort in reverse date order:
$result = sql_select(
array(
"docs.id_document AS id_doc",
"docs.extension AS extension",
"docs.fichier AS fichier",
"docs.date AS date",
"docs.titre AS titre",
"docs.descriptif AS descriptif",
"R.id_rubrique AS id_rub",
"R.titre AS titre_rub"),
array(
"spip_documents AS docs",
"spip_documents_liens AS lien",
"spip_rubriques AS R"),
array(
"docs.id_document = lien.id_document",
"R.id_rubrique = lien.id_objet",
"lien.objet='rubrique'",
"docs.mode = 'document'"),
"",
"docs.date DESC");
while ($row=sql_fetch($result)) {
$titre=$row['titre'];
// ...
// and with the previous table:
$titre_extension = $types[$row['extension']]['titre'];
}