SQL plugin double posting

Discussion in 'Community Support' started by primitiveType, Apr 6, 2012.

  1. primitiveType New Member

    Level: Community
    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.
  2. cheesegrits Support Gopher

    Level: Community
    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:

    PHP:
    $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
    1 person likes this.
  3. primitiveType New Member

    Level: Community
    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

Share This Page