insert data to mysql database

[Former Community Member]

Hi everyone,

i've been looking all over the internet to find solutions to my problems, but it's not that easy to find good explanations of how things work with databases.

So, here's the situation:

i had a form (built with LC designer 8.0) with a HTTPsubmit button linked to a php script that would handle all the communication to my mySQL Database. Everything works fine for that.

now, i have a second form and wanted to do the same. But on this new form i have some tables with repeating data with the same name (each row has the same nodes), and the HTTP POST method will give me only one of the data. I could change the name of each node to make it unique, but 1. it takes time, and 2. it's not a very clean solution

So i tried to set up the famous "DataConnection" which i tried to avoid at first. I successfully installed the ODBC connector for mySQL and the communication between the form and the database is perfectly fine. But it is my understanding that, with the DataConnection "method" each field of the form corresponds to one field of the database. Whereas i would like to insert a new mySQL entry for each row of my tables.

This is where I'm stuck. I thought about two methods:

1. I'm a newbie with DataConnection so there is maybe a way to do what i want, but i don't know it. Ideally, I would imagine something like that: "OK there's good communication with the mySQL database. When I click the "submit" button, proceed with the javascript code that gathers information from the form and gives an INSERT query to the mySQLdatabase; and do that for each row of the table." Gathering the information is no problem, but how should I handle these multiple "INSERT" query?

2. Maybe there's also a way to modify the submit button, so that data is sent to a server-side php or js script, but instead of having urlencoded method or HTTP POST method, send the whole XML data and the script will deal with it. Actually, I know that this modification is possibe; but i have no idea on how to handle the data coming from the form if it's not HTTP POST (or GET). If i choose a submit to a php script, and i send XML or XDP, what is actually sent to the script?

Well, many thanks in advance if i finally find a solution!

tguiot.

[Former Community Member]

IMHO - the way to accomplish this is the first way. This technique allows you to be more flexible with the code that is writing to the DB and keeps it all in onw place. If you have the form communicating with teh DB directly you will have to configure a DSN on each users machine, you will have to use Acrobat or Reader Extend the form for Reader users to be able to make that connection from Reader and the users will have tobe on the same network as the database to be abel to connect to it.

If we assume you will stay with teh 1st way then we can look into why you are only getting one row in your post. If I send the entire XML data stream in I woudl expect that the same thing will happen (you will only get one row). Is the table setup as a dynamic table  (i.e. can I add and remove rows on the fly) or is it a static table (x number of rows at all times)? What is the binding for the row subform and the binding for a form in the row?

If not and you want to pursue the 2nd option we can do that as well.....your choice!

Paul

[Former Community Member]

thank you for your answer pguerett.

I would prefer the first solution as well.

Here below is a screen capture of the form with a static table (an excerpt, there are 6 pages with the same table). Each row should be inserted as a new line in the mySQL database (because each row describes different lesions: bone lesions, liver lesions, etc. And the type of lesion should also be inserted in the database).

As i said, I'm a newbie with DataConnection. As far as my understanding goes, i can link one form field to one database field. But then, i can only have one "insert" query and i should have as many insert queries as there are rows. So basically, i think the problem is just my limited knowledge on DataConnection and how to precisely manipulate which data will be inserted in the DB... Unfortunately, the more advanced manipulation of DataConnection doesn't seem to be easily available on the net, and i hoped i would find some help in the forums.

So the questions are:

- do I really have to link my form fields with the database fields? and can i link multiple form fields to the same database field?

- how do i say, in javascript code for the click event of my button: "give this sql query string to the database" (building the string itself won't be a problem) ?

Thanks again,

Thomas.

[Former Community Member]

So if we are going with the 1st solution then the trick is to get all of the data in the table to get posted which has nothing to do with the update of the database. If you submit the data as xml instead af posting name value pairs then you can parse the xml to give you the row by row information and do an update of the table for each row.

Make sense?

Paul

[Former Community Member]

Makes perfect sense. It's just that, to me, it sounds more like the second solution... when you say "data to be posted", it means to me "posted to a server-side script". And indeed, instead of posting name value pairs (which are limited: if there are multiple fields with the same name, only one will be posted), posting the whole xml.

But then, i don't know what is actually posted, and what i should use on my php script to handle the xml. Is it just a simple .xml file?

[Former Community Member]

Yes it is just a simple xml file. I am not a programmer so I do not know the specifics of how to get it from the response object to your program but I know it can be done.

Have a look at the adobe devnet site ……I remember seeing examples of this up there (not in PHP but the technique is what you are after)

Paul

[Former Community Member · Accepted solution]

OK nevermind, i found the solution with a proper method. Here's the link where i found what i needed: http://blogs.adobe.com/formbuilder/2006/12/databases_ins_upd_del.html

This is exactly what i originally intended to do, but it is indeed a more advanced manipulation of the database connection object. Here's the idea:

• create a DataConnection to your database, using the standard method (in my case, using the mySQL ODBC connector)
• create a copy of the DataConnection object, and clean it (to remove all the unnecessary nodes)
• replace the value of the <select> node by your sql string
• execute the sql statement with the method "open()"

in the link provided, you'll find the pdf file to open with livecycle designer. There's a "Database" variable which contains the functions, and the code is commented quite precisely.

All i now have to do, is navigate through my table and execute a sql insert for each row.

So good to finally have a solution!

Thanks anyway for your help pguerett.