lwismanuel
Posts: 39
|
| Posted: 07/02/2008, 8:35 AM |
|
Hi everyone,
I need to insert a record where the last field on it is equal to the primary key.
Here are the fields I have on the table: "usr_id, usr, pwd, level, acl, stat, owner"
the primary key "usr_id" is set to auto_increment and I would like to enter in the last field "owner" the value of "usr_id" on insertion. I could do it using multiple queries but I want to know if it is possible to do it on one query only.
Thanks,
Luis Manuel
|
 |
 |
jjrjr1
Posts: 942
|
| Posted: 07/02/2008, 8:54 AM |
|
Hi
I think the easiest way to do that would be to add a hidden field on your data entry form. Call it owner or what ever.
On the before insert and before update events get the value of the usr_id and put it into owner.
$Container->owner->SetValue($Container->urs_id->GetValue());
Should work.
If you do not want to put it into 2 events you can put it into the forms validation event since that gets executed on both inserts and updates. Just not as clean with respect to documentation etc..
Let me know if that helps.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 07/02/2008, 9:11 AM |
|
Hi
On second thought, this might not work. I forgot you were generating the sequence using auto increment.
What you might have to do is in the after insert event is get the sequence id you just created with the insert by using the mysql_insert_id() function.
eg:
$ID=mysql_insert_id();
Then do a query to update the record where usr_id = $ID and put $ID into the owner field.
I think the minimun you can do this in is 2 queries one for the insert and one for the update since the auto increment value for your newly inserted record is not available till after the insert occurs.
Let me know if that is better.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
lwismanuel
Posts: 39
|
| Posted: 07/02/2008, 5:35 PM |
|
Thanks John, my only fear with that approach is if someone inserts a record before the second query gets executed and the second query updates the field with the wrong ID.
I was thinking on inserting the record first then get the primary key of that record and populate the owner field with it. Thanks again for the suggestion.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 07/03/2008, 8:00 AM |
|
Sure,
That is probably the safest way but will require 3 queries. It would be interesting to find out how the mysql_insert_id() functions exactly. Does it return the result of the actual last insert performed to the database or the last insert performed for the current session.?
Would be an interesting experiment.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 07/03/2008, 8:08 AM |
|
Hi
I had to look into this issue.
Here is the Mysql web site doc on this function and it sort of indicates that you get back the last inserted auto increment for the session that did the insert.
http://mirror.yandex.ru/mirrors/ftp.mysql.com/doc/refma...-insert-id.html
Maybe, I will test it out.
Let me know if you use this and if it works.
Take Care.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
lwismanuel
Posts: 39
|
| Posted: 07/03/2008, 10:35 AM |
|
John, you are correct. Thanks a lot for your assistance. 
|
|
|
|
