Building a query string with a multidimentional array

Question

I am trying to build a sql string which can insert multiple row in one query itself please have a look on the following code .
I have an array called

conversion_rates which is something like this array(1) { [11]=> float(507.6) }

and the code which I am trying to execute is something like this

public function updateExRate(){/*Updates the table in the DB with exchange rates*/

    $sql =  'INSERT INTO conversor '.
           '( Sl_no  , Ex_rate,Idmoneda,Time_update) VALUES'.foreach($this->conversion_rates as $k =>$v){.' ( NULL ,'.$v.
           ','.$k.','.time().');'};

    var_dump($this->conversion_rates);
    echo 'Running... '.$sql.'<br>';
    $this->parent->parent->database->query($sql);  

}

So this is a function to update the db where i am trying to build a sql statement which is inserting multiple row and to inert the values I am using that foreach loop inside the string and that is where I am getting the error

Use mysql batch insert http://stackoverflow.com/questions/5526917/how-to-do-a-batch-insert-in-mysql — Sajeewa, Feb 27 '14 at 07:03

Vikas Umrao · Accepted Answer · 2014-02-27T07:15:44.503

1

You can try this:

$sql = 'INSERT INTO conversor "( Sl_no , Ex_rate,Idmoneda,Time_update) VALUES ';

        foreach($this->conversion_rates as $k =>$v)
        {
            $sql .= '( NULL ,'.$v.','.$k.','.time().');';

        }

        $sql .= '"}';

edited Feb 27 '14 at 07:15

answered Feb 27 '14 at 07:07

Vikas Umrao

2,800
1
15
23

small mistake in the code :) $sql = '( NULL ,'.$v.','.$k.','.time().');'; it should be $sql .= '( NULL ,'.$v.','.$k.','.time().');'; I mean '.' was missing because of that the string was being overwritten – Vikram Anand Bhushan Feb 27 '14 at 07:14

score 0 · Answer 2 · answered Feb 27 '14 at 07:14

0

you can use this :

$sql =  'INSERT INTO conversor ( `Sl_no`  , `Ex_rate,Idmoneda`,`Time_update`) VALUES ';

foreach($this->conversion_rates as $k =>$v)$sql.="( NULL ,'$v','$k',".time()."),";

$sql=rtrim($sql, ",").";";

answered Feb 27 '14 at 07:14

YamaCasis

1
1

This is uch better :) – Vikram Anand Bhushan Feb 27 '14 at 07:23

Building a query string with a multidimentional array

2 Answers2