Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating MySQL dB from HTML table values

    Hello,

    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:

    Code:
    # 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 = """
    <td>%s<br></td>
    ... (line repeated for each column)
    """ % row[0:15]
    
       req.write(new_row)
    Any ideas? Maybe this whole thing should be rewritten in something else.

    Thanks!

  • #2
    The fat guy next door VIPStephan's Avatar
    Join Date
    Jan 2006
    Location
    Halle (Saale), Germany
    Posts
    8,868
    Thanks
    6
    Thanked 1,029 Times in 1,002 Posts
    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”. I repeat again: Make your website work without JS before you think about using AJAX and stuff.

  • #3
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Last edited by martopoulos; 07-14-2011 at 12:55 AM.

  • #4
    The fat guy next door VIPStephan's Avatar
    Join Date
    Jan 2006
    Location
    Halle (Saale), Germany
    Posts
    8,868
    Thanks
    6
    Thanked 1,029 Times in 1,002 Posts
    You can assign array names to form controls like <input name="whatever[]" type="text" /> which can so a server side script (at least PHP, 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
    Code:
    <input type="text" name="whatever1" />
    <input type="text" name="whatever2" />
    …

  • #5
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    Quote Originally Posted by martopoulos View 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...
    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
    Code:
    <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.

  • #7
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, thank you, I appreciate the input and ideas.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •