Multiple field update at once

[Ricky.]

Hi,

 

I am looking to update an already existing table, its an attendance table

Its table design goes as follows:

 

 

-------------------------------------------------

Date          |    employe ID |  Status

--------------------------------------------------

12-03-2012|    232            | a

12-03-2012|    234            | a

12-03-2012|    235            | a

13-03-2012|    232            | p

13-03-2012|    234            | p

13-03-2012|    235            | a

--------------------------------------------------

 

 

Here a = absent , p = present

 

 

So, I have form which fetches existing table then returns result as below modified

 

 

    [2011-12-01] => a

    [2011-12-02] => a

    [2011-12-03] => p

    [2011-12-04] => p

    [2011-12-05] => p

    [2011-12-06] => a

with userID as hidden value

 

So, now I have update STATUS column, I can do it for one like :

 

UPDATE records SET status = 'a' WHERE date = '2011-12-02' AND employe_id = userID

So, it will update status to 'a' for this date 2011-12-02 where employe_id is userID

 

Now issue is that I need to update most probably few hundred records at once and I am not able find any query which can do this in one SQL query, last resort could be using php loop but that will kill SQL server.

 

I have tried to look into case and IN statement but I think I am not very good to do this currently.. any insight will be grateful .

 

 

[AyKay47]

What are the specific requirements of the update?

Perhaps something like this:

 

UPDATE `records` SET `status` = 'a' WHERE `date` = '2011-12-02' AND `employe_id` IN ( ... )

 

The IN clause would contain a comma delimited list of ids

[PFMaBiSmAd]

Why do you think you would be UPDATING 100's of rows. That would imply that 100's of pieces of original data were entered incorrectly or that you are simply looping over all the submitted form fields instead of just the ones that were changed.

[Ricky.]

Well, I will definitely need to check if it is already set to same status or not ..

btw, wht do you mean that I I first find what are the rows changed and then insert data into them using separate query.. or just check each row , if data is changed then replace else do  nothing using single query ?

 

Please give example..that would be helpful.

 

[PFMaBiSmAd]

If you want to mass update any number of data values (one overall form with one submit button), you can either carry the existing values in hidden fields or in a session array variable, or you can query for the all the current values in the form processing code. Alternatively, you can update each value as you change it (one form and one submit button for each value.)

[The Little Guy]

I don't know how your page is laid out but assuming that you are using two radio button for each employee (one for present one for absent)

 

<input type="radio" name="ap['a']" value="123" />
<input type="radio" name="ap['p']" value="123" />

<input type="radio" name="ap['a']" value="456" />
<input type="radio" name="ap['p']" value="456" />

<input type="radio" name="ap['a']" value="789" />
<input type="radio" name="ap['p']" value="789" />

 

now that builds us two arrays one for present employees one for absent employees.

 

Next we want to update our rows.

 

The follow uses a unique key on date and employee_id (for an on duplicate key update)

 

// Process the absent employees
$absent = implode(",", $_POST['ap']['a']);
$abs = array();
foreach($absent as $a){
$abs[] = "(currdate(), $a, 'a')";
}
$av = implode(",", $abs);
mysql_query("INSERT INTO my_table (date, employee_id, status) values $av ON DUPLICATE KEY UPDATE status = values(status)");

// Process the present employees
$present = implode(",", $_POST['ap']['p']);
$pre = array();
foreach($present as $p){
$pre[] = "(currdate(), $p, 'p')";
}
$pv = implode(",", $pre);
mysql_query("INSERT into my_table (date, employee_id, status) values $pv ON DUPLICATE KEY UPDATE status = values(status)");

[Ricky.]

If you want to mass update any number of data values (one overall form with one submit button), you can either carry the existing values in hidden fields or in a session array variable, or you can query for the all the current values in the form processing code. Alternatively, you can update each value as you change it (one form and one submit button for each value.)

Hey, thanx Little GUY and PFMaBiSmAd, but I think we are diverted because my actual issue is not receiving form data, I am already receiving it as array below(and above all this form is for single employe for various date where I am receiving employe ID as hidden field)....

 

    [2011-12-01] => a

    [2011-12-02] => a

    [2011-12-03] => p

    [2011-12-04] => p

    [2011-12-05] => p

    [2011-12-06] => a

 

I only need to to update, for example, an employe could be ABSENT on 2011-12-01 But he was taken as PRESENT, now I need to fix, so I need to update

  2011-12-01 to b to

  2011-12-01  to a,

 

However, issue arises only when I need to update many dates, like below

    [2011-12-01] => a

    [2011-12-02] => a

    [2011-12-03] => p

    [2011-12-04] => p

    [2011-12-05] => p

    [2011-12-06] => a

 

 

So I have this data provided by form, now I need to check it against already submitted data and if STATUS is changed (see my original table design for STATUS column) then update in DB at once.

[The Little Guy]

I only need to to update, for example, an employe could be ABSENT on 2011-12-01 But he was taken as PRESENT, now I need to fix, so I need to update

my "ON DUPLICATE KEY UPDATE" will just that

[Ricky.]

Hmm.. but I don't want to insert at all as they already there but Yes, it will update if its already there.. however I will need to know on what date..

But , I have already solved this issue, taking cues from your previous example, I splitted it two parts ie. one for dates on which employe is absent and one for employe is present.

So, it makes two queries in total and also will first check if its already there then will not update.

 

I actually made a function for this :

 

<?php 
function update_individual_attendance($attendance , $uid)
{
// If all arrays are available 
		If(is_array($attendance))
			{
			$tmp = array();
			// Separation of a and p 
				foreach($attendance as $key => $value)
				{
					if($value == 'a')
						$tmp['a'][] =  "'" . $key . "'";
					if($value == 'p')
						$tmp['p'][] = "'" . $key . "'";
				}


			// now updating all 'a' in db, it also saves us if person is not present at all
			if(is_array($tmp['a']))
				{
					$sql_a = implode(',' , $tmp['a']);
					$result = mysql_query("UPDATE attendance SET status='a' WHERE date IN ($sql_a) AND status = 'p' AND st_id = $uid");
					if($result)
						$reply = TRUE;
					else 
						$reply = FALSE;
				}
                                    /// time for P to db
			if(is_array($tmp['p']))
				{	
					$sql_p = implode(',' , $tmp['p']);
					$result = mysql_query("UPDATE attendance SET status='p' WHERE date IN ($sql_p) AND status = 'a' AND st_id = $uid");
					if($result)
						$reply = TRUE;
					else 
						$reply = FALSE;
				}
			}
		else
			$reply = FALSE;


		return $reply;

} 
?> 

 

I have tried to make all comments for explaining process, if any suggestion then let me know.

 

 

Also, as you suggested following case;

<input type="radio" name="ap['a']" value="123" />
<input type="radio" name="ap['p']" value="123" />

<input type="radio" name="ap['a']" value="456" />
<input type="radio" name="ap['p']" value="456" />

<input type="radio" name="ap['a']" value="789" />
<input type="radio" name="ap['p']" value="789" />

In above scenerio , radio button will not work, thy will act independently ie. you can select both absent and present because their name are different. So point of radio button fails completely here.