4

This Method for creating a subquery using JDatabase provides some great insight and I think it would be beneficial to have a agreed method of subquery documented. I came across this because I am wanting to do the same thing but as an insert statement. The closest I ever got was replicated the entire table because for some reason I keep losing the columns.

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

// Insert columns.
$columns = array('user_id', 'title', 'ordering', 'published', 'description');

$now = $query->currentTimestamp();
// Insert values.

$pre->insert($db->qn('#__table'))->columns($db->qn($columns));


// Prepare the insert query.
$query->select(array(
                    $db->q('') . ' AS id',
                    $db->qn('user_id'),
                    $db->qn('c.name', 'title'),
                    $db->q('1') . ' AS published',
                    $db->q('') . ' AS ordering',
                    $db->q('') . ' AS created_by',
                    $now .  ' AS created_on',
                    $db->q('') . ' AS modified_by',
                    $db->q('') . ' AS modified_on',
                    )
            )
            ->from($db->qn('#__other_table', 'c'));

    $query = $pre . $query ;

    // Set the query using our newly populated query object and execute it.
    $db->setQuery($query);

    $db->execute();
  • Since writing I am using a $objects = $db->loadObjectList(); foreach ($objects AS $obj){ $db->insertObject('#__table', $obj); } – poproar Jul 2 '14 at 16:38
2

Although this is an old question, it might be helpful to others. Here is the insert by select query using JDatabase.

Build some select query using JDatabase

$querySelect = $db->getQuery(true)
  ->select('id, title')
  ->from('sometable');

Now your insert query

$queryInsert = $db->getQuery(true)
  ->insert('anothertable')
  ->columns($db->qn(array('id','title')))
  ->values($querySelect)

Hope this helps.

4

You have to type which values you want to insert

$pre->insert($db->qn('#__table'))->columns($db->qn($columns))->values(here values);
  • That works for insert values but my question is specifically for insert select. – poproar Jul 2 '14 at 16:35
  • This is the right answer to the wrong question. – mickmackusa Jun 7 '18 at 2:51

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.