I’m trying to insert details that I got from my cart into an invoice, but I noticed that the problem was that the foreign key userid doesn’t sync with primary key userid with the account I log in to. I tried to take away the null from userid in cart but then I got an error. So I was wondering if someone could help me with syncing the userid so when I enter the isbn and add the quantity then cart will also get updated with the userid I logged into.
def registration():
fname = input("Enter your first name: ")
lname = input("Enter your last name: ")
address = input("Enter your address: ")
city = input("Enter your city: ")
zip = input("Enter your zip: ")
phone = input("Enter your phone number: ")
email = input("Enter your email: ")
password = input("Enter your passwords: ")
db = database()
mycursor = db.cursor()
sql = "INSERT INTO members (fname, lname, address, city, zip, phone, email, password) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
val = (fname, lname, address, city, zip, phone, email, password)
mycursor.execute(sql,val)
db.commit()
print("You have registered successfully!")
m = input("Press Enter to go back to Menu")
if m == "":
mycursor.close()
db.close()
def login():
email = input("Enter your email: ")
password = input("Enter your passwords: ")
db = database()
mycursor = db.cursor()
sql = "SELECT * FROM members WHERE email = %s AND password = %s"
val = (email, password)
mycursor.execute(sql,val)
user = mycursor.fetchone()
if user:
print("Login")
loginmenu()
return user[0]
else:
print("doesn't exist")
menu()
mycursor.close()
db.close()
mycursor.close()
db.close()
def subject():
db = database()
mycursor = db.cursor()
mycursor.execute("SELECT DISTINCT subject FROM books ORDER BY subject")
subjects = mycursor.fetchall()
print("nSubjects:")
for sub, subject in enumerate(subjects, start=1):
print(f"{sub}. {subject[0]}")
m = input("Enter your choice: ")
if m == "1":
m = "Action & Adventure"
elif m == "2":
m = "Arts, Film & Photography"
elif m == "3":
m = "Biographies, Diaries & True Accounts"
elif m == "4":
m = "Comics & Mangas"
elif m == "5":
m = "Computing, Internet & Digital Media"
elif m == "6":
m = "Crime, Thriller & Mystery"
elif m == "7":
m = "Humour"
elif m == "8":
m = "Language, Linguistics & Writing"
elif m == "9":
m = "Politics"
elif m == "10":
m = "Romance"
elif m == "11":
m = "Sports"
else:
print("Doesn't exist")
return
sql = "SELECT * FROM books WHERE subject = %s Limit 2"
val = (m,)
mycursor.execute(sql,val)
user = mycursor.fetchall()
for users in user:
print("nAuthor:",users[1])
print("Title:",users[2])
print("ISBN:",users[0])
print("Price:",users[3])
print("Subject:",users[4])
n = input("Enter ISBN to add to CART or n Enter to browse or ENTER to go back to menu: ")
if n == "n":
n = m
for sub, subject in enumerate(subjects, start=1):
print(f"{sub}. {subject[0]}")
elif n == " ":
menu()
else:
sql1 = "SELECT * FROM books WHERE isbn = %s"
val1 = (n,)
mycursor.execute(sql1,val1)
use = mycursor.fetchall()
if use:
z = input("Enter qty: ")
sql3 = "INSERT INTO cart (isbn,qty) VALUES (%s,%s)"
val3 = (userid,n,z)
mycursor.execute(sql3,val3)
db.commit()
menu()
else:
print("That's not a isbn")
mycursor.close()
db.close()
def Author():
db = database()
mycursor = db.cursor()
print("1. Author Search")
print("2. Title Search")
print("3. Go Back to Member Menu")
m = input("Enter your choice: ")
if m == "1":
n = input("Enter your choice: ")
sql = "SELECT * FROM books WHERE Author LIKE %s Limit 3"
val = (f"%{n}%",)
mycursor.execute(sql,val)
user = mycursor.fetchall()
for users in user:
print("nAuthor:",users[1])
print("Title:",users[2])
print("ISBN:",users[0])
print("Price:",users[3])
print("Subject:",users[4])
z = input("Enter ISBN to add to CART or n Enter to browse or ENTER to go back to menu: ")
if z == "n":
z = m
elif z == "":
menu()
else:
sql1 = "SELECT * FROM books WHERE isbn = %s"
val1 = (z,)
mycursor.execute(sql1,val1)
use = mycursor.fetchall()
if use:
b = input("Enter qty: ")
sql3 = "INSERT INTO cart (isbn,qty) VALUES (%s,%s)"
val3 = (z,b)
mycursor.execute(sql3,val3)
db.commit()
menu()
else:
print("That's not a isbn")
elif m == "2":
n = input("Enter your choice: ")
sql = "SELECT * FROM books WHERE Title LIKE %s Limit 3"
val = (f"%{n}%",)
mycursor.execute(sql,val)
user = mycursor.fetchall()
for users in user:
print("nAuthor:",users[1])
print("Title:",users[2])
print("ISBN:",users[0])
print("Price:",users[3])
print("Subject:",users[4])
z = input("Enter ISBN to add to CART or n Enter to browse or ENTER to go back to menu: ")
if z == "n":
z = m
elif z == "":
menu()
else:
sql1 = "SELECT * FROM books WHERE isbn = %s"
val1 = (z,)
mycursor.execute(sql1,val1)
use = mycursor.fetchall()
if use:
b = input("Enter qty: ")
sql3 = "INSERT INTO cart (isbn,qty) VALUES (%s,%s)"
val3 = (z,b)
mycursor.execute(sql3,val3)
db.commit()
menu()
else:
print("That's not a isbn")
elif m == "3":
menu()
mycursor.close()
db.close()
menu()
while True:
n = input("Type in your option: ")
if n == "1":
userid = login()
if userid is not None:
while True:
m = input("Enter:")
if m == "1":
subject()
elif m == "2":
Author()
elif m == "3":
check_out()
elif m == "4":
break
elif n == "2":
registration()
menu()
elif n == "q":
break
My MySql from mysql worksbench:
CREATE TABLE `members` (
`fname` varchar(50) NOT NULL,
`lname` varchar(50) NOT NULL,
`address` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`zip` int NOT NULL,
`phone` varchar(15) DEFAULT NULL,
`email` varchar(40) NOT NULL,
`userid` int NOT NULL AUTO_INCREMENT,
`password` varchar(200) NOT NULL,
PRIMARY KEY (`userid`)
)
CREATE TABLE `books` (
`isbn` char(10) NOT NULL,
`author` varchar(100) NOT NULL,
`title` varchar(200) NOT NULL,
`price` float NOT NULL,
`subject` varchar(200) NOT NULL,
PRIMARY KEY (`isbn`)
)
CREATE TABLE `cart` (
`userid` int DEFAULT NULL,
`isbn` char(10) DEFAULT NULL,
`qty` int DEFAULT NULL,
KEY `userid` (`userid`),
KEY `isbn` (`isbn`),
CONSTRAINT `cart_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `members` (`userid`),
CONSTRAINT `cart_ibfk_2` FOREIGN KEY (`isbn`) REFERENCES `books` (`isbn`)
)