Dave:
Ok - first please 'reply to all' for this message. While I have sent a subsribe to the list, I have yet to receive any mail from it, so I'm not certain that it is working for me yet.
<SNIP>
I want to have a method for inserting into the data_table that does uses this value:
ie, it should look like this:
Last Name ************ Name *************************** Keywords ****************************
and populate both the data_table, including the submitter id, and the data_table2.
So my questions are:
a) how do I get the sql_method that I am using to look up the id# for the user based on the LastName and insert that value into the data_table?
This might be a little dangerous since you've not indicated that LastName is a key field. Such a query (i.e., SELECT id from user_table where l_name = <!--#var l_name--> ) might very well return multiple rows. Could you get the person to "login" to the system and either a) set a cookie or b) a hidden form variable?
INSERT INTO data_talbe values ( "", '<!--#sqlvar name fmt=sql-quote-->', ???????????????????
what do I put in the ?????????????? to make it work?
b) then I have an sql_delimeter line, and I want to iterate through the keywords tokens and insert a record into the data_table2 for each of the keywords, including the submitter id# and the id# of the just inserted record from data_table. How do I do this??
I think this is harder than you might think... It's hard (impossible?) to get the id of a just-inserted record when you're using the database's native auto-increment facility. In cases where we've needed to this very thing we define a table key that is populated with a pseudo-random string (e.g., a string-ified time representation plus some a few random characters). This gives you uniqueness in the keys and, more importantly, you can do other things with the key value. So, to summarize I would, get the person to visit a "login" screen. Successful logins set a non-persistent cookie which is that person's user_id. Subsequent requests can use the cookie in DTML (and in SQL Methods). Inserts into data_tables should use a pseudo-random string (PrincipiaTime works nicely for this). This way you'll know the id. Maybe even stuff it into the REQUEST so it's available for the entire request. --Rob