WEB web forms and databases

Discussion in 'OT Technology' started by Limp_Brisket, Apr 9, 2008.

  1. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    i have a form in PHP that has a bunch of text inputs filled with data from a table in the database. what's the most efficient way to store this data when the form is submitted? it's basically just a list of phone numbers.

    i could do an insert/update statement on each data element one by one but that could get long depending on how many rows the form has and that'd be a lot of queries plus some fields may not have changed.

    i could delete all the rows in the table then just insert all the fields from the form.

    i could pull all the rows from the database and compare each value with the new values and decided which ones to update/insert/delete.

    what do you guys usually do? maybe something i haven't listed?
     
  2. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    or i could also have the list be uneditable with icons next to each one to either delete or edit and do it on a 1 entry by 1 entry basis. plus have a field at the top to add entries? that's another option.
     
  3. 2ofdem

    2ofdem OT Supporter

    Joined:
    Jun 17, 2003
    Messages:
    114,854
    Likes Received:
    8
    Location:
    aus.vic.mel
    Its probably more efficient to do it lazy and just update/insert everything.
     
  4. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    yup, that's the kind of problem i was finding a solution for. it just didn't seem very efficient because depending on the amount of data you have you could be doing 100's of SQL queries since you would be doing an SQL query for every row.. was just wondering if there was a more efficient method someone has used.
     
  5. 2ofdem

    2ofdem OT Supporter

    Joined:
    Jun 17, 2003
    Messages:
    114,854
    Likes Received:
    8
    Location:
    aus.vic.mel
    you could do it in one big sql query with a loop to build the query
     
  6. Vailripper

    Vailripper Daywalkers have feelings too.

    Joined:
    Jan 25, 2006
    Messages:
    6,170
    Likes Received:
    0
    Location:
    Bellingham, WA
    so it lists all of the phone numbers, but you only want to update the phone numbers which were actually modified?

    In that case you could dump all the phone numbers into an array, use implode to put them in a string and store it in a cookie. Then when they submit the form, explode that stored cookie back into an array. Compare all the values, updating for those that are different.
     
  7. Vailripper

    Vailripper Daywalkers have feelings too.

    Joined:
    Jan 25, 2006
    Messages:
    6,170
    Likes Received:
    0
    Location:
    Bellingham, WA
    updates are pretty server heavy, and the time they take scales up rather quickly as your rows increase.

    But yeah, it obviously depends alot on often it will be used.
     
  8. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    i thought of this too. well with a session instead of a cookie. or with the hidden form elements with the previous data to compare with. i wonder if this is the best solution :dunno:
     
  9. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    eek, 500 queries with each form save. that makes me cringe. :hsd:
     
  10. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    well i just ended up displaying them all as plain text and putting icons on each row for "edit" and "delete" and then i have a form at the top to add new entries. i guess i'll stick with that for now unless i hear some solution that blows my mind :x:
     
  11. Vailripper

    Vailripper Daywalkers have feelings too.

    Joined:
    Jan 25, 2006
    Messages:
    6,170
    Likes Received:
    0
    Location:
    Bellingham, WA
    telepathic php

    :eek3:
     
  12. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    as often as my boss needs to update this phone list :dunno: probably a few times a day, max

    either way, a lot of queries is a lot of queries. the database already takes a lot of hits from other places and the last thing i need is it freezing up for a few seconds while it runs 500 updates :dunno:
     
  13. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    hmm, i dunno about making the user do the extra work; although you did give me an idea.

    onload you could install an event handler on each element that keeps track of its original value and whether it's been changed. and since disabled form elements don't submit data (as i painfully discovered one day) you could have the onsubmit event loop through and disable each form element that hasn't been changed right before submission so they don't get sent to $_POST.

    that's just crazy enough to work and it would actually be pretty simple javascript. that's pretty much like your idea but then the browser keeps track of it instead of using check boxes.
     

Share This Page