[ic] return value of autonumber or code at time of INSERT
maillists
lists at gmnet.net
Mon Aug 22 16:05:45 EDT 2005
On Mon, 2005-08-22 at 12:17 -0500, Mike Heins wrote:
> Quoting maillists (lists at gmnet.net):
> > HI,
> >
> > I have searched the docs and mail list and I have not found the answer
> > to this. I need to be able to get the value of the primary key made by
> > autonumber or auto_increment at the time it is created.
> >
> > Here is my scenario:
> >
> > * user creates an account or logs in.
> >
> > * user can insert a new record in a table via a form. call it tableA
> >
> > * When the user inserts this record, into tableA, 2 things happen.
> >
> > 1) user gets an email with the new record ID or code in the email.
> > 2) I update the value of a field in another table say tableB with the
> > value of the ID or code from the new inserted record in tableA.
> >
> > I know how to do all the inserts, email, and everything. I just can't
> > figure out how to return the value code of tableA at the time it is
> > created.
> >
>
> Depends how you are creating the record.
>
> If it is with embedded Perl, it is returned from set_slice:
>
> $Scratch->{new_key} = $db->set_slice(undef, \%record);
>
> If it is with [table-editor] or mv_action=set, you should find [cgi
> the_key] set to the new key value upon return from writing. You can
> control where that value goes with mv_data_return_key, meaning if you
> do in the table-editor:
>
> hidden.mv_data_return_key=foo
>
> then [cgi foo] will contain the new key value.
>
Thanks Mike,
I'm still not getting a value returned.
I hope this email is not too long...
I guess my problem is the "way" that I am inserting the data.
My form is based on the "contact us" web form in the foundation catalog,
where is sends an email. My database "tableA" is a mysql table with
code set to type=INT(20) and auto_increment.
I was told that I need to set "code" to "NULL" in the form in order to
make the auto_increment work in mysql. The auto_increment DOES work!
however the tableA.autonumber file is not incremented. I guess this is
not used. when I use [value code] or [cgi code] I get the expected value
of "NULL" that I set on the form. I guess that MySQL ignores this and
just auto increments the value but does not tell Interchange what is
was. I think I need to turn off the auto_increment in MySQL and have
Interchange do that work.
What is the best way to set the code on the form in a way that I can
retrieve it with [cgi code] ?
Here is a piece of my "test_form.html" file #######################
<form action="[process]" method=post>
<INPUT TYPE=hidden NAME=mv_session_id VALUE="[data session id]">
<INPUT TYPE=hidden NAME=mv_form_profile VALUE=new_test_form>
<INPUT TYPE=HIDDEN NAME="mv_data_table" VALUE="tableA">
<INPUT TYPE=HIDDEN NAME="mv_data_key" VALUE="code">
<INPUT TYPE=HIDDEN NAME="mv_data_fields" VALUE="code,field2,field3">
<INPUT TYPE=hidden NAME=mv_todo VALUE=set>
<INPUT TYPE=hidden NAME=mv_nextpage VALUE="@@MV_PAGE@@">
<INPUT TYPE=hidden NAME=mv_session_id VALUE="[data session id]">
<INPUT TYPE=hidden NAME=code VALUE="NULL">
<INPUT TYPE=HIDDEN NAME=field2 VALUE="test-value">
<INPUT TYPE=HIDDEN NAME=field3 VALUE="test-value">
<INPUT TYPE=SUBMIT name=mv_check VALUE="[L]Send Request[/L]">
[set Send Request]
[set mv_data_enable]1[/set]
[set mv_data_function]insert[/set]
[/set]
#########################################################
Here is my profile:################################
__NAME__ new_community_form
&success=../special_pages/new_test_request
&fail=test_form
__END__
##################################################
Here is my new_test_request special page:
This sends the email, and writes data to another file, then bounces back
to the test_form. ##############################################
[email
to="__EMAIL_SERVICE__"
from="[value fname] [value lname] <[value email]>"
Subject="Test Email"
]
Info:
ID: [cgi code]
field2: [value field2]
field3: [value field3]
------
[/email]
[tag flag write]tableB[/tag]
[query list=1
row-count=1
table=tableB
st=sql
sql="UPDATE tableB
SET community = '[cgi code]'
WHERE user_id = '[value mv_username]'
"]
[/query]
[set message_sent]1[/set]
[bounce page=test_form]
#############################################################
Thank you
Chris
More information about the interchange-users
mailing list