QRdvark.com

  1. Table Structure
  2. The Wrong Way
  3. Getting it Right

Quite often when coding with PHP, there is a need to insert many items into a a database. The data may come from a feed, an array in yousr code, a form submitting multiple values or many other sources.

A common mistake many make, is to loop over the array directly, and INSERT a new record for each iteration of the array. Whilst this works, there is a lurking peril if the size of the array is large. The number of INSERTS that needs to be done is the same as the size of the array.

Here we look at a simple method of creating the query before INSERTing the data and using a single query to INSERT, thus saving much needed, and expensive resources.

Table Structure

Lets use a simple table structure to demonstrate with.

CREATE TABLE test_table(    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,    name VARCHAR(20) NOT NULL,    description TEXT ) ENGINE INNODB;

The Wrong Way

Here we see an example of looping over the array of data, however, an INSERT is required for every array item. This is an expensive process and needs to be remedied.

<?php
    
// an array items to insert
    
$array = array(    'dingo'    =>'A native dog',
            
'wombat'    =>'A native marsupial',
            
'platypus'    => 'A native monotreme',
            
'koala'    => 'A native Phascolarctidae'
            
);
    
    
$conn mysql_connect('db_user''db_password' );
    
mysql_select_db'test_db'$conn )

    foreach( 
$array as $key=>$value )
    {
        
$sql "INSERT INTO test_table (name, description ) VALUES ($key$value)";
        
mysql_query$sql$conn );
    }
    
?>

Whilst the above code works, and the items are inserted, a new insert is need to be run on every iteration of the foreach loop. When the array is much larger and running many queries, possibly thousands, this becomes a serious bottle neck in our code, which is avoidable.

Getting It Right

By building the query within the foreach loop, and running a single SQL query, the whole process is greatly accellerated. This code also makes use of PHP iterators to handle the array. As an iterator only knows the current array member, it is much more efficient than a standard foreach loop, which makes a copy of the whole array internally to loop over.

<?php

    
// an array items to insert
    
$array = array( 'dingo'       => 'A native dog',
            
'wombat'      => 'A native marsupial',
            
'platypus'    => 'A native monotreme',
            
'koala'       => 'A native Phascolarctidae'
            
);

    
// begin the sql statement
    
$sql "INSERT INTO test_table (name, description ) VALUES ";

    
// this is where the magic happens
    
$it = new ArrayIterator$array );

    
// a new caching iterator gives us access to hasNext()
    
$cit = new CachingIterator$it );

    
// loop over the array
    
foreach ( $cit as $value )
    {
        
// add to the query
        
$sql .= "('".$cit->key()."','" .$cit->current()."')";
        
// if there is another array member, add a comma
        
if( $cit->hasNext() )
        {
            
$sql .= ",";
        }
    }


    
// now we can use a single database connection and query
    
$conn mysql_connect('db_user''db_password' );
    
mysql_select_db'test_db'$conn );
    
mysql_query$sql$conn );

?>