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.

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. — Elin, Jan 03 '16 at 23:10
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. — developerjack, Jan 05 '16 at 00:09
@Elin 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? — ruinlach, Jan 07 '16 at 19:13
@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! — ruinlach, Jan 07 '16 at 19:16
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). — developerjack, Jan 07 '16 at 22:35
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. — Elin, Jan 08 '16 at 01:36

craigmayhew · Answer 1 · 2017-02-26T03:06:39.857

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

When giving an answer it is preferable to give [some explanation as to WHY your answer](http://stackoverflow.com/help/how-to-answer) is the one. — Stephen Rauch, Feb 26 '17 at 02:57

score 0 · Answer 2 · answered Jan 29 '16 at 14:10

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

Moving Rows Between Tables in Joomla

2 Answers2