2

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?

enter image description here

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) {}
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
JudgementFlame
  • 107
  • 1
  • 2
  • 9

3 Answers3

5

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

SOfanatic
  • 5,523
  • 5
  • 36
  • 57
  • Would there be a way to specify the one-to-many and many-to-many relationships in MySql? What would each of the foreign keys be referencing? – JudgementFlame Apr 28 '13 at 21:49
  • Take a look at this post, it shows a detail example on how to create table relationships. http://stackoverflow.com/questions/260441/how-to-create-relationships-in-mysql – SOfanatic May 01 '13 at 01:29
2

SOfanatic's answer is the correct way to do this. If for some reason you can't add another table, then itemIDFK will need to hold a delimited list of items such as itemId;itemId;itemId (or itemiditemIditemId if the ids are fixed width) - the problem being that this is much more difficult to query

Zim-Zam O'Pootertoot
  • 17,888
  • 4
  • 41
  • 69
0

MySQL does have one data type Serial. So, there is another way to implement the order with multiple items, and item with different quantity.

It is possible to implement an Arraylist<>, serialize and unserialize the data in and out of a single column.

Xubo Zhao
  • 1