Moving Rows Between Tables in Joomla

Question

I have been trying to create a PHP script that will periodically move "completed" rows from a table on my Joomla site to a different table. The query I wrote works just fine in PHPMyAdmin:

INSERT INTO my_calsgovdocs.sent_copy
SELECT *  FROM my_calsgovdocs.entered_copy
WHERE `Status` LIKE '%Sent%';
DELETE FROM my_calsgovdocs.entered_copy
WHERE `Status` LIKE '%Sent%';

I attempted to translate it into some PHP code which could run inside Joomla, and I've pasted that code below. It returns an "Unexpected T_STRING" error which points to the line below which starts ->insert into, and it has now occurred to me that the script wouldn't work because "insert into" isn't a valid method name! So far I can't find an equivalent method to be used inside Joomla. This was my attempt at the code:

try
{
    $db->transactionStart();

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

    $query
        ->insert into($db->quoteName('sent_copy'))
        ->select('*')
        ->from($db->quoteName('entered_copy'))
        ->where($db->quoteName('Status') . ' LIKE ' . $db->quote('%Sent%') . ';')
        ->delete from($db->quoteName('entered_copy'))
        ->where($db->quoteName('Status') . ' LIKE ' . $db->quote('%Sent%'));

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

    $db->transactionCommit();
}
catch (Exception $e)
{
    $db->transactionRollback();
    JErrorPage::render($e);
}

Anyone have an idea how I can accomplish this inside Joomla? I'd prefer (as you may have noticed above) to do it in one transaction so that, if there's an error, I won't have a mess on my hands.

Answer 1

$db->setQuery allows being passed a query string as an argument instead of an object. See "preparing the query": https://docs.joomla.org/J1.5:Accessing_the_database_using_JDatabase

I've also suggested running two of these queries as part of the same transaction.

I unfortunately don't have a joomla installation handy to test this, please comment if you find it doesn't work.

try
{
    $db->transactionStart();

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

    $query1 = "INSERT INTO my_calsgovdocs.sent_copy
    SELECT *  FROM my_calsgovdocs.entered_copy
    WHERE `Status` LIKE '%Sent%'";

    $db->setQuery($query1);
    $result1 = $db->execute();

    $query2 = "DELETE FROM my_calsgovdocs.entered_copy
    WHERE `Status` LIKE '%Sent%'";

    $db->setQuery($query2);
    $result2 = $db->execute();

    $db->transactionCommit();
}
catch (Exception $e)
{
    $db->transactionRollback();
    JErrorPage::render($e);
}