The fastest way to update a database table

Question

Hello,

I've got a website where people can create a table of an unknown length containing two columns, word_1 and word_2, and store this table in a database. It is of course very simple to insert these values into the database by just iterating through the rows.

Now someone decides to update some of the values and he goes to the edit-page where he finds all the rows in <input/>-fields in a table. What is the fastest way to update these values when he edits the content, adds another row or deletes a row?

For example, this is the table after it was created:

     [word_1        ]  [word_2       ]

1.   [foo           ]  [bar          ]
2.   [something     ]  [more         ]
3.   [another       ]  [row          ]
...
600. [another_value ]  [blabla       ]
601. [last          ]  [row          ]

And then someone decides to edit it:

1.   [foo           ]  [bar          ]
2.   [changed_val   ]  [more         ]
...
234. [another_change]  [changed_value]
...
600. deleted
601. [last          ]  [row          ]
602. [new_row       ]  [new_value    ]

I think my HTML-code will look like the following:

<table>
    <tr>
        <td>
            <input type="hidden" name="row[0][id]" value="id-2" />
            <input type="text" name="row[0][word_1]" value="value_1" />
        </td>
        <td>
            <input type="text" name="row[0][word_2]" value="value_2" />
        </td>
        <td>
            <img src="img.gif" onclick="delete_row()" />
        </td>
    </tr>
     <tr>
        <td>
            <input type="hidden" name="row[1][id]" value="id-5" />
            <input type="text" name="row[1][word_1]" value="value_1" />
        </td>
        <td>
            <input type="text" name="row[1][word_2]" value="value_2" />
        </td>
        <td>
            <img src="img.gif" onclick="delete_row()" />
        </td>
    </tr>
</table>

Is there a better way to update the table than to go trough each of these rows and check with a query if the current row is edited? And what is the best way to see if a row is deleted?

I know this data is very easy to store in XML, but I prefer a database so I can link data from other tables and users etc. together.

Answer 1

Probably the fastest way, assuming the tables are going to be big, would be to pull the whole thing into memory with one query, loop through that comparing the rows, and updating as needed.

Better would be to do something dynamic, where queries are sent to the db on the fly as the rows are edited using an AJAX call.

Answer 2

you can use delete_row() with the id of that word and each time a delete_row is run it keeps the data in a js variable and at the end when the user clicks update button, you know which rows to update

var deletedRows = new Array();
var numberOfRowsDeleted = 0;
function delete_row(row_id)
{
    deletedRows[numberOfRowsDeleted] = row_id;
    numberOfRowsDeleted++;
}