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