Insert query not working within a loop

Question

I am trying to insert records in a loop. But unable to do so. Following is my code:

historical.raw.php : created a task in controller of format raw

public function historical()
{
    $db = JFactory::getDBO();
    $arr =  array('ARI','ATL' );

    foreach ($arr as $key => $value) 
    {
        $query="INSERT INTO `tbname` (<columns>) VALUES (<values>)";
        $db->setQuery($query);
        $db->query(); 
        // or $db->execute();
    }
}

through URL I am accessing the task but it is giving me 500 error. The loop is only executing once. In database, rows are inserted only for the 1st array value "ARI" and not for the second one.

When I comment the $db->query() line then my file is running properly without any error. Kindly, guide me the correct way

Thank you

Answer 1

I have resolved the issue. Following is my complete code for the query execution:

<?php 
$arr =  array('ARI','ATL' );
$columns = array('col1','col2','col3');
$db = JFactory::getDbo();
foreach ($arr as $key => $value) 
{
$values = array($val1,$val2,$val3);
$query = $db->getQuery(true);
$query->insert($db->quoteName('#__tbname'))
      ->columns($db->quoteName($columns))
      ->values(implode(',', $db->quote($values)));
$db->setQuery($query);
$db->query();
}

The implode function needs to be added as the $values is an array and $db->quote($values) is necessary because it will provide quotes to the values.

Thank you everyone for your answers and guidance

Answer 2

This can be done by using ->insert() and ->values(), with the $values being an array of the strings with the columns to insert.

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$columns = array('col_one','col_two');
$values = array();
// Proper escaping/quotes should be done here, and probably in a loop

foreach ($arr as $key => $value) {
    $values[] = '$key, $value';
}

$query->insert($db->quoteName('#__tablename'));
$query->columns($columns);
$query->values($values);
$db->setQuery($query);
$db->query();

I hope it will help you :)

Answer 3

An Table with 100 cols, should not be handled by hand.In most cases is something forgotten. On Changes you have heavy task to find in code the Positions

Basically us the JForm with the correct Data-Definition (validation) and an JTable object that is connected with the table .. it prevents from a lot of errors ;-)

<?php   
/** Example peace to get the Correct Columns**/
// Make sure, you have correct reflected and represented table columns
$existingColumns = array();
$table           = JTable::getInstance('TableFileName', 'Prefix');
$table->reset()
if ($table instanceof JTable) {
    $existingColumns = array_keys($table->getFields());
}
// we only want use this columns
$wantedColumns = array('col1', 'col2', 'col3');
/**
 * Working with checked and save existing columns
 **/
$arr = array('ARI', 'ATL');
// try this
$columns = $existingColumns;//array('col1', 'col2', 'col3');
// or this
$selectiveColumns = array();
foreach ($wantedColumns as $idx => $columnName) {
    if (in_array($columnName, $existingColumns, true)) {
        $selectiveColumns[] = $columnName;
    }
}

$db = JFactory::getDbo();
foreach ($arr as $key => $value) {

    $values = array($val1, $val2, $val3);

    $query = $db->getQuery(true);

    $query->insert($db->quoteName('#__tbname'))
          ->columns($db->quoteName($columns))
        // or selective checked Columns
        //  ->columns($db->quoteName($selectiveColumns))
          ->values(implode(',', $db->quote($values)));

    $db->setQuery($query);
    try {
        $db->execute();
    } catch (Exception $e) {
        // for testing purposes let us know what happends
        var_dump($e->getMessage());
    }
}

// Alternate you can use save the JTable as it self
// generic data
$dataStack =
    array(
    array('col1' => 'data'),
    array('col1' => 'dataX'),
    array('col1' => 'dataY'));

$table = JTable::getInstance('TableFileName', 'Prefix');

foreach ($dataStack as $data) {
    $table->reset();
    $table->bind($data);
    $table->store();

}