Announcement

Collapse
No announcement yet.

Need help with onAfterinsert eventt. Insert records.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help with onAfterinsert eventt. Insert records.

    Hello all, I need to perform the following MySQL code (which works in MySQL) in form event onAfterInsert. It must insert multiple records into multiple tables. I cannot figure out the syntax for the event. Here is the MySQL code.

    BEGIN;
    INSERT INTO module1_1_v2 (item_title, comp_id, module)
    VALUES
    ((SELECT item_title FROM module_item_titles WHERE module = '111'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '111')),
    ((SELECT item_title FROM module_item_titles WHERE module = '112'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '112')),
    ((SELECT item_title FROM module_item_titles WHERE module = '113'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '113'));
    INSERT INTO module1_2_v2 (item_title, comp_id, module)
    VALUES
    ((SELECT item_title FROM module_item_titles WHERE module = '121'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '121')),
    ((SELECT item_title FROM module_item_titles WHERE module = '122'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '122')),
    ((SELECT item_title FROM module_item_titles WHERE module = '123'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '123')),
    ((SELECT item_title FROM module_item_titles WHERE module = '124'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '124')),
    ((SELECT item_title FROM module_item_titles WHERE module = '125'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '125')),
    ((SELECT item_title FROM module_item_titles WHERE module = '126'),
    [comp_id],
    (SELECT module FROM module_item_titles WHERE module = '126'));
    COMMIT;
    Last edited by mstopkey; 08-29-2014, 03:00 PM. Reason: Changing approach

  • #2
    Hi Mark, can you describe your problem? Afaik it's business as useual: create a sql statement and execute it. It might be that you need a sc_committrans before as your current transaction might be corrupted (of your form). Do you get an error message?
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #3
      It gives me Syntax error at 'INSERT INTO module1_1_v2 (item_ti' and stops right there.

      Comment


      • #4
        Does this code work outside Scriptcase?
        Best regards,
        Carlos Lacerda.
        ScriptCase Commercial Manager.

        Skype: carlos.lacerda82
        Email: carlos@scriptcase.net
        Visit our Blog: http://www.scriptcase.net/blog/
        Visit out fan page: http://www.facebook.com/Scriptcase

        Comment


        • #5
          Yes, works from MySql cli. Except of course where [comp_id] which is needed in the app to pass company variable. In MySql cli, I tested with static comp_id of '5' and it works as intended. I just cannot get the beginning and ending code in to work with sc_sql_exec and I get that error.
          Last edited by mstopkey; 08-29-2014, 01:01 PM.

          Comment


          • #6
            A Different way

            This is what I am currently attempting.
            Start Code:

            $insert = "insert into module1_1_v2 (comp_id, item_title, module)
            ({comp_id}, (select item_title, module from module_item_titles where mod_table_id = 'm11'))";
            )
            sc_exec_sql($insert);
            sc_commit_trans();

            End Code:

            But I get this error after the below statement.
            This is what it is trying to insert.

            (mysqlt): insert into module1_1_v2 (comp_id, item_title, module) (5 , (select item_title, module from module_item_titles where mod_table_id = 'm11'))

            This is the error.

            1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5 , (select item_title, module from module_item_titles where mod_table_id = 'm11' at line 2

            It is pulling the new comp_id from the field, but it looks like sql is not closed or quoted correctly. Does anyone see the mistake in the code?

            After successful, I will then add the other module tables inserts.
            Last edited by mstopkey; 08-29-2014, 03:01 PM.

            Comment


            • #7
              Where is the VALUES?
              /Giuseppe

              Professional Scriptcase Services
              Some Customers opinions

              Comment


              • #8
                You referring to the VALUES comment in string or where are they actually coming from? I have tried

                $insert = "insert into module1_1_v2 (comp_id, item_title, module)
                VALUES
                ({comp_id}, (select item_title, module from module_item_titles where mod_table_id = 'm11'))";
                )
                sc_exec_sql($insert);
                sc_commit_trans();

                This does not work, {comp_id} is from form field, item_title and module is select from other table.

                Comment


                • #9
                  Your {comp_id} has to be inside of the select.

                  $insert = "insert into module1_1_v2 (comp_id, item_title, module)
                  VALUES
                  (select {comp_id}, item_title, module from module_item_titles where mod_table_id = 'm11')";

                  jsb

                  Comment


                  • #10
                    Hi jsb,

                    I just tried that. No go. I get the following.

                    1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 5 , item_title from module_item_titles where mod_table_id = 'm11')' at line 2

                    Please keep in mind {comp_id} is form field. I can run this all day long in MySql and pass the field value. But when I put it in ScriptCase event, it blows up.

                    MS

                    PS, I currently have it stripped down to just the field value and one select field from table for debugging purposes.
                    Last edited by mstopkey; 09-02-2014, 10:01 AM.

                    Comment


                    • #11
                      Perhaps you can consider to put the text in a help-variable. Then putting the sql statement between double quotes you can let php evaluate the variable to a decent sql statement. If needed you need to single quote the alphanumeric vars.

                      " .... myfield='$helpfield' ..."
                      Albert Drent
                      aducom software netherlands
                      scriptcase partner, reseller, support and (turn-key) development
                      www.scriptcase.eu / www.scriptcase.nl

                      Comment

                      Working...
                      X