3

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

2
  • Sitenote: SQL queries should not go in the controller but in the model. Oct 28, 2015 at 17:38
  • I did this as well - added sql query in model . But didn't work for me. Still giving 500 error.
    – user5491
    Oct 29, 2015 at 4:53

3 Answers 3

5

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

0
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 :)

1
  • I tried this before but this wasn't working for me. I have 135 columns in my database. Therefore, went for the long way
    – user5491
    Oct 28, 2015 at 12:43
1

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();

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.