SQL plugin double posting

[primitiveType]

First off, I apologize if i'm posting in the wrong place. I looked around and this seemed like the right place.

I have started using the SQL plugin for 1.5, and I am very satisfied with it. However, I have noticed some unusual behavior that is keeping me from accomplishing what I need to with it.

I have two different queries running in the same form. When I use just one, the problem does not occur. WHen I use them both, they appear to each be double posting... Here are the queries.

INSERT INTO tablename
( lname, about, location , website, email, comment, date, status, companyname, fname)
SELECT LastName, ' ' , ' ', ' ', ' ', ' ', ' ', 2 , CompanyName, FirstName
FROM table2
WHERE status LIKE 'ALLOW' ;


and

UPDATE table2

SET status='MIGRATED'

WHERE status LIKE 'ALLOW';


in that order. What is happening is that the rows that are meant to be copied into the other table are being copied twice before the status field is changed. These two queries are in seperate elements on the same form.

Any help is greatly appreciated.

 

[cheesegrits]

I don't think the SQL element is what you need, as it'll get run on rendering forms and tables.

What you need is the 'calc' element. Probably set with "Calc on save only" set to Yes. Your PHP code will need to look like:

$db = JFactory::getDBO();
$db->setQuery("INSERT INTO tablename ( lname, about, location , website, email, comment, date, status, companyname, fname) SELECT LastName, ' ' , ' ', ' ', ' ', ' ', ' ', 2 , CompanyName, FirstName FROM table2 WHERE status LIKE 'ALLOW' ");
$db->query();
$db->setQuery("UPDATE table2 SET status='MIGRATED' WHERE status LIKE 'ALLOW'");
$db->query();

-- hugh

 

[primitiveType]

This seems to work a lot better, thank you.


My only concern now is that the most recent submission of the form isn't getting migrated. That is, it seems to be querying before the form is actually submitted (and yes I turned on the option you mentioned). Any ideas why this might occur?


Thanks so much for your help