Php foreach data insert

Question

Hi i have an app on Joomla + php + Mysql. The array passed into function where it will be insert into mysql database. Following are my code.

public function setItems($params,$type)
{
$search_id = 1;
$query = $db->getQuery( true );
$columns = array('query_type','query_name','search_id');
foreach($params['banks'] as $key=>$value){
            $values = array($db->quote('bank'), $db->quote($value), $db->quote($search_id));
            $query->insert($db->quoteName('#__bank_parameters'))
              ->columns($db->quoteName($columns))
              ->values(implode(',',$values));
             $db->setQuery($query);
             $db->query();

        }

}

The output of params as follow:

Array
(
    [0] => bank1
    [1] => bank2
)

The issues now, i am only getting one data insertion, not 2 as how the params above has. What might be missing here? Thanks

don't know this query builder but might it be possible that you need to call $query = $db->getQuery( true ); again inside the loop before the second go round? — Orangepill
– Orangepill, Commented May 23, 2013 at 3:03

piotr_cz · Accepted Answer · 2016-04-26 09:57:08Z

2

Insert and Columns should be used once, Values multiple times:

// Create the base insert statement.
$query
   ->clear()
   ->insert(    $db->qn('#__bank_parameters'))
   ->columns(   array('query_type', 'query_name', 'search_id'))
;

foreach ($params['banks'] as $key => $value)
{
    $query->values(implode(', ', $db->q(array('bank', $value, $search_id))))
}

// Set the query and execute the insert.
$db->setQuery($query);

edited Apr 26, 2016 at 9:57

answered May 23, 2013 at 9:51

piotr_cz

9,7843 gold badges33 silver badges27 bronze badges

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

Comments

d3bug3r · Accepted Answer · 2013-05-23 03:12:20Z

0

Found an solution for above problems.

foreach($params['banks'] as $key=>$value){
            $query = $db->getQuery( true );
            $columns = array('query_type','query_name','search_id');
            $values = array($db->quote('bank'), $db->quote($value), $db->quote($search_id));
            $query->insert($db->quoteName('#__bank_parameters'))
              ->columns($db->quoteName($columns))
              ->values(implode(',',$values));
             $db->setQuery($query);
             $db->query();

        }

Thanks to Orangepill

answered May 23, 2013 at 3:12

d3bug3r

2,5963 gold badges36 silver badges75 bronze badges

Comments

Pramod Kumar Sharma · Accepted Answer · 2013-05-23 09:54:57Z

0

Create object of db $db = JFactory::getDbo();

  public function setItems($params,$type)
        {
        $search_id = 1;
        $db = JFactory::getDbo();
        $query = $db->getQuery( true );
        $columns = array('query_type','query_name','search_id');
        foreach($params['banks'] as $key=>$value){
                    $values = array($db->quote('bank'), $db->quote($value), $db->quote($search_id));
                    $query->insert($db->quoteName('#__bank_parameters'))
                      ->columns($db->quoteName($columns))
                      ->values(implode(',',$values));
                     $db->setQuery($query);
                     $db->query();

                }

    }

answered May 23, 2013 at 9:54

Pramod Kumar Sharma

8,0325 gold badges30 silver badges53 bronze badges

Collectives™ on Stack Overflow

Php foreach data insert

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related