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:

  1. // selection
  2. if ($resultats = sql_select('column', 'table')) {
  3. // loop on the results
  4. while ($res = sql_fetch($resultats)) {
  5. // use the results
  6. // $res['column']
  7. }
  8. }

Download

The $select and $from parameters accept the declaration of aliases. This offers the following type of construction:

  1. if ($r = sql_select(
  2. 'a.column AS colA',
  3. 'b.column AS colB',
  4. 'SUM(b.number) AS btotal'
  5. ),
  6. 'tableA AS a',
  7. 'tableB AS b'
  8. ))) {
  9. while ($ligne = sql_fetch($r)) {
  10. // we now have access to:
  11. // $ligne['colA'] $ligne['colB'] $ligne['btotal']
  12. }
  13. }

Download

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 ’*’) :

  1. $result = sql_select('*', "spip_rubriques", "id_parent=0", '', '0+titre,titre');
  2. while ($row = sql_fetch($result)){
  3. $id_rubrique = $row['id_rubrique'];
  4. // ...
  5. }

Download

Select cats but not dogs (in the title) for articles in sector 3:

  1. $champs = array('titre', 'id_article', 'id_rubrique');
  2. $where = array(
  3. 'id_secteur = 3',
  4. 'titre LIKE "%chat%" ',
  5. 'titre NOT LIKE "%chien%"'
  6. );
  7. $result = sql_select($champs, "spip_articles", $where);

Download

Select the titles and extensions recognised for documents, and store the result in a table:

  1. $types = array();
  2. $res = sql_select(array("extension", "titre"), "spip_types_documents");
  3. while ($row = sql_fetch($res)) {
  4. $types[$row['extension']] = $row;
  5. }

Download

This selection could also be written as:

  1. $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:

  1. $result = sql_select(
  2. "docs.id_document AS id_doc",
  3. "docs.extension AS extension",
  4. "docs.fichier AS fichier",
  5. "docs.date AS date",
  6. "docs.titre AS titre",
  7. "docs.descriptif AS descriptif",
  8. "R.id_rubrique AS id_rub",
  9. "R.titre AS titre_rub"),
  10. "spip_documents AS docs",
  11. "spip_documents_liens AS lien",
  12. "spip_rubriques AS R"),
  13. "docs.id_document = lien.id_document",
  14. "R.id_rubrique = lien.id_objet",
  15. "lien.objet='rubrique'",
  16. "docs.mode = 'document'"),
  17. "",
  18. "docs.date DESC");
  19. while ($row=sql_fetch($result)) {
  20. $titre=$row['titre'];
  21. // ...
  22. // and with the previous table:
  23. $titre_extension = $types[$row['extension']]['titre'];
  24. }

Download

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