INSERT AND UPDATE IF EXISTST
Heb nog een vraag over het feit als ik een INSERT doe in mijn tabel maar de waarde bestaat al.
Ik heb de volgende query:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?php
$sql .= "INSERT INTO ".$timesheet." (assignment_id,day,hours) VALUES ";
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00') && ($arraykey!='type' && $arraykey!='week_number')){
$sql .= "('".$assignment_id."','".$day."','".$arrayvalue."'),";
}
}
$sql = rtrim($sql, ",");
?>
$sql .= "INSERT INTO ".$timesheet." (assignment_id,day,hours) VALUES ";
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00') && ($arraykey!='type' && $arraykey!='week_number')){
$sql .= "('".$assignment_id."','".$day."','".$arrayvalue."'),";
}
}
$sql = rtrim($sql, ",");
?>
Deze query ziet er bijvoorbeeld zo uit:
INSERT INTO timesheet (assignment_id,day,hours) VALUES ('3','2009-07-27','8.00'),('3','2009-07-28','8.00'),('3','2009-07-29','8.00'),('3','2009-07-31','8.00'),('2','2009-07-29','6.00'),('2','2009-07-30','8.00'),('4','2009-07-30','8.00'),('8','2009-07-29','8.00')
De tabel heeft een index, genaamd timesheet_id. De waarden assignment_id en day zijn leidend. Als deze al bestaan, dan moet hij geupdate worden, niet vervangen, anders moet er een INSERT plaats vinden.
Nu had ik al zitten kijken op http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html, maar daar kom ik niet uit.
Heeft iemand een idee hoe ik dit het beste kan aanpakken?
Vast bedankt voor jullie hulp.
Groeten
Wim
Wat snap je dan nog niet van die insert-on-duplicate? Die werkt heel goed hoor.
Alleen niet bij meerdere inserts in 1 keer. Je zult dus je queries moeten splitsen. Steeds maar 1 waarde in de tabel wegschrijven. Het wordt dan dus:
INSERT INTO timesheet (assignment_id,day,hours) VALUES ('3','2009-07-27','8.00') ON DUPLICATE KEY UPDATE
day='2009-07-27', hours='8.00';
INSERT INTO timesheet (assignment_id,day,hours) VALUES ('2','2009-07-29','6.00') ON DUPLICATE KEY UPDATE
day='2009-07-29', hours='6.00';
Tnx voor je reactie. Aangezien een week 7 dagen heeft en er meer dan 1 project kan zijn zullen er dan 7 maal Xprojecten = heeft veel query's uitgevoerd moeten worden.
Ik heb het nu anders opgelost, waardoor ik voor de updates nog wel tig querys moet uitvoeren, maar voor de inserts niet meer. Ik heb namelijk de "name" attributen van de inputvelden een extra id meegegeven. Als die leeg is, dan is er sprake van een INSERT, anders van een UPDATE, zie code hieronder.
Als iemand nog een beter idee heeft hoor ik het graag.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<?php
[code]<?php
include('../../../../include/connection.inc.php');
$week_number = $_POST['week_number'];
$year = $_POST['year'];
// Example of name of POST and value and be
// POST : $_POST['8::2009-08-17::1']
// value: 8.00
// The POST name represents ['assignment_id::date_of_day::timesheet_id']
// The assignment_id and the date_of_day are used as keys
// The timesheet_id is the id of the row.
//
// If timesheet_id is not empty, then it means that we have to update the record
// else we have to insert a new record
// First check if there are more then 0 updates and more then 0 inserts
// Therefor set update to 0 and insert to 0 and start counting array
$update =0;
$insert =0;
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id!='' && ($arraykey!='year' && $arraykey!='week_number')){
$update +=1;
}
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id=='' && ($arraykey!='year' && $arraykey!='week_number')){
$insert +=1;
}
}
// If there are more then 0 results, only then the update may be executed
if($update >0){
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id!='' && ($arraykey!='year' && $arraykey!='week_number')){
$sql_upd= "UPDATE ".$timesheet."
SET hours='".$arrayvalue."'
WHERE assignment_id='".$assignment_id."'
AND day='".$day."'";
$res_upd = mysql_query($sql_upd,$con);
if (!$res_upd) die('Invalid query: ' . mysql_error());
}
}
}
// If there are more then 0 results, only then the insert may be executed
if($insert>0){
$sql_ins .= "INSERT INTO ".$timesheet." (assignment_id,day,hours) VALUES ";
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id=='' && ($arraykey!='year' && $arraykey!='week_number')){
$sql_ins .= "('".$assignment_id."','".$day."','".$arrayvalue."'),";
}
}
$sql_ins = rtrim($sql_ins, ",");
$res_ins = mysql_query($sql_ins,$con);
if (!$res_ins) die('Invalid query: ' . mysql_error());
}
// This is used to rebuild the complete calendar without refreshing page
// This is done with JQUERY function => "post_week_date(week,selected_month,go_to_add)"
echo $week_number."-".$year;
?>
?>
[code]<?php
include('../../../../include/connection.inc.php');
$week_number = $_POST['week_number'];
$year = $_POST['year'];
// Example of name of POST and value and be
// POST : $_POST['8::2009-08-17::1']
// value: 8.00
// The POST name represents ['assignment_id::date_of_day::timesheet_id']
// The assignment_id and the date_of_day are used as keys
// The timesheet_id is the id of the row.
//
// If timesheet_id is not empty, then it means that we have to update the record
// else we have to insert a new record
// First check if there are more then 0 updates and more then 0 inserts
// Therefor set update to 0 and insert to 0 and start counting array
$update =0;
$insert =0;
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id!='' && ($arraykey!='year' && $arraykey!='week_number')){
$update +=1;
}
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id=='' && ($arraykey!='year' && $arraykey!='week_number')){
$insert +=1;
}
}
// If there are more then 0 results, only then the update may be executed
if($update >0){
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id!='' && ($arraykey!='year' && $arraykey!='week_number')){
$sql_upd= "UPDATE ".$timesheet."
SET hours='".$arrayvalue."'
WHERE assignment_id='".$assignment_id."'
AND day='".$day."'";
$res_upd = mysql_query($sql_upd,$con);
if (!$res_upd) die('Invalid query: ' . mysql_error());
}
}
}
// If there are more then 0 results, only then the insert may be executed
if($insert>0){
$sql_ins .= "INSERT INTO ".$timesheet." (assignment_id,day,hours) VALUES ";
foreach($_POST as $arraykey=>$arrayvalue){
list($assignment_id,$day,$timesheet_id) = explode("::",$arraykey);
if(($arrayvalue!='' && $arrayvalue!='0.00')&& $timesheet_id=='' && ($arraykey!='year' && $arraykey!='week_number')){
$sql_ins .= "('".$assignment_id."','".$day."','".$arrayvalue."'),";
}
}
$sql_ins = rtrim($sql_ins, ",");
$res_ins = mysql_query($sql_ins,$con);
if (!$res_ins) die('Invalid query: ' . mysql_error());
}
// This is used to rebuild the complete calendar without refreshing page
// This is done with JQUERY function => "post_week_date(week,selected_month,go_to_add)"
echo $week_number."-".$year;
?>
?>
Gewijzigd op 01/01/1970 01:00:00 door Wim Selles