updating a number of records

[dbaseboy]

Hi all,

Lets see if I can manage to phrase this question so it can be understood (its clear in my head but not so sure on paper).

Im busy developing a management system which includes a diary of whos in/out in a week. What Im looking to have is a table displayed on the page with the peoples names down the side and the next 14 days dates across the top, each "cell" would then contain a checkbox to tick for in or nothing if theyre out.

Producing the table shouldnt be a problem however I cant get my head around the update part, ie how do you tell the system to add an entry into the table for each person on the relevant day.

I've made a sample jpeg here

showing the basics of it, I then have a mySQL table called "diary" with 3 columns: unique id, date, personid. With the view to putting an entry in there for each day a persons available (and no entry if theyre not) ie when the Submit button is clicked an entry should be recorded for every personid and date that has a tick in it.

While writing this Im now wondering if this is the best way to do it lol, one question thats just come to mind is, if the box was ticked originally and then you untick it because somethings changed then how do I spot that and delete the original entry?

Oh my god this is way tooooo difficult to explain!!!

If anyone can understand what Ive written and knows the answer then Id be grateful.

Thanks

Peter

 

[BeRtjh]

Is the box originally ticked..? Do you want to untick it as an administator or should this be done automatically?

 

[ToeKnee2]

If I've understood correctly, I think you will need some sort of "if" statements to do this.

So...

The If statements should determine if the databse should add or remove the value.

Here is the pseudo code...

For each Day of the week {

For each person { 

if { day is ticked - insert value into table where personid = **.
}else {
set value = 0 in table where personid = **
}

} //end of people
} //end of days of the week

Sorry, I know it's a little crude, but I hope this should give you an idea of one way you could do this...

ToeKnee

 

[jpadie]

given your data schema i'd assume that your primary key was not the uniqueid but actually the combination of the date and the person.

then assuming that you are using a form control schema like this:

<input type=¨checkbox¨ name=¨2007-01-15[name1]' />

you would iterate through the submitted form like this:

$names = array(¨name1¨, ¨name2¨); //etc
if (isset($_POST)){
 for ($i=1; $i<=31; $i++){
  $j = $i <10 ? '0'.$i : $i; //add a leading zero
  foreach($names as $name){
   if (isset($_POST['2007-01'.$j][$name]){
    $query = ¨Replace into sometable 
             set 
             personid = ¨.mysql_escape_string($name).¨,
             date = '2007-01-$j',
             intheoffice = 1¨;
   } else {
    $query = ¨Replace into sometable 
             set 
             personid = ¨.mysql_escape_string($name).¨,
             date = '2007-01-$j',
             intheoffice = 0¨;
   }
  }
 }
}

you do end up with a large number of database writes. you may be able to reduce this materially by adding some intelligence to the process to write only changes to the db. you could do this by including some javascript in the form to provide a record ¨dirty¨ marker.

or ... you could attach each checkbox to a js event that triggers an ajax conversation with the server to update just that record. this would obviate the need for a page refresh on each change and would also potentially avoid wasted time if a large number of changes were made by a user but potentially could not be written and thus cause wasted time. this would be pretty trivial to code for but woudl require that your users have javascript turned on in their browsers.

 

[overyde]

You need to basically give each entry(person) an id(normally the primary key id):

<input type=\"hidden\" name=\"id\" value=\"$id\">

<input type=\"text\" name=\"monday[$id]\" value=\"$monday\" size=\"10\" maxlength=\"15\">

<input type=\"text\" name=\"tuesday[$id]\" value=\"$monday\" size=\"10\" maxlength=\"15\">

Once that is then done you need to loop through each field to update.

foreach ($_POST['id'] as $f) {
	$monday = $_POST['monday'][$f];
	$tuesday = $_POST['tuesday'][$f];
	
		
mysql_query("UPDATE appointments SET monday = '$monday', tuesday = '$tuesday' WHERE sch_id = '$f'");
			
}

That should give you enough to work on as an idea


Reality is built on a foundation of dreams.