我有一个页面，用户在其中向数据库输入数据，数据插入到数据库中很好，但我在更新查询方面仍然有问题。。。当我点击提交时，它一直给我以下错误消息：
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"
这是我的代码：
<!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="text" 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>
这是模式
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;
您正在将主键更新为整个表中的一个值。
你不应该更新project_no，而是添加一个where子句，例如
WHERE project_no = pg_escape_string($_POST['project_no']);
确定激发了哪个条件，放置一个echo"this is if"；在if块中，所以您现在可以在您确定哪一个条件为true之后，if或else只关注sql并省略所有php代码，这意味着：
回显sql并直接从客户端进行测试。最后，Sorrow说的话对我来说也很有道理。