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.

Deictic

You can't have a space in there. I'm not sure why you aren't just using insert anyway. However if you really want to you can just enter insert into as part of a query string rather than use the query constructor. The whole thing is not going to work anyway because you can't mix insert and delete in that way. – Tarshatarshish

Is there any reason you cannot use some kind of status/workflow state field within the table to denote 'completed' state? Subsequent queries/views etc. can then filter based on this rather than need to query multiple tables. – Yovonnda

@Tarshatarshish Yep; I'm definitely aware I can't use a space there; that's what I meant by "'insert into' isn't a valid method name!" Query string might be the way to go, though; thanks for the suggestion. I'm not sure I understand what you mean about not mixing insert and delete, though; the SQL query I pasted above worked adequately, I just needed to be able to trigger it from inside PHP/Joomla. Could you elaborate on that? – Deictic

@JackSkinner, that would honestly be preferable; the problem is that, for some reason, all my scripts break (I get kind of a timeout error from Joomla) whenever the table gets above ~2000 rows. I suspect hosting limitations (I'm using free hosting for this site which is limited in certain ways), but if you have any insight into specifically what might be causing it, I'd be very interested to hear! – Deictic

Can you confirm w/ the hosting what that limit is? If its 2k rows thats particularly limited - look for a new host (in my experience you're in for further problems down the track). If its a connection timeout then indexes on the table should help (though at 2k rows it shouldn't really help that much). – Yovonnda

Read the docblocks for the API, they specifically say that they can't be mixed. The way the queries are created ... the fact is that Joomla cannot read your mind and know whether the WHERE is associated with the the INSERT or the DELETE. And you have 2 WHERE statements so it's even more confusing. – Tarshatarshish

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

Firman

When giving an answer it is preferable to give some explanation as to WHY your answer is the one. – Wonderstricken

Answer 2

You could try doing it in plain old php? Something like

$conf = JFactory::getConfig(); // load your config
try{
  $link = mysqli_connect($conf->get('host'), $conf->get('user'),
    $conf->get('password'), $conf->get('db'));
  mysqli_begin_transaction($link, MYSQLI_TRANS_START_READ_WRITE);
  mysqli_query($link, "INSERT INTO my_calsgovdocs.sent_copy
    SELECT *  FROM my_calsgovdocs.entered_copy
    WHERE `Status` LIKE '%Sent%'");
  mysqli_query($link, "DELETE FROM my_calsgovdocs.entered_copy
    WHERE `Status` LIKE '%Sent%'");
  mysqli_commit($link);
}
catch (Exception $e)
{
  mysqli_rollback($link);
  JErrorPage::render($e);
}
mysqli_close($link);

Aten