mysql_insert_id() bug?

Scriptcase 7 Bugs
,
#1

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

#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

[QUOTE=pkrawetzky;23210]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?[/QUOTE]

#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);

}

#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.

#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?

#7

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.

#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?

#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.

#10

[QUOTE=pkrawetzky;23305]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.[/QUOTE]

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…