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:

  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:

$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