sql_select

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:

  1. $select,
  2. $from,
  3. $where,
  4. $groupby,
  5. $orderby,
  6. $limit,
  7. $having,
  8. $serveur,
  9. $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'];
}

Footnotes

[1Maybe one of these days there will be a genuinely dedicated column for this!

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

Translations : English, français