Announcement

Collapse
No announcement yet.

mysql_insert_id() bug?

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

  • mysql_insert_id() bug?

    I've been trying several methods to get the primary key after an insert is done to a table. The primary key is autoincrement on the database.

    I've reviewed several other posts and it seems this may be a bug again! Looks like it was fixed but now I'm not sure.

    Try #1:
    in the onafterinsert event:
    sc_commit();
    $new_value = mysql_insert_id();

    When I display $new_value it is zero.

    Try #2:
    in the onafterinsert event:
    $new_value = mysql_insert_id();

    When I display the $new_value it is zero.

    It looks like this may have been fixed before but it doesn't seem to work. Am I just not doing it right?

  • #2
    Hi,
    mysql_insert_id() never worked consistently in the event structure of SC.

    Workaround:
    Put your primery key on the form, you can hide it though.
    onAfterInsert: $new_value = {your_primery_key};

    jsb

    Comment


    • #3
      Two ways I do it

      Hi. I have done it both of these ways in SC:


      - for the onafterinsert event, just use
      $whateverid = {ID}; // to get previous insert

      - after a manual insert do this:

      // insert a menu_day record using the above vars
      //////////////////////////////

      // convert date string to a mysql date field
      $mysqldate = date("Y-m-d H:i:s", $adate);

      // SQL statement parameters
      $insert_table = 'menu_day'; // Table name
      $insert_fields = array( // Field list, add as many as needed
      'MDDow' => "'$adow'",
      'MDDate' => "'$mysqldate'",
      'MenuCalID' => "'$amenucalid'"
      );

      // Insert record
      $insert_sql = 'INSERT INTO ' . $insert_table
      . ' (' . implode(', ', array_keys($insert_fields)) . ')'
      . ' VALUES (' . implode(', ', array_values($insert_fields)) . ')';

      sc_exec_sql($insert_sql);
      $recid = mysql_insert_id(); // save the record id, which is the MenuDayID


      The code above has worked for me on real-world apps over and over. Hope it helps,
      Jamie




      Originally posted by pkrawetzky View Post
      I've been trying several methods to get the primary key after an insert is done to a table. The primary key is autoincrement on the database.

      I've reviewed several other posts and it seems this may be a bug again! Looks like it was fixed but now I'm not sure.

      Try #1:
      in the onafterinsert event:
      sc_commit();
      $new_value = mysql_insert_id();

      When I display $new_value it is zero.

      Try #2:
      in the onafterinsert event:
      $new_value = mysql_insert_id();

      When I display the $new_value it is zero.

      It looks like this may have been fixed before but it doesn't seem to work. Am I just not doing it right?

      Comment


      • #4
        The id is already a read only display field on a tab. I have it marked as not required and calculated by database on insert (nothing for update as this is the primary key and update should not change the value).

        So from what you are saying I should see a value in the event onafterinsert? I don't.

        Below is my code. Email address is a field which gets updated during an insert/update and that resolves correctly:

        $reservationid = {idreservations};

        /**
        * Send a simple email
        */

        // Email parameters
        if ({email_address_1})
        {
        $mail_smtp_server = 'smtp.mail_server.com'; // SMTP server name or IP address
        $mail_smtp_user = 'valid@emailaddress.com'; // SMTP user name
        $mail_smtp_pass = 'somepassword'; // SMTP password
        $mail_from = 'someemail'; // From email
        $mail_to = {email_address_1}; // To email
        $mail_subject = 'some subject ' . $reservationid; // Message subject
        $mail_message = 'Thank you for your reservation with us.'; // Message body
        $mail_format = 'T'; // Message format: (T)ext or (H)tml
        $mail_port = '465';
        $mail_ssl = 'S';

        // Send email";
        sc_mail_send($mail_smtp_server,
        $mail_smtp_user,
        $mail_smtp_pass,
        $mail_from,
        $mail_to,
        $mail_subject,
        $mail_message,
        $mail_format,
        '',
        '',
        $mail_port,
        $mail_ssl);
        }
        Last edited by pkrawetzky; 05-06-2014, 12:38 PM.

        Comment


        • #5
          I think that the last insert id needs to be part of the transaction which has been executed. So above the commit. And the commit is only necessary if you do a sc_redir somewhere in the event.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Interesting... All the examples I've see have the commit the mysql_insert_id() after. So you are saying mysql_insert_id() then sc_commit?

            Comment


            • #7
              Originally posted by pkrawetzky View Post
              Interesting... All the examples I've see have the commit the mysql_insert_id() after. So you are saying mysql_insert_id() then sc_commit?
              Well I can be wrong, but it's what I would try. There are several problems with the last insert id as in the time the transaction is committing another transaction can be done and then the last insert id would not be trustworthy. But again, I'm not sure, but give it a try.
              Albert Drent
              aducom software netherlands
              scriptcase partner, reseller, support and (turn-key) development
              www.scriptcase.eu / www.scriptcase.nl

              Comment


              • #8
                In onmountian's example he performs the actual insert then mysql_insert_id(). In my code, SC is handling the insert. Does that make a difference?

                Comment


                • #9
                  Ok so I figured 2 things out. One I was missing the sc_begin_trans() in the onload event. I took Albert's advice and move the sc_commit_trans() after the mysql_insert_id() function. It gave me a record id but for the sc_log entry, not the insert on the table I wanted. So I guess I figured out how to use it, now I have to figure out how to get the new id from the correct table. I guess I could turn off logging on that particular form but I added this option for a reason.

                  Guess what I could do is devise a query to retrieve the new row using the form data... That is just more work for me.

                  Comment


                  • #10
                    Originally posted by pkrawetzky View Post
                    Ok so I figured 2 things out. One I was missing the sc_begin_trans() in the onload event. I took Albert's advice and move the sc_commit_trans() after the mysql_insert_id() function. It gave me a record id but for the sc_log entry, not the insert on the table I wanted. So I guess I figured out how to use it, now I have to figure out how to get the new id from the correct table. I guess I could turn off logging on that particular form but I added this option for a reason.

                    Guess what I could do is devise a query to retrieve the new row using the form data... That is just more work for me.
                    That's why the last_id is not a convenient way. Not completely safe too, but you can consider using a select max(key) from construction after the commit....
                    Albert Drent
                    aducom software netherlands
                    scriptcase partner, reseller, support and (turn-key) development
                    www.scriptcase.eu / www.scriptcase.nl

                    Comment

                    Working...
                    X