11

Instead of using a loop, can Joomla database functions create a SQL statement like this?

INSERT INTO #__tablename (col1,col2)
VALUES 
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three'),
...
 ('999', 'three'),

The docs at Accessing the database using JDatabase, refer to Transactions and using SQL or Objects, but no mention of multiple values in either case.

12

This can be accomplished 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, but cluttered the answer, so omitted it
$values[] = '1, "one"';
$values[] = '2, "two"';
$values[] = '3, "three"';
$values[] = '999, "nineninetynine"';

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

SQL that is produced using echo $query->dump()

INSERT INTO `xyz_tablename`
(col_one,col_two) VALUES 
(1, "one"),(2, "two"),(3, "three),(999, "nineninetynine")
3

Joomla core support basic SQL queries. You can create a class to store several inserts and create a single final query to execute a single insert at the end.

    public function flushInserts()
{
    $db = JFactory::getDbo();

    foreach ($this->bulk_tables as $table)
    {
        // No inserts
        if (!array_key_exists($table, $this->bulk_inserts))
        {
            continue;
        }

        $tableq = $db->qn($table);

        $insertSet = $this->bulk_inserts[$table];
        $values = implode(',', $insertSet);
        $query = 'INSERT INTO ' . $tableq . ' VALUES ' . $values . ';';

        $db->setQuery($query);
        $db->execute();

        if ($error = $db->getErrorMsg())
        {
            throw new Exception($error);
        }
    }

    $this->bulk_inserts = array();
}
0

Adding to @GDP's answer

...
$columns = array('user_id', 'type', 'object', 'lvl', 'date');
$values  = array();
foreach ($batch as $row) {
    $array    = array(
        $row->user_id,
        $db->quote($row->type),
        $db->quote($row->object),
        $db->quote($row->lvl),
        $db->quote($row->date),
    );
    $values[] = implode(',', $array);
}
$query->insert($db->quoteName('#__activity_log'));
$query->columns($db->quoteName($columns));
$query->values($values);
...
  • Can you explain the value in your answer that isn't already covered by @GDP's accepted answer? This is very nearly a code-only answer. – mickmackusa Jul 24 '18 at 15:16
  • It shows how to populate the $values variable with an array of comma separated row strings programatically, which saves a lot of time and has lower margin for errors, rather than manually creating the comma separated strings as in his answer or even concatenating for that matter. – Mohd Abdul Mujib Jul 24 '18 at 15:19
  • This information should be packaged into your answer so that it can educate future researchers. – mickmackusa Jul 24 '18 at 15:21
  • Well the way I see it is, provided code is pretty self explanatory, though surely there may be areas of improvement, if you think so please feel free to edit and enhance the answer – Mohd Abdul Mujib Jul 24 '18 at 15:23
  • Your code is self-explanatory for those who understand what the php/Joomla syntax means. You don't like to refine your own work? On JSX (more than Stackoverflow) fully explaining answers is mega-important because the people who are just starting a career in web development are enticed to do so by CMS. You do realize that the point of posting content here is to educate, right? I mean, I am encouraging you to post your best answer and risk earning upvotes. – mickmackusa Jul 24 '18 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.