I keep on getting this error: "Error deleting table: Cannot delete or update a parent row: a foreign key constraint failsError creating table: Table 'tbl_Customer' already exists" when I try and run this script:
<?php
include "DBConn.php";
mysqli_select_db($conn, $dbname);
//Code to see if Table Exists
$exists = mysqli_query($conn, "select * from tbl_Invoice");
if($exists !== FALSE) {
//Drop table before re-creating from text file
$sql = "DROP TABLE tbl_Invoice;";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error deleting table: " . $conn->error;
}
//Creating table in database
$sql = "create table tbl_Invoice(
InvoiceID int NOT NULL AUTO_INCREMENT,
Date date not null,
TotalCost decimal(15,2) not null,
Address varchar(30) not null,
PRIMARY KEY(InvoiceID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('invoiceData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($Date,$TotalCost,$Address) = $explodeLine;
$qry = "insert into tbl_Invoice (Date, TotalCost, Address) values('".$Date."','".$TotalCost."','".$Address."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
else {
//Creating table in database
$sql = "create table tbl_Invoice(
InvoiceID int NOT NULL AUTO_INCREMENT,
Date date not null,
TotalCost decimal(15,2) not null,
Address varchar(30) not null,
PRIMARY KEY(InvoiceID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('invoiceData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($Date,$TotalCost,$Address) = $explodeLine;
$qry = "insert into tbl_Invoice (Date, TotalCost, Address) values('".$Date."','".$TotalCost."','".$Address."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
//Code to see if Table Exists
$exists = mysqli_query($conn, "select * from tbl_Customer");
if($exists !== FALSE) {
//Drop table before re-creating from text file
$sql = "DROP TABLE tbl_Customer;";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error deleting table: " . $conn->error;
}
//Creating table in database
$sql = "create table tbl_Customer(
CustomerID int NOT NULL AUTO_INCREMENT,
Name varchar(30) not null,
Surname varchar(30) not null,
Email varchar(30) not null,
Password varchar(50) not null,
PRIMARY KEY(CustomerID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('userData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($FName,$LName,$Email,$Password) = $explodeLine;
$qry = "insert into tbl_Customer (Name, Surname, Email, Password) values('".$FName."','".$LName."','".$Email."','".$Password."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
else {
//Creating table in database
$sql = "create table tbl_Customer(
CustomerID int NOT NULL AUTO_INCREMENT,
Name varchar(30) not null,
Surname varchar(30) not null,
Email varchar(30) not null,
Password varchar(50) not null,
PRIMARY KEY(CustomerID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('userData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($FName,$LName,$Email,$Password) = $explodeLine;
$qry = "insert into tbl_Customer (Name, Surname, Email, Password) values('".$FName."','".$LName."','".$Email."','".$Password."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
//Code to see if Table Exists
$exists = mysqli_query($conn, "select * from tbl_Order");
if($exists !== FALSE) {
//Drop table before re-creating from text file
$sql = "DROP TABLE tbl_Order;";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error deleting table: " . $conn->error;
}
//Creating table in database
$sql = "create table tbl_Order(
OrderID int NOT NULL AUTO_INCREMENT,
CustomerID int not null,
InvoiceID int not null,
PRIMARY KEY(OrderID),
FOREIGN KEY(CustomerID) REFERENCES tbl_Customer(CustomerID) ON DELETE CASCADE,
FOREIGN KEY(InvoiceID) REFERENCES tbl_Invoice(InvoiceID) ON DELETE CASCADE
)";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('orderData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($CustomerID,$InvoiceID) = $explodeLine;
$qry = "insert into tbl_Order (CustomerID, InvoiceID) values('".$CustomerID."','".$InvoiceID."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
else {
//Creating table in database
$sql = "create table tbl_Order(
OrderID int NOT NULL AUTO_INCREMENT,
CustomerID int not null,
InvoiceID int not null,
PRIMARY KEY(OrderID),
FOREIGN KEY(CustomerID) REFERENCES tbl_Customer(CustomerID) ON DELETE CASCADE,
FOREIGN KEY(InvoiceID) REFERENCES tbl_Invoice(InvoiceID) ON DELETE CASCADE
)";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('orderData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($CustomerID,$InvoiceID) = $explodeLine;
$qry = "insert into tbl_Order (CustomerID, InvoiceID) values('".$CustomerID."','".$InvoiceID."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
//Code to see if Table Exists
$exists = mysqli_query($conn, "select * from tbl_Item");
if($exists !== FALSE) {
//Drop table before re-creating from text file
$sql = "DROP TABLE tbl_Item;";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error deleting table: " . $conn->error;
}
//Creating table in database
$sql = "CREATE TABLE tbl_Item (
ItemID varchar(10) NOT NULL,
Description varchar(30) NOT NULL,
Cost_Price decimal(15,2) NOT NULL,
Quantity int(10) NOT NULL,
Sell_Price decimal(15,2) NOT NULL,
PRIMARY KEY (ItemID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('itemData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($ItemID,$Description,$Cost_Price,$Quantity,$Sell_Price) = $explodeLine;
$qry = "insert into tbl_Item (ItemID, Description, Cost_Price, Quantity, Sell_Price) values('".$ItemID."','".$Description."','".$Cost_Price."','".$Quantity."','".$Sell_Price."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
else {
//Creating table in database
$sql = "CREATE TABLE tbl_Item (
ItemID varchar(10) NOT NULL,
Description varchar(30) NOT NULL,
Cost_Price decimal(15,2) NOT NULL,
Quantity int(10) NOT NULL,
Sell_Price decimal(15,2) NOT NULL,
PRIMARY KEY (ItemID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('itemData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($ItemID,$Description,$Cost_Price,$Quantity,$Sell_Price) = $explodeLine;
$qry = "insert into tbl_Item (ItemID, Description, Cost_Price, Quantity, Sell_Price) values('".$ItemID."','".$Description."','".$Cost_Price."','".$Quantity."','".$Sell_Price."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
//Code to see if Table Exists
$exists = mysqli_query($conn, "select * from tbl_CustAddress");
if($exists !== FALSE) {
//Drop table before re-creating from text file
$sql = "DROP TABLE tbl_CustAddress;";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error deleting table: " . $conn->error;
}
//Creating table in database
$sql = "create table tbl_CustAddress(
AddressID int not null AUTO_INCREMENT,
CustomerID int not null,
Address varchar(40) not null,
PRIMARY KEY(AddressID),
FOREIGN KEY(CustomerID) REFERENCES tbl_Customer(CustomerID) ON DELETE CASCADE
)";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('addressData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($CustomerID,$Address) = $explodeLine;
$qry = "insert into tbl_CustAddress (CustomerID, Address) values('".$CustomerID."','".$Address."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
else {
//Creating table in database
$sql = "create table tbl_CustAddress(
AddressID int not null AUTO_INCREMENT,
CustomerID int not null,
Address varchar(40) not null,
PRIMARY KEY(AddressID),
FOREIGN KEY(CustomerID) REFERENCES tbl_Customer(CustomerID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('addressData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($CustomerID,$Address) = $explodeLine;
$qry = "insert into tbl_CustAddress (CustomerID, Address) values('".$CustomerID."','".$Address."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
//Code to see if Table Exists
$exists = mysqli_query($conn, "select * from tbl_OrderItem");
if($exists !== FALSE) {
//Drop table before re-creating from text file
$sql = "DROP TABLE tbl_OrderItem;";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error deleting table: " . $conn->error;
}
//Creating table in database
$sql = "create table tbl_OrderItem(
OrderItemID int not null AUTO_INCREMENT,
OrderID int not null,
ItemID varchar(10) not null,
Quantity int not null,
Sell_Price decimal(15,2) not null,
PRIMARY KEY(OrderItemID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
}
else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('orderItemData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($OrderID,$ItemID,$Quantity,$Sell_Price) = $explodeLine;
$qry = "insert into tbl_OrderItem (OrderID, ItemID, Quantity, Sell_Price) values('".$OrderID."','".$ItemID."','".$Quantity."','".$Sell_Price."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
else {
//Creating table in database
$sql = "create table tbl_OrderItem(
OrderItemID int not null AUTO_INCREMENT,
OrderID int not null,
ItemID varchar(10) not null,
Quantity int not null,
Sell_Price decimal(15,2) not null,
PRIMARY KEY(OrderItemID)
)";
if ($conn->query($sql) === TRUE) {
echo "";
} else {
echo "Error creating table: " . $conn->error;
}
//Zameer, A., 2017. PHP read text file and insert into MySQL database. [online] WDB24. Available at: <https://www.wdb24.com/php-read-text-file-and-insert-into-mysql-database/> [Accessed 2 May 2021].
$open = fopen('orderItemData.txt','r');
while (!feof($open))
{
$getTextLine = fgets($open);
$explodeLine = explode(",",$getTextLine);
list($OrderID,$ItemID,$Quantity,$Sell_Price) = $explodeLine;
$qry = "insert into tbl_OrderItem (OrderID, ItemID, Quantity, Sell_Price) values('".$OrderID."','".$ItemID."','".$Quantity."','".$Sell_Price."')";
mysqli_query($conn,$qry);
}
fclose($open);
}
?>
As you can see, It's pretty much the same code just repeated for six tables. The code drops the table and recreates it inside the phpmyadmin test database. Most of the tables work except for a few. I think this might have something to with my ERD and the way I've structured my database. I think I've done it incorrectly. Here is my ERD:
TIA for any help!
source https://stackoverflow.com/questions/67909805/keep-on-getting-foreign-key-errors-when-trying-to-drop-my-tables-in-database
Hello, my name is Muhammad Athar. I'm a 17 year old student. I'm a Web Developer, Designer and Freelancer.
No comments:
Post a Comment