sql_insertq_multi

The sql_insertq_multi() function is used to insert, in one single action, several elements with identical schemas into a database table. If the database manager port allows it, it will then use a single SQL command to implement the insert. More specifically, a single SQL command for each batch of 100 elements is used in order to avoid memory congestion.

The function has the same 5 parameters as sql_insertq() , but the second parameter for this function is a table of a table of pairs, and not just directly a table of pairs:

  1. $table is the name of the SQL table,
  2. $couples is a table of associative tables of name / value pairs,
  3. $desc,
  4. $serveur,
  5. $option.

The columns used in this command absolutely must be the same set for all of the inserts. The command is used as shown below:

$id = sql_insertq_multi('table', array(
	array('column' => 'value'),
	array('column' => 'value2'),
	array('column' => 'value3'),
);

Example

Searches made using SPIP use the spip_resultats table to store some elements used as a cache, by taking care to use the table for the SQL connection. $tab_couples contains all of the data to be inserted:

// insert the results into the results cache table
if (count($points)){
	$tab_couples = array();
	foreach ($points as $id => $p){
		$tab_couples[] = array(
			'recherche' => $hash,
			'id' => $id,
			'points' => $p['score']
		);
	}
	sql_insertq_multi('spip_resultats', $tab_couples, array(), $serveur);
}

The "Polyhierarchie" plugin also uses this function for inserting the list of sections just recently linked to a given object:

$ins = array();
foreach($id_parents as $p){
	if ($p) {
		$ins[] = array(
			'id_parent' => $p, 
			'id_objet' => $id_objet, 
			'objet' => $objet);
	}
	if (count($ins)) {
		sql_insertq_multi("spip_rubriques_liens", $ins, "", $serveur);
	}

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

Translations : English, français