我的数据库项目一直有问题。我使用XAMPP(3.2.1[编译:2013年5月7日]),并使用phpMyAdmin在localhost上运行数据库。我有64位Windows 8.1在华硕X555L上运行(如果这是有用的信息)。
我写了一个php脚本来加载数据库,但一直遇到问题。这是代码:
<html>
<head><title>Load KTCS Database</title></head>
<body>
<?php
/* Program: KTCS_load.php
* Desc: Creates and loads the KTCS database tables with
* sample data.
*/
$host = "localhost";
$user = "11cph2";
$password = "samplePassword";
$database = "ktcs";
$cxn = mysqli_connect($host,$user,$password, $database);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
die();
}
mysqli_query($cxn,"drop table Car;");
mysqli_query($cxn,"drop table Member;");
mysqli_query($cxn,"drop table old_member;");
mysqli_query($cxn,"drop table Reservations;");
mysqli_query($cxn,"drop table Pick_Up;");
mysqli_query($cxn,"drop table Rental_History;");
mysqli_query($cxn,"drop table Locations;");
mysqli_query($cxn,"drop table Fee;");
mysqli_query($cxn,"drop table Comment;");
mysqli_query($cxn,"drop table Reply;");
mysqli_query($cxn,"drop table Administrator;");
mysqli_query($cxn,"drop table Member_Password;");
mysqli_query($cxn,"drop table Admin_Password;");
mysqli_query($cxn,"CREATE TABLE Car(
VIN CHAR(10) NOT NULL,
make VARCHAR(15) NOT NULL,
model VARCHAR(15) NOT NULL,
year CHAR(4) NOT NULL,
type CHAR(3) NOT NULL,
LAddress VARCHAR(40) NOT NULL,
status CHAR(20) NOT NULL,
LastOMRead CHAR(6),
LastGasRead CHAR(2),
LastMaintOMRead CHAR(6),
LastMaintGasRead CHAR(2),
PRIMARY KEY(VIN));");
mysqli_query($cxn,"CREATE TABLE Member(
MNo CHAR(10) NOT NULL,
MName VARCHAR(15) NOT NULL,
MAddress VARCHAR(60) NOT NULL,
MPhoneNo CHAR(10),
MEmail VARCHAR(30),
DLicenseNo CHAR(15) NOT NULL,
CCardNo CHAR(16) NOT NULL,
CCardExpDate CHAR(5) NOT NULL,
RegAnnivDate DATE NOT NULL,
PRIMARY KEY(MNo));");
mysqli_query($cxn,"CREATE TABLE old_member(
MNo CHAR(10) NOT NULL,
MName VARCHAR(15) NOT NULL,
MAddress VARCHAR(40) NOT NULL,
MPhoneNo CHAR(11),
MEmail VARCHAR(30),
PRIMARY KEY(MNo));");
mysqli_query($cxn,"CREATE TABLE Reservations(
ResNo CHAR(10) NOT NULL,
MNo CHAR(10) NOT NULL,
VIN CHAR(10) NOT NULL,
ResDate DATE NOT NULL,
ResTime CHAR(5) NOT NULL,
PickUpAddress VARCHAR(40) NOT NULL,
PRIMARY KEY(ResNo),
FOREIGN KEY (MNo) REFERENCES Member(MNo),
FOREIGN KEY (VIN) REFERENCES Car (VIN));");
mysqli_query($cxn,"CREATE TABLE pick_up(
ResNo CHAR(10) NOT NULL,
MNo CHAR(10) NOT NULL,
VIN CHAR(10) NOT NULL,
ResDate DATE NOT NULL,
ResTime CHAR(5) NOT NULL,
PickUpDate DATE NOT NULL,
PickUpTime CHAR(5) NOT NULL,
PickUpAddress VARCHAR(40) NOT NULL,
PUpOMRead CHAR(6) NOT NULL,
PRIMARY KEY(ResNo),
FOREIGN KEY (MNo) REFERENCES Member(MNo),
FOREIGN KEY (VIN) REFERENCES Car (VIN));");
mysqli_query($cxn,"CREATE TABLE Rental_History(
ResNo CHAR(10) NOT NULL,
MNo CHAR(10) NOT NULL,
VIN CHAR(10) NOT NULL,
PickUpDate DATE NOT NULL,
PickUpTime CHAR(5) NOT NULL,
ReturnDate DATE NOT NULL,
ReturnTime CHAR(5) NOT NULL,
PickUpAddress VARCHAR(40) NOT NULL,
RetAddress VARCHAR(40) NOT NULL,
PUpOMRead CHAR(6) NOT NULL,
RetOMRead CHAR(6) NOT NULL,
PRIMARY KEY(ResNo),
FOREIGN KEY (MNo) REFERENCES Member(MNo),
FOREIGN KEY (VIN) REFERENCES Car (VIN));");
mysqli_query($cxn,"CREATE TABLE Locations(
LAddress VARCHAR(40) NOT NULL,
NoAvailSpaces INTEGER NOT NULL,
PRIMARY KEY(LAddress));");
mysqli_query($cxn,"CREATE TABLE Fee(
MNo CHAR(10) NOT NULL,
AnnualMemFee DECIMAL(5,2) NOT NULL,
UsageFee DECIMAL(5,2),
PRIMARY KEY (MNo),
FOREIGN KEY (MNo) REFERENCES Member(MNo));");
mysqli_query($cxn,"CREATE TABLE Comment(
MNo CHAR(10) NOT NULL,
Date DATE NOT NULL,
Time CHAR(5) NOT NULL,
Topic CHAR(7) NOT NULL,
Comment VARCHAR(300) NOT NULL,
Reply VARCHAR(300) NOT NULL,
PRIMARY KEY(MNo, Date, Time),
FOREIGN KEY (MNo) REFERENCES Member(MNo));");
mysqli_query($cxn,"CREATE TABLE Reply(
ENo CHAR(10) NOT NULL,
ReplyDate DATE NOT NULL,
ReplyTime CHAR(5) NOT NULL,
MNo CHAR(10) NOT NULL,
Date DATE NOT NULL,
Time CHAR(5) NOT NULL,
Reply VARCHAR(300) NOT NULL,
PRIMARY KEY(ENo, ReplyDate, ReplyTime, MNo, Date, Time),
FOREIGN KEY (MNo) REFERENCES Member(MNo),
FOREIGN KEY (ENo) REFERENCES Administrator(ENo));");
mysqli_query($cxn,"CREATE TABLE Administrator(
ENo CHAR(10) NOT NULL,
EName VARCHAR(15) NOT NULL,
EAddress VARCHAR(60) NOT NULL,
EPhoneNo CHAR(10) NOT NULL,
EEmail VARCHAR(30) NOT NULL,
SIN CHAR(9) NOT NULL,
Salary INTEGER NOT NULL,
PRIMARY KEY(ENo));");
mysqli_query($cxn,"CREATE TABLE Member_Password(
MNo CHAR(10) NOT NULL,
MPassword VARCHAR(20) NOT NULL,
PRIMARY KEY(MNo),
FOREIGN KEY (MNo) REFERENCES Member(MNo));");
mysqli_query($cxn,"CREATE TABLE Admin_Password(
ENo CHAR(10) NOT NULL,
EPassword VARCHAR(20) NOT NULL,
PRIMARY KEY(ENo),
FOREIGN KEY (ENo) REFERENCES Administrator(ENo));");
mysqli_query($cxn,"insert into Car values
('0000000050', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'in-use', '2015007', '54','185064', '66'),
('0000000051', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'in-use', '2015007', '54','185064', '66'),
('0000000052', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'available', '2015007', '54','185064', '66'),
('0000000053', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'out-for-maintenance', '2015007', '54','185064', '66'),
('0000000054', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'in-use', '2015007', '54','185064', '66'),
('0000000055', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'available', '2015007', '54','185064', '66'),
('0000000056', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'in-use', '2015007', '54','185064', '66'),
('0000000057', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'out-for-maintenance', '2015007', '54','185064', '66'),
('0000000058', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'in-use', '2015007', '54','185064', '66'),
('0000000059', 'Toyota', 'Corolla', 'Car', '2003', '240 Princess Street, Kingston', 'in-use', '2015007', '54','185064', '66'),
('0000000010', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'available', '079430', '33','022542', '27'),
('0000000011', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'available', '079430', '33','022542', '27'),
('0000000012', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'out-for-maintenance', '079430', '33','022542', '27'),
('0000000013', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'available', '079430', '33','022542', '27'),
('0000000014', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'out-for-maintenance', '079430', '33','022542', '27'),
('0000000015', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'available', '079430', '33','022542', '27'),
('0000000016', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'available', '079430', '33','022542', '27'),
('0000000017', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'in-use', '079430', '33','022542', '27'),
('0000000018', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'in-use', '079430', '33','022542', '27'),
('0000000019', 'Hyundai', 'Accent Sedan', 'Van', '2007', '427 king Street, Kingston, ON', 'available', '079430', '33','022542', '27'),
('0000000060', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'in-use', '071900', '12','065405', '50'),
('0000000061', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'available', '071900', '12','065405', '50'),
('0000000062', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'in-use', '071900', '12','065405', '50'),
('0000000063', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'in-use', '071900', '12','065405', '50'),
('0000000064', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'out-for-maintenance', '071900', '12','065405', '50'),
('0000000065', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'in-use', '071900', '12','065405', '50'),
('0000000066', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'available', '071900', '12','065405', '50'),
('0000000067', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'in-use', '071900', '12','065405', '50'),
('0000000068', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'in-use', '071900', '12','065405', '50'),
('0000000069', 'KIA', 'Sorento', 'Van', '2004', '240 Princess Street, Kingston', 'available', '071900', '12','065405', '50'),
('0000000030', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'out-for-maintenance', '160547', '27','120548', '38'),
('0000000031', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'available', '160547', '27','120548', '38'),
('0000000032', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'available', '160547', '27','120548', '38'),
('0000000033', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'in-use', '160547', '27','120548', '38'),
('0000000034', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'available', '160547', '27','120548', '38'),
('0000000035', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'available', '160547', '27','120548', '38'),
('0000000036', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'in-use', '160547', '27','120548', '38'),
('0000000037', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'in-use', '160547', '27','120548', '38'),
('0000000038', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'available', '160547', '27','120548', '38'),
('0000000039', 'Honda', 'Civic', 'Car', '2004', '746 Gardiners Road, Kingston, ON', 'available', '160547', '27','120548', '38');");
mysqli_query($cxn,"insert into Member values
('0000000001', 'Johnny Graph', '25 Collingwood Street, Kingston, ON', null, null, 'H1479681425432', '4560792363707025', '12/15', '2015-06-12'),
('0000000002', 'Brian Moore', '246 Albert Street, Kingston, ON', '6134829215', 'brianmoorecsqueensuca', 'F61374545461262', '3460602855077016', '03/17', '2015-10-06'),
('0000000003', 'Aaron Abbey', '38 Sydenham Street, Kingston, ON', null, null, 'E94384541504362', '7148061363696900', '09/18', '2015-03-27'),
('0000000004', 'Annie Wang', '1060 Woodbine Road, Kingston, ON', '6135579438', null, 'H14682024658751', '4590286617636329', '01/16', '2016-01-19');");
mysqli_query($cxn,"insert into Reservations values
('0000000020', '0000000001', '0000000037', '2015-06-10', '11:00', '240 Princess Street, Kingston, ON'),
('0000000068', '0000000002', '0000000050', '2015-04-05', '10:30', '185 Bath Road, Kingston, ON'),
('0000000076', '0000000001', '0000000036', '2015-04-09', '09:30', '427 king Street, Kingston, ON'),
('0000000098', '0000000004', '0000000057', '2015-05-14', '10:00', '240 Princess Street, Kingston, ON'),
('0000000104', '0000000004', '0000000013', '2015-08-23', '17:00', '185 Bath Road, Kingston, ON'),
('0000000130', '0000000001', '0000000065', '2015-07-06', '12:30', '240 Princess Street, Kingston, ON'),
('0000000147', '0000000003', '0000000055', '2015-04-17', '15:00', '240 Princess Street, Kingston, ON');");
mysqli_close($cxn);
echo "KTCS database created.";
?>
</body></html>
这是我的ER图的链接。很抱歉,如果有很多事情我错过了或逻辑错误。。我对它很陌生。
ER图
以下是我的问题列表:
有时我很难在表中添加值。
当我注释掉所有的"创建表"answers"添加值"代码(也就是只剩下"删除表")时,我不能一次删除所有表(需要运行两次脚本)。会不会是外键约束?
当整个数据库为空(没有表),并且只包含"删除表"answers"创建表"代码时,并不是所有表都会同时添加到数据库中。只有当我第二次运行脚本时,才会添加表"Reply"。为什么?
现在,我已经成功地将所有的值添加到数据库中,但在继续之前,我只想清除程序中的所有这些奇怪行为。我不知道是什么原因造成的,但可能和外键有关。
不一致的行为真的很令人沮丧,因为我花了很长时间检查数据类型和语法错误。很多时候,语法是完美的,但我只需要再运行几次脚本,就可以显示表,或者在尝试加载表之前删除所有表。
任何帮助都会很棒。
对于拖放和创建表,请更好地使用此
DROP TABLE IF EXIST
CREATE TABLE IF NOT EXIST