Get an array for parameterized query

Question

I need to get an array in this form:

array(':name'=>'PRIMO,':time_of_execution'=>12,':price'=>50,':active'=>1))

This is my start $cols array:

Array
(
    [key] => Array
        (
            [0] => name
            [1] => time_of_execution
            [2] => price
            [3] => active
        )

    [value] => Array
        (
            [0] => Array
                (
                    [name] => PRIMO
                    [time_of_execution] => 10
                    [price] => 100
                    [active] => 1
                )

        )

)

In $cols['key'] I have the column of database and with this I can realize the base $sql:

$sql = 'INSERT INTO '.$this->table.'('.implode(",", $cols['key']).') VALUES (';
            $i=1;
            foreach ($cols['key'] as $key=>$value)
            {
                $sql.=':'.$value;
                if ($i<count($cols['key']))
                {
                    $sql.=', ';
                }
                $i++;
            }
            $sql.= ')';

Now last piece of code that I'm missing is transform the $cols['value'] in an array in this form:

array(':name'=>'PRIMO,':time_of_execution'=>12,':price'=>50,':active'=>1))

My code, but wrong is:

$max = count($cols['value']);
            $i=0;
            $k=0;
            foreach ($cols['key'] as $key=>$value)
            {
                for($i=0;$i<$max;$i++)
                {
                    $args[$k] = array(':'.$value=>$cols['value'][$i][$value]);
                    $k++;
                }
            }

@Bono I do not see any mysql_*() function in OPs code and he is using parametrized queries! — Sirko
– Sirko, Commented Jan 23, 2015 at 16:56

David Triebe · Accepted Answer · 2015-01-23 17:10:21Z

2

Why not simply like this?

$args = array();
foreach($cols['value'][0] as $key => $value) {
    $args[':'.$key] = $value;
}

If you're actually going for nested inserts and need to extract multiple records from the $cols array I'd go for something like this:

$args = array();
foreach($cols['value'] as $i => $col) {
    $args[$i] = array();
    foreach($col as $key => $value) {
        $args[$i][':'.$key] = $value;
    }
}

edited Jan 23, 2015 at 17:10

answered Jan 23, 2015 at 16:59

David Triebe

3751 silver badge5 bronze badges

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

Comments

Rasclatt · Accepted Answer · 2015-01-23 18:12:51Z

This code will set you up with bound values and value statements (procedural):

$set =  array(
    'key' => array(
            '0' => name,
            '1' => time_of_execution,
            '2' => price,
            '3' => active,
        ),

    'value' => array(
            0 => array(
                    'name' => 'PRIMO',
                    'time_of_execution' => 10,
                    'price' => 100,
                    'active' => 1,
                ),

            1 => array(
                    'name' => 'POOP',
                    'time_of_execution' => 20,
                    'price' => 131,
                    'active' => 2
                )

        )

);
        $b  =   1;
        foreach($set['value'] as $row => $values) {
                // loop each instance
                foreach($values as $paramkeys => $params) {
                        // Set bind array/parameters
                        $bind[":".$b]   =   $params;
                        // Save to sql for implosion
                        $sql[$row][]    =   ":".$b;
                        $b++;
                    }
            }

        // Implode each set of values
        foreach($sql as $sets) {
                $sqlcomp[]  =   "(".implode(",",$sets).")";
            }

    // Implode columns to set columns
    $columns    =   "(`".implode("`,`",$set['key'])."`)";

    echo '<pre>';
    print_r($bind);
    print_r($sqlcomp);
    print_r($columns);

Will give you:

// Bind array w/parameters
Array
(
    [:1] => PRIMO
    [:2] => 10
    [:3] => 100
    [:4] => 1
    [:5] => POOP
    [:6] => 20
    [:7] => 131
    [:8] => 2
)
// Values
Array
(
    [0] => (:1,:2,:3,:4)
    [1] => (:5,:6,:7,:8)
)
// Columns
(`name`,`time_of_execution`,`price`,`active`)

SQL:

echo 'insert into `table` '.$columns." VALUES ".implode(",",$sqlcomp);

Gives you:

insert into `table` (`name`,`time_of_execution`,`price`,`active`) VALUES (:1,:2,:3,:4),(:5,:6,:7,:8)

Since you are using $this->table, presumably you are using this in a class so here is a class version:

class   QueryEngine
    {
        public      $columns;
        public      $rows;
        public      $parameters;
        protected   $table;

        public  function SetStatement($array = array())
            {
                $b  =   1;
                foreach($array['value'] as $row => $values) {
                        // loop each instance
                        foreach($values as $paramkeys => $params) {
                                // Set bind array/parameters
                                $bind[":".$b]   =   $params;
                                // Save to sql for implosion
                                $sql[$row][]    =   ":".$b;
                                $b++;
                            }
                    }

                // Implode each set of values
                foreach($sql as $sets) {
                        $sqlcomp[]  =   "(".implode(",",$sets).")";
                    }

                // Bound Parameters
                $this->parameters   =   $bind;
                // Row array
                $this->rows         =   $sqlcomp;
                // Implode columns to set columns
                $this->columns      =   "(`".implode("`,`",$array['key'])."`)";

                $this->AssembleSQL();

                return $this;
            }

        public  function AssembleSQL()
            {
                $this->statement[]  =   '`'.$this->table.'` '.$this->columns.' VALUES '.implode(",",$this->rows);
                return $this;
            }

        public  function insert($table = false)
            {
                $this->table        =   $table;
                $this->statement[]  =   "insert into ";
                return $this;
            }
    }

// New instance
$QueryEngine    =   new QueryEngine();
// Returned array of compiled elements, so implode
$statement      =   implode("",$QueryEngine->insert('mytable')->SetStatement($set)->statement);
// Gives you:
// insert into `mytable` (`name`,`time_of_execution`,`price`,`active`) VALUES (:1,:2,:3,:4),(:5,:6,:7,:8)
// Assign parameters
$parameters     =   $QueryEngine->parameters;
// To use in PDO (I am familiar with PDO so I will give that example)
// Insert formatted statement
$query          =   $con->prepare($statement);
// Execute with bound parameters
$query->execute($parameters);

Collectives™ on Stack Overflow

Get an array for parameterized query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related