Creating Multiple Record Update Forms

[R Duncan]

Hi,

I'm considering moving a payroll application out of Microsoft Access to some
web-based solution. It is getting way to big for Access and the system is
growing unstable, so I'm learning PHP and MySQL and things are looking very
promising.

There is one piece of this I am having problems with. The attendance module
shows users all their employees in a table and allows them edit it this way.
My users need to be able to see and update all their employee records at
once, and they need to do it quickly, so they can't wait to load one
employee at a time.

So they would see something like this:

Clock Date Hours OT Absent
123 7/7/04 8 0 0
456 7/7/04 9 1 0
789 7/7/04 5 0 3
987 7/7/04 0 0 8

They would be able to update the Hours, OT, and Absent fields, and they
should be able to insert records.

I can figure out how to do a single record update form (like the one you get
with the Update Form wizard in Dreamweaver), but not a multiple record
update form. That is just not practical.

I think I can make each row a form and put an update button at the end. I've
seen this done on some web sites and that might work, but it could still be
too slow. What I would like to do is have one update button at the end of
the form that would send all the records back to the server (or maybe just
those that were changed). Does anyone know if this is possible and have any
pointers on how it is done? Even directions to a good web resource for this,
or a book would be appreciated.

Thanks,
Rob

[Geoff Berrow]

I noticed that Message-ID: <BD*********************@yahoo.com> from R
Duncan contained the following:

What I would like to do is have one update button at the end of
the form that would send all the records back to the server (or maybe just
those that were changed). Does anyone know if this is possible and have any
pointers on how it is done? Even directions to a good web resource for this,
or a book would be appreciated.

Include the record id as a hidden field.
<input type="hidden" name="id[]" value="id from database">

Make the fields you want to update into arrays. e.g. <input
type="textbox" name="hours_worked[]"value=" Hours_worked from database>

for($i=0;$i<count($_POST["id"]);$i++){
$hw=$_POST["hours_worked"][$i];
$id=$_POST["id"][$i]
$pdate=mysql_query("UPDATE table SET hours_worked='$hw'
WHERE id=$id");
}

add more fields to same loop as necessary

Apparently there is no problem updating all records since MySql will
ignore those that haven't changed.
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[CJ Llewellyn]

"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:hl********************************@4ax.com...

I noticed that Message-ID: <BD*********************@yahoo.com> from R
Duncan contained the following:

What I would like to do is have one update button at the end of
the form that would send all the records back to the server (or maybe justthose that were changed). Does anyone know if this is possible and have anypointers on how it is done? Even directions to a good web resource for this,or a book would be appreciated.

Include the record id as a hidden field.
<input type="hidden" name="id[]" value="id from database">

Make the fields you want to update into arrays. e.g. <input
type="textbox" name="hours_worked[]"value=" Hours_worked from database>

Not a good idea for the simple reason you can't guarentee that the browser
will return hours_worked[] in the same order as id[].

For each field on row embed the row id in the field name

<input type="text" name="hoursworked_$id" value="$hoursworked">

foreach($_POST AS $k => $v)
{
/*
check for each field in the row by writing a reg expr
*/
if(preg_match("^hoursworked_" , $k))
{
list($ignore , $id) = split("_" , $k)
{
$aPayroll[$id][HOURS_WORKED] = $v;
}
}
}

[Geoff Berrow]

I noticed that Message-ID: <cc**********@slavica.ukpost.com> from CJ
Llewellyn contained the following:

Include the record id as a hidden field.
<input type="hidden" name="id[]" value="id from database">

Make the fields you want to update into arrays. e.g. <input
type="textbox" name="hours_worked[]"value=" Hours_worked from database>

Not a good idea for the simple reason you can't guarentee that the browser
will return hours_worked[] in the same order as id[].

In which case you could simply use a counter (hours_worked[$i] id[$]) to
make sure.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[CJ Llewellyn]

"Geoff Berrow" <bl******@ckdog.co.uk> wrote in message
news:ou********************************@4ax.com...

I noticed that Message-ID: <cc**********@slavica.ukpost.com> from CJ
Llewellyn contained the following:

Include the record id as a hidden field.
<input type="hidden" name="id[]" value="id from database">

Make the fields you want to update into arrays. e.g. <input
type="textbox" name="hours_worked[]"value=" Hours_worked from database>

Not a good idea for the simple reason you can't guarentee that the browserwill return hours_worked[] in the same order as id[].

In which case you could simply use a counter (hours_worked[$i] id[$]) to
make sure.

How would you know the start and end points?

You could query the payroll table to see what rows should have been returned
by the form.

Better still, store them in a session array.

[Geoff Berrow]

I noticed that Message-ID: <cc**********@slavica.ukpost.com> from CJ
Llewellyn contained the following:

In which case you could simply use a counter (hours_worked[$i] id[$]) to
make sure.

How would you know the start and end points?

Well I'd like to leave the OP something to do, but I was thinking
something like this:-

$i=0
while($myrow=$mysql_fetch_array($result)){

print "<input
type=\"textbox\"name=\"hours_worked[$i]\"value=\"".$myrow['hours_worked']."\">";
print"<input type=\"hidden\"name=\"id[$i]\"
value=\"".$myrow['row_id']."\">";
$i++
}

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/