can you update multiple fields-records in a table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tcl4p
Forum Newbie
Posts: 15
Joined: Mon Apr 02, 2007 9:28 am

can you update multiple fields-records in a table

Post by tcl4p »

Is there a way to update multiple records in a table on submit? That is to say, you have a table where each row has input fields such as below which is loaded from a table in a mysql table.
<tr>
<td width="5%"><input name="<? echo $ID; ?>" id="pID" type="checkbox" value="pID" /></td>
<td><input name="txtAsOfDate" type="text" id="txtAsOfDate" size="15" maxlength="12" value="<? echo $AsOfDate; ?>"</td>
<td align="right"><input name="txtQuantity" type="text" id="txtQuantity" size="15" maxlength="12" value="<? echo $Quantity; ?>"</td>
<td align="right"><input name="txtPrice" type="text" id="txtPrice" size="15" maxlength="12" value="<? echo number_format($Price, 2, '.', ','); ?>"</td>
<td align="right"><input name="txtCurValue" type="text" id="txtCurValue" size="15" maxlength="12" value="<? echo number_format($CurValue,2, '.', ','); ?>"</td>
</tr>


When I click the submit button and look at the data in the POST, the only row data(txtAsOfDate, txtQuantity, txtPrice and txtCurValue) that comes across is the first record. I did see the ID values for the check boxes, but that was all. Is there a way to allow the user to edit multiple input fields in a table and then update the data from each row upon submit?

Thanks,
Tom
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: can you update multiple fields-records in a table

Post by califdon »

Yes. It is called programming. OK, that's being sarcastic, what I'm really saying is that you can do whatever you want to do, but if you want to do something complicated, it will take a lot of code. You will have to determine the logic that will check to see what the user has changed, then piece together the SQL statement that will find each affected record and update the appropriate fields.

I would think twice about trying to do this. It's a lot of work for functionality that's probably not really that valuable.
tcl4p
Forum Newbie
Posts: 15
Joined: Mon Apr 02, 2007 9:28 am

Re: can you update multiple fields-records in a table

Post by tcl4p »

Thanks for the reply. The question wasn't about the need for programming, rather if, using programming, you could build an editable table. As I mentioned I could see the values for the ID and the question was directed toward finding out if and how to code the table so that the data fields, like the ID, could be identified in the POST. Judging from my search on the web this is not something done on a regular basis.

Regards,
Tom
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: can you update multiple fields-records in a table

Post by mikosiko »

it is not uncommon... and califdon is correct... you need to program the functionality... or use some market available options like this
http://www.phpgrid.com/grid/

Miko
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: can you update multiple fields-records in a table

Post by califdon »

Actually, your question was about the need for programming, it's just that you weren't and aren't thinking of it in that way. My answer is still the correct answer. There is absolutely NOTHING that doesn't defy the rules of logic that can't be achieved by programming. The question is always whether the result is worth the effort.

When users are allowed direct access to data, the risk of destructive errors increases. Whether this is a consideration for a particular application varies, but I would say that in most serious applications, your most important responsibility, as a database designer, is to build in safeguards to validate data modification to some degree, and that can only be done by interjecting code between the user's actions and their effect on the stored data. Merely displaying multiple lines of data and allowing the user to skip around, changing data, is the most risky and usually the least efficient way to interact with data. It is the spreadsheet mentality--which is fine for spreadsheets, but not for a database.

That said, I would be the first to concede that some applications exist where it would be useful to allow direct editing on multiple rows, with a single submit. That can certainly be done, with quite a lot of programming. I wouldn't attempt to explain all the details in a forum post, but you have already recognized one of the issues, that of identifying the data that is POSTed back to the server. That can be done by using arrays for each table field, with the array index to associate the values for each row. So your html table might contain rows of <input> elements with names like pID[0], pID[1], pID[2], and txtPrice[0], txtPrice[1]. etc. Your script to handle the modified data would receive those arrays and the values could be used to build your UPDATE query. You could use a loop structure so that you only need to change the index as you loop through the returned arrays.

[Edit:] Mikosiko posted just after I began this post. Yes, there are solutions available, either commercial, as mikosiko referred to, or (probably) open source. You still need to be knowledgeable about PHP and your own data structure, but it might offer you the solution you are seeking.
Last edited by califdon on Tue Apr 20, 2010 1:08 pm, edited 1 time in total.
Reason: To acknowledge mikosiko's post
Post Reply