1. The forums have been upgraded to XenForo 1.4. Please post any issues or comments here.
  2. Our hosting provider will be performing some scheduled maintenance and upgrading during the early morning (US time) of Tue 28th July, starting at approx 2am EST, which will require some down time of the server.

SQL plugin double posting

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

  1. primitiveType

    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

    cheesegrits Support Gopher Staff Member

    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

    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