[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