"duplicate key"-error while inserting into db
I have this page where user enters data to the database, the data are inserted to the database just fine, I'm still having problems with the update query though... it keeps giving me the following error message when I click submit:
INSERT INTO ongoing (project_no, project_title, duration, manager, country, total_fee, performed, remaining, gross_profit, gp, performance_year, gp_year) VALUES('ff', 'ffd', '', 'dd', 'f','f','f','d', 'gg','', '','')
Warning: pg_query() [function.pg-query]: Query failed: ERROR: duplicate key value violates unique constraint "ongoing_pkey" in C:\ms4w\Apache\htdocs\project\editongoing.php on line 64
Error in SQL query: ERROR: duplicate key value violates unique constraint "ongoing_pkey"
Here is my code:
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<title>Edit Ongoing projects</title>
</head>
<body bgcolor="666657">
<?php
// attempt a connection
$connection = pg_connect("host=localhost dbname=Project_list user=admin password=Password1");
if (!$connection) {
die("Error in connection: " . pg_last_error());
}
if ($_REQUEST['ProjectID']!=''){
$QueryStr = "Select * from ongoing where project_no='".$_REQUEST['ProjectID']."'";
$result = pg_query($connection, $QueryStr);
if (!$result) {
die("Error in SQL query: " . pg_last_error());
}
$row = pg_fetch_row($result);
}
if ($_POST['action']=='edit'){
$update=1;
}else {
$update = 0;
}
if ($_POST['submit']) {
// escape strings in input data
$project_no = pg_escape_string($_POST['project_no']);
$title = pg_escape_string($_POST['title']);
$duration = pg_escape_string($_POST['duration']);
$manager = pg_escape_string($_POST['manager']);
$country = pg_escape_string($_POST['country']);
$total_fee = pg_escape_string($_POST['total_fee']);
$performed = pg_escape_string($_POST['performed']);
$remaining = pg_escape_string($_POST['remaining']);
$gross_profit = pg_escape_string($_POST['gross_profit']);
$gp = pg_escape_string($_POST['gp']);
$performance_year = pg_escape_string($_POST['performance_year']);
$gp_year = pg_escape_string($_POST['gp_year']);
if ($update==0){
// execute query
$sql = "INSERT INTO ongoing (project_no, project_title, duration, manager, country, total_fee,
performed, remaining, gross_profit, gp, performance_year, gp_year)
VALUES('$project_no', '$title', '$duration', '$manager', '$country','$total_fee','$performed','$remaining',
'$gross_profit','$gp', '$performance_year','$gp_year')";
} else {
$sql="UPDATE ongoing SET project_no='".pg_escape_string($_POST['project_no'])."' , project_title='".pg_escape_string($_POST['title'])."',duration='".pg_escape_string($_POST['duration']).
",manager='".pg_escape_string($_POST['manager'])."',country='".pg_escape_string($_POST['country'])."', total_fee=".pg_escape_string($_POST['total_fee']).
" ,performed=".pg_escape_string($_POST['performed']).", remaining=".pg_escape_string($_POST['remaining']).", gross_profit=".pg_escape_string($_POST['gross_profit']).
" , gp='".pg_escape_string($_POST['gp'])."' , performance_year=". pg_escape_string($_POST['performance_year']).", gp_year=". pg_escape_string($_POST['gp_year'])."";
}
print_r($sql);
$result = pg_query($connection, $sql);
if (!$result) {
die("Error in SQL query: " . pg_last_error());
}
echo "Data successfully inserted!";
// free memory
pg_free_result($result);
// close connection
pg_close($connection);
}
?>
<form action= "<?php echo $_SERVER['PHP_SELF']; ?>" method="post"><b><font color = "#ffb200">
Project No.: <br> <input id="project_no" type="text" name="project_no" size="20" value=<?= $row[0] ?>>
<p>
Project Title: <br> <input id="title" type="te开发者_如何学Cxt" name="title" value=<?= $row[1] ?>>
<p>
Duration: <br> <input ID="duration" type="text" name="duration" value=<?= $row[2]?>>
<p>
Project Manager: <br> <input ID="manager" type="text" name="manager" value=<?= $row[3] ?>>
<p>
Country: <br> <input ID="country" type="text" name="country" value=<?= $row[4] ?>>
<p>
Total Fee: <br> <input ID="total_fee" type="text" name="total_fee" value=<?= $row[5] ?>>
<p>
Already performed: <br> <input ID="performed" type="text" name="performed" value=<?= $row[6] ?>>
<p>
Remaining performance: <br> <input ID="remaining" type="text" name="remaining" value=<?= $row[7] ?>>
<p>
Gross Profit: <br> <input ID="gross_profit" type="text" name="gross_profit" value=<?= $row[8] ?>>
<p>
GP%: <br> <input ID="gp" type="text" name="gp" value=<?= $row[9] ?>>
<p>
Performance actual year: <br> <input ID="performance_year" type="text" name="performance_year" value=<?= $row[10] ?>>
<p>
GP actual year: <br> <input ID="gp_year" type="text" name="gp_year" value=<?= $row[11] ?>>
<p>
<input type=text name=action value=<?=$_REQUEST['action'] ?>><br>
<input type="submit" name="submit" value="Sumbit my table" size="30">
<P>
<a href="ongoing.php"</a>View ongoing projects</a>
<a href="editproject.php"</a>Back to editing menu</a>
</form>
</body>
</html>
here is the schema
CREATE TABLE ongoing (
project_no character varying(80) NOT NULL,
project_title character varying(80),
duration character varying(20),
manager character varying(80),
country character varying(80),
totalfee integer,
performed integer,
remaining integer,
gross_profit integer,
gp character varying(20),
performance_year integer,
gp_year integer,
lastmodified timestamp without time zone DEFAULT now(),
"Active" character varying(20)
);
ALTER TABLE public.ongoing OWNER TO postgres;
COPY ongoing (project_no, project_title, duration, manager, country, totalfee, performed, remaining, gross_profit, gp, performance_year, gp_year, lastmodified, "Active") FROM stdin;
K90.6849.001.412090 Water Supply and Sanitat Queena \N \N \N \N \N \N \N \N \N \N 2011-05-22 13:01:08.593 \N
\.
ALTER TABLE ONLY ongoing
ADD CONSTRAINT ongoing_pkey PRIMARY KEY (project_no);
CREATE TRIGGER update_lastmodified_modtime
BEFORE UPDATE ON ongoing
FOR EACH ROW
EXECUTE PROCEDURE update_lastmodified_column();
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
You are updating your primary key to one value throughout the table.
you shouldn't update project_no, but add a where clause e.g
WHERE project_no = pg_escape_string($_POST['project_no']);
Make sure which condition is fired, place an echo 'this is if'; in if block so you can now After you make sure which condition is true if or else the focus only in sql and leave out all php code, this means:
echo the sql and test it directly from client. Finally what Sorrow said makes sense for me too.
加载中,请稍侯......
精彩评论