Build a single multiple insert query from an array in PHP

Question

I have an array that looks like this

$users = array(
                    array('name'=>'aaa','age'=>2),
                    array('name'=>'bbb','age'=>9),
                    array('name'=>'ccc','age'=>7)
               );

I would like to create a function that will accept an array like above, creates a clause for a single query-multiple insert, prepares an array of variable that I can bind with PDO.

example output:

$clause = INSERT INTO tablename (`name`,`age`) 
          VALUES (:name_0,:age_0),(:name_1,:age_1),(:name_2,:age_2);

Then another set of array corresponding to the values above:

$params => Array
        (
            [name_0] => aaa
            [age_0] => 2
            [name_1] => bbb
            [age_1] => 9
            [name_2] => ccc
            [age_2] => 7
        );

So that the can execute it like so:

$prepared = $connection->prepare($clause);
$prepared->execute($params);

Is it possible to achieve this in a single function?

Elymentree · Accepted Answer · 2016-03-31 13:45:20Z

2

Yes that very possible, I did exactly the same thing for my custom query builder class:

function INSERT_MULTIPLE_QUERY($ARRS = array()){

           $raw_cols = '(`';

           // PREPARE THE COLUMNS 
           foreach($ARRS[0] as $key1 => $value):
               $raw_cols .= $key1.'`,`'; 
           endforeach;
           $final_cols = rtrim($raw_cols,'`,`') . '`)';
           $ctr1=0;  $raw_vals='';

           // PREPARE THE VALUES
           foreach($ARRS as $ARR_VALUE):
               $raw_vals .= '(';
               foreach($ARR_VALUE as $key => $value): $raw_vals .= ':'.$key.'_'.$ctr1.','; endforeach;
               $raw_vals  = rtrim($raw_vals,',');
               $raw_vals .= '),';
               $ctr1++;
           endforeach;
           $final_vals = rtrim($raw_vals,',');
           $ctr2 = 0; $param = array();

           // PREPARE THE PARAMETERS
           foreach($ARRS as $ARR_PARAM):
               foreach($ARR_PARAM as $key_param => $value_param):$param[$key_param.'_'.$ctr2] = $value_param; endforeach;
               $ctr2++;
           endforeach;

           // PREPARE THE CLAUSE 
           $clause = 'INSERT INTO tablename '  . $final_cols . ' VALUES ' . $final_vals;

           // RETURN THE CLAUSE AND THE PARAMETERS 
           $return['clause'] = $clause;
           $return['param']  = $param;

           return $return; 
        }

Now to use this function:

$query = INSERT_MULTIPLE_QUERY($users); 
     //  $users is your example array above

Then:

$prepared = $connection->prepare($query['clause']);
$prepared->execute($query['param']);

answered Mar 31, 2016 at 13:45

Elymentree

85311 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mm-Art-In Over a year ago

ditto for my own query builder class.

Elymentree Over a year ago

Awesome! can I get an up vote and correct answer then?

Mihai Matei · Accepted Answer · 2016-03-31 14:49:52Z

You can do it in a OOP style by creating a QueryBuilder and PDOStatementDecorator like below:

class QueryBuilder
{
    const BUILD_TYPE_INSERT_MULTIPLE = 'INSERT_MULTIPLE';

    protected $table;
    protected $values;
    protected $buildType;

    public function __construct($table)
    {
        $this->table = $table;
    }

    public static function onTable($table)
    {
        return new self($table);
    }

    public function insertMultiple(Array $values = array())
    {
        $this->values = $values;

        $this->buildType = self::BUILD_TYPE_INSERT_MULTIPLE;

        return $this;
    }

    public function build()
    {
        switch ($this->buildType) {
            case self::BUILD_TYPE_INSERT_MULTIPLE:
                return $this->buildInsertMultiple();
        }
    }

    protected function buildInsertMultiple()
    {
        $fields = array_keys($this->values[0]);

        $query = "INSERT INTO {$this->table} (" . implode(',', $fields) . ") VALUES ";

        $values = array();

        for ($i = 0; $i < count($fields); $i++) {
            $values[] = '(' . implode(', ', array_map(function($field) use ($i) {
                return ':' . $field . $i;
            }, $fields)) . ')';
        }

        $query .= implode(', ', $values);

        return $query;
    }
}

class PDOStatementDecorator
{
    protected $pdoStatement;

    public function __construct(PDOStatement $pdoStatement)
    {
        $this->pdoStatement = $pdoStatement;
    }

    public function executeMultiple(Array $bindsGroup = array())
    {
        $binds = array();

        for ($i = 0; $i < count($bindsGroup); $i++) {
            foreach ($bindsGroup[$i] as $key => $value) {
                $binds[$key . $i] = $value;
            }
        }

        return $this->execute($binds);
    }

    public function execute(Array $inputParemeters)
    {
        return $this->pdoStatement->execute($inputParemeters);
    }

    public function fetch($fetchStyle = null, $cursorOrientation = 'PDO::FETCH_ORI_NEXT', $cursorOffset = 0)
    {
        return $this->pdoStatement->fetch($fetchStyle, $cursorOrientation, $cursorOffset);
    }

    /**
     * TODO
     * Implement all public PDOStatement methods 
     */
}

The query builder can be enhanced to be able to build queries for update/delete statements.

Now the usage would be very simple:

$users = array(
    array('name' => 'aaa', 'age' => 2),
    array('name' => 'bbb', 'age' => 9),
    array('name' => 'ccc', 'age' => 7),
);

$query = QueryBuilder::onTable('users')->insertMultiple($users)->build();

$stmt = new PDOStatementDecorator($pdo->prepare($query));

$stmt->executeMultiple($users);

fusion3k · Accepted Answer · 2016-04-01 14:10:54Z

This function require Table Name, your original array, and an optional parameter that is used as default value, only if one field is not present in all array rows:

function buildQuery( $table, $array, $default='NULL' )
{
    /* Retrieve complete field names list: */
    $fields = array();
    foreach( $array as $row ) $fields = array_merge( $fields, array_keys( $row ) );
    $fields = array_unique( $fields );

    /* Analize each array row, then update parameters and values chunks: */
    $values = $params = array();
    foreach( $array as $key => $row )
    {
        $line = array();
        foreach( $fields as $field )
        {
            if( !isset( $row[$field] ) )
            { $line[] = $default; }
            else
            {
                $line[] = ":{$field}_{$key}";
                $params["{$field}_{$key}"] = $row[$field];
            }
        }
        $values[] = '('.implode(',',$line).')';
    }

    /* Compone MySQL query: */
    $clause = sprintf
    (
        "INSERT INTO `%s` (`%s`) VALUES %s;",
        $table,
        implode( '`,`', $fields ),
        implode( ',', $values )
    );

    /* Return array[ clause, params ]: */
    return compact( 'clause', 'params' );
}

Calling it in this way:

$query = buildQuery( 'mytable', $users );

$query will contain this:

Array
(
    [clause] => INSERT INTO `mytable` (`name`,`age`) VALUES (:name_0,:age_0),(:name_1,:age_1),(:name_2,:age_2);
    [params] => Array
        (
            [name_0] => aaa
            [age_0] => 2
            [name_1] => bbb
            [age_1] => 9
            [name_2] => ccc
            [age_2] => 7
        )

)

eval.in demo

Collectives™ on Stack Overflow

Build a single multiple insert query from an array in PHP

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related