View Full Version : Updating MySQL dB from HTML table values

07-13-2011, 11:45 PM

I'm dynamically outputting the contents of a MySQL table using Python, to a HTML table, which users view (obviously) through a web browser. It looks great, but the problem is that I'd like users to be able to update these values. For example, I have the current value of certain fields in text boxes. I'd like users to be able to change those values, then hit a submit button or similar (ideally on-the-fly) to update the dB. I imagine javascript will need to pass values to a Python script, but I don't know what the best way to go about this is.

The table is generated with a loop like so:

# Python code embedded in a .psp file

cursor = db.cursor()
cursor.execute("SELECT from_name, from_address, project, orb_proj, app, tier, environment, network, security_zone, datacenter, server_type, num_servers, date, status FROM requests")

rows = cursor.fetchall()

for row in rows:
new_row = """
... (line repeated for each column)
""" % row[0:15]


Any ideas? Maybe this whole thing should be rewritten in something else. :(


07-14-2011, 12:14 AM
OK, at first you should note that JavaScript should only be regarded as usability enhancement to an already working website. So the first thing to have users update something would be an HTML form and server side script that is processing the input data upon submitting the form. Then you can add JavaScript so that, for example, the form is submitted and input data displayed without completely reloading the page. This is known as “asynchronous JavaScript and XML (http://en.wikipedia.org/wiki/Ajax_%28programming%29)”. I repeat again: Make your website work without JS before you think about using AJAX and stuff.

07-14-2011, 12:53 AM
Okay, well, that "server side script" is the big question mark for me. For example, how would I handle the fact that all the form elements (text boxes and drop downs) have the same name? They, and the rows that contain them, are generated with the above loop (one line would be <td><input ... value="%s"> ... </td>). Any easy way around this problem?

07-14-2011, 01:04 AM
You can assign array names to form controls like <input name="whatever[]" type="text" /> which can so a server side script (at least PHP (http://docs.php.net/manual/en/faq.html.php#faq.html.arrays), and I assume other languages, too) can work with it. Or, if you have some kind of loop I suppose you can pass the index, too, so each form control would have its own number like

<input type="text" name="whatever1" />
<input type="text" name="whatever2" />

07-14-2011, 01:14 AM
Yeah, that did occur to me, but I was hoping for something a bit cleaner as far as linking each row back to its respective SQL table row.

As it is now, the best, possible solution I can come up with is to use each row's index (either from the 'for' loop or the row's 'id' value stored in the dB) to name each form element element_id. Then pass the values to a python script using POST. Maybe I could make each row have its own form, so the user doesn't have to re-submit the entire table, including values that haven't even changed. Just thinking aloud here...

Old Pedant
07-14-2011, 01:31 AM
Maybe I could make each row have its own form, so the user doesn't have to re-submit the entire table, including values that haven't even changed. Just thinking aloud here...

Ummm...you can only submit *ONE* <form> per page. So if you made each row its own form, the user could only update one row per load of that page. After each update, he/she would have to wait for the server to reload all the rows and then go (again) change one row.

I think you need to move this to a Python forum and get suggestions from Python users on how best to accomplish this.

One difficulty with using same-named form fields, even in PHP where they use stuff like <input name="address[]" /> to imply an array of fields, is that *UNCHECKED* checkboxes do *NOT* appear at all in the data sent by the browser to the server. So if your rows use checkboxes you can be utterly hosed.

In general, I think it is better to do something like

<form ...>
<input name="name_773" value="Joe"/>
<input name="address_773" value="111 Elm St." />
<input name="city_773" value="Amityville" />
<input name="name_817" value="Harry"/>

That is, you append the *ID* of the record in the data base to each field name. Then it's easy to find and process all related fields in your server-side code.

But, as VIPStephan said, none of this has anything to do with JavaScript. You should always ensure your pages can and will work when JavaScript is disabled, if at all possible. Depend on your server-side coding to do the heavy lifting.

I'm 90% sure you can do all of this in pure Python code. You can certainly do it in pure ASP, JSP, ASP.NET, PHP, and CGI (Perl) code. So, again, try posting in a Python forum.

07-14-2011, 01:52 AM
OK, thank you, I appreciate the input and ideas.