如果名字和姓氏的组合已经在Student表中，我希望在addStudent.php页面上输出一个错误。目前，它只是不向表中添加非唯一的条目，但不通知用户该条目已经存在。
表结构:<?php
include 'connect.php';
// sql to create table
$sql = "CREATE TABLE IF NOT EXISTS student (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(30),
lname VARCHAR(30),
mclass VARCHAR(30),
aclass VARCHAR(30),
UNIQUE (`fname`, `lname`)
)";
if ($conn->query($sql) === TRUE) {
} else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>
addStudent.php
<fieldset>
<legend><h2> Details </h2></legend>
<form class="pure-form">
<label>First Name </label><input type="text" id="firstname" name = "firstname" autofocus=""><br><br>
<label>Last Name </label><input type="text" id="lastname" name = "lastname"><br><br>
</form>
</fieldset>
<br>
<fieldset>
<legend><h2>Classes</h2></legend>
<form class="pure-form">
<label>Morning Class </label>
<select id = "morningclass" name="morningclass">
<option value=""> </option>
<option value="G1F">G1-F</option>
<option value="G1S">G1-S</option>
<option value="G2J">G2-J</option>
<option value="G2A">G2-A</option>
<option value="G3">G3</option>
<option value="G4">G4</option>
<option value="G5">G5</option>
</select>
<br> <br>
<label>Afternoon Class </label>
<select id = "afternoonclass" name = "afternoonclass">
<option value=""> </option>
<option value="7P">7P</option>
<option value="7H">7H</option>
<option value="8P">8P</option>
<option value="8H">8H</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
</select>
<br> <br>
</form>
</fieldset>
<br>
<div class="buttonAlign">
<input type="button" value="Cancel" onclick="cancel();" class="button-error pure-button">
<input type="button" value="Add" id="button" class="button-secondary pure-button">
</div>
<br>
</body>
</div >
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script>
$(document).ready(function(){
$("#button").click(function(){
var fname=$("#firstname").val();
var lname=$("#lastname").val();
var mclass=$("#morningclass").val();
var aclass=$("#afternoonclass").val();
if (fname !== "" && lname !== "" && mclass !== "" && aclass !== "") {
$.ajax({
type:"post",
url:"add.php",
data:"firstname="+fname+"&lastname="+lname+"&morningclass="+mclass+"&afternoonclass="+aclass,
success:function(){
alert("Entry added");
window.location.href = "viewStudent.php";
}
});
document.getElementById("firstname").value = "";
document.getElementById("lastname").value = "";
document.getElementById("morningclass").value = "";
document.getElementById("afternoonclass").value = "";
} else {
alert("You must fill out all the empty information!");
}
});
});
function cancel() {
window.location.href = "viewStudent.php";
}
</script>
. php
<?php
include 'connect.php';
$fname=preg_replace('/[^a-z]/', "", strtolower($_POST["firstname"]));
$lname=preg_replace('/[^a-z]/', "", strtolower($_POST["lastname"]));
$mclass=$_POST["morningclass"];
$aclass=$_POST["afternoonclass"];
$sql=("INSERT INTO student(fname,lname,mclass,aclass) values('$fname','$lname','$mclass','$aclass')");
if ($conn->query($sql) === TRUE) {
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
存储过程将在服务器上完成繁重的工作，而不是通过php分别调用select和update语句。使用这种方法，您不需要在不同的页面上发布多个帖子…
basic sql stored procedure
--------------------------
create procedure `spAddUser`(IN `param_forename` VARCHAR(64), IN `param_surname` VARCHAR(64) )
language sql
not deterministic
contains sql
sql security definer
comment ''
begin
declare dbrecords integer default 0;
declare strsql varchar(10000);
declare forename varchar(64);
declare surname varchar(64);
set @forename=cast(param_forename as char character set utf8);
set @surname=cast(param_surname as char character set utf8);
/* Does the user already exist? */
set @strsql=concat("select distinct count(*) into @dbrecords from `users` where `forename`=@forename and `surname`=@surname;");
prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
if( @dbrecords > 0 ) then
/* user exists, return statement that tells of problem */
set @strsql=concat("select 'sorry, that combination of firstname and lastname already exists' as 'result'");
prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
else
/* all good, add the record */
set @strsql=concat("insert into `users` set `forename`=@forename, `surname`=@surname;");
prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
set @strsql=concat("select 'Thankyou, your details have been added to the database' as 'result'");
prepare stmt from @strsql;
execute stmt;
deallocate prepare stmt;
end if;
end
php
---
$sql="call `spAddUser`('$forename','$surname')";
$db->query( $sql ); //etc
您的代码大部分都很好(除了SQL注入漏洞)。您所需要做的就是查看在尝试插入副本时返回的mysql错误信息，以确定您是否遇到了唯一约束违反，或者是否出现了其他错误。