In my project the employee inserts a table number, selects all the items the customer orders and saves that to a database. I have three tables:
Employee(empId, firstname, lastname)
Orders(orderId,tableNum,empIDFK,itemIDFK,totalPrice)
Item(itemId,itemName, itemPrice)
My problem is that if the employee puts more than one item in the order in only saves the last item in the itemIDFK column. How do I go about attaining the id of all the items that the employee entered?
Here is some example code, mostly all the buttons have code similar to this:
private void chickenbuttonActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
try{
st = connection.createStatement();
String query;
query = "SELECT itemName, itemPrice FROM item WHERE itemID = '14446'";
String itemName = " ",itemPrice =" ";
ResultSet rs = st.executeQuery(query);
if(rs != null){
while(rs.next())
{
itemName = rs.getString(1);
itemPrice = rs.getString(2);
}
model.addRow(new Object[]{itemName, itemPrice});
total+= Double.parseDouble(itemPrice);
String format = formatter.format(total);
totalField.setText(format);
}
//inserts corresponding item id in itemIDFK
String query2 = "Update orders SET itemIDFK = '14446' Where tableNum = " + tableNum;
ps= connection.prepareStatement(query2);
ps.executeUpdate();
} catch (SQLException ex) {}
What you want to do in this case is add another table, say OrderDetails
, and this table would have:
PK: Id
FK: OrderId
FK: ItemId
So then you can add multiple items to an order. There would be a one-to-many
between Order
and OrderDetails
and many-to-many
between OrderDetails
and Items