dongtu7205 2014-01-30 11:29
浏览 48
已采纳

Joomla:使用JDatabaseQuery来表示插入..选择查询?

So I need to run a long(ish) query to insert a new row into a table, based on values of another row elsewhere in the database. This is running in Joomla 3.1.5

Typically, you can use MySql's INSERT .. SELECT syntax to easily do this, but I'm looking for a way to keep close to Joomla's query builder, example:

<?php

// ...

// Base Tables & Columns.

$my_table = '#__my_table';

$columns = array('column_one', 'column_two');

// Set up the database and query.

$db = JFactory::getDBO();

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

// Escape / quote the table name.
$my_table = $db->quoteName($my_table);

// Escape all columns.
$cols = array_map(array($db, 'quoteName'), $cols);

$query
    ->insert($my_table)
    ->columns($columns)
    // E.g. ->select( ... )->from( ... ) ...

$db->setQuery($query);

$result = $db->query();

// ...

?>

Of course, the example comment won't work, but I was wondering if there was a way which would allow me to perform something similar (without needing to run a separate query elsewhere).

Naturally, if there's no way to perform this type of query, I can just drop to using a raw query string.

  • 写回答

1条回答 默认 最新

  • doumeikuan6834 2014-01-30 14:32
    关注

    The docblocks of many JDatabaseQuery methods include the following statement

     * Note that you must not mix insert, update, delete and select method calls when building a query.
    

    This is because ... to over simplify but as an example how would we know which list of columns (or which where clause etc) to put where when we are building the query.

    But there are ways that you can get around this limitation by building your query in a slightly more complex way (which is fair since it's a more complex query). So for example

    $db = JFactory::getDBO();
    
    $queryselect = $db->getQuery(true);
    $queryselect->select($db->quoteName(array('id','title','alias')))
        ->from($db->quoteName('#__content'));
    $selectString = $queryselect->__toString();
    
    
    $queryInsert = $db->getQuery(true);
    $queryInsert->columns($db->quoteName(array('id','title','alias')))
    ->insert($db->quotename('#__newtable'));
    
    $db->setQuery($queryInsert . $selectString);
    $db->execute();
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?