[Zope-DB] inserting data from multiple selection drop down into Mysql

[Charlie Clark]

On 2003-08-08 at 13:37:10 [+0200], info/caseviewer wrote:
> 6.    The problem (which you may have already figured out, is that I
> cannot insert any of the selected state_id values into the t_org table! I 
> am guessing there's something wrong with my insert syntax.Can you please 
> help? 
>  
> Thanks,
>  
> rex nedoli

Hi Rex,

reposting doesn't really help. I didn't reply initially because I wasn't 
quite sure what the problem was - I've pretty much gone "clean" on DTML 
having been prescribed ZPT by the doc! ;-)

<dtml-sqlvar "','+_.string.join(state_id,',')+','" type=string> 

does look suspect to me. You are inserting something without a name which I 
find very confusing. I need to write this out to understand what you want 
to do:

INSERT INTO t_org
(state_id)
VALUES
(",1,")

assuming state_id is an integer. But stop. According to your form state_id 
can be multiple so you want to store a list (If I understand you 
correctly)? My first reaction is: don't do this. You should always do m->n 
via an intermediary table. Having to use complex syntax otherwise is one of 
the best reasons for this. If you still wish to do things that way then I 
would suggest using a PythonScript for this transformational logic. I only 
use ZSQL myself as pretty much pure SQL with little more than 
<DTML-if></DTML-if> conditions.

If you have a PythonScript you have much more control of your variables and 
hopefully less work and less problems.

Your script can be something like this:

state_id = request.form.state_id
state_id = ",%s," %(",".join(state_id)) # assuming you want values ",1,2,3,"
context.insert_t_org(state_id=state_id)

Hope that helps a bit

Charlie