想要mysql和php的指导:)

I'm learning PHP and Mysql and i came up with a little sample project. In this project i can register an Invoice, i can add some items to it and at last but not least i can move those items into the correct store's storage. As far as i see now when i want to move the invoice_items to the storage table i have to make a query at every item to check the item is exists or not in that store and then UPDATE that row's quantity record or insert a new row.

This isn't seems to me a good solution. Isn't there any way that i could solve this with a single mysql query? And is it the proper db infrastructure for this task if not how could i improve it?

Any advice is greatly appreciated.

Invoice

  • ID (PRIMARY KEY)
  • Invoice number
  • store_id

Inovice_items

  • ID (PRIMARY KEY)
  • invoice_id
  • item
  • quantity

Storage

  • ID(PRIMARY KEY)
  • Item_id
  • quantity
  • store_id

Item

  • ID (PRIMARY KEY)
  • name

Store

  • ID (PRIMARY KEY)
  • name

Just do this in two queries.

  1. Update the counts for those that already exists
  2. Insert any new items

Query 1:

UPDATE
    s
SET
    s.quantity = s.quantity + item.quantity
FROM
    Storage s
    JOIN Invoice_item item ON s.item_id = item.item_id
WHERE
    item.invoice_id = 5 -- invoice to put into storage
    AND s.ID = 3 -- store to put the invoice items into

Query 2:

INSERT INTO
    Storage (item_id, quantity, store_id)
SELECT
    item.item_id
    ,item.quantity
    ,3 -- store to put the invoice items into
FROM
    Invoice_item item
WHERE
    item.invoice_id = 5
    AND NOT EXISTS (
        SELECT *
        FROM Storage s
        WHERE
            s.store_id = 3
            AND s.item_id = item.item_id
    )

As far as i see now when i want to move the invoice_items to the storage table i have to make a query at every item to check the item is exists or not in that store and then UPDATE that row's quantity record or insert a new row.

You might alter your table structure. Currently you have already added PRIMARY KEY to IDs, but you can also make use of UNIQUE. This will make an update query fail, if the data already exists in the db. In other words by setting a column unique, the database will do the exist check for you and you don't have to write a check-if-id-already-exists-then-update-or-insert() function in PHP.

You might also use AUTO_INCREMENT on your IDs. By doing that, the ID is incremented automatically, when you insert a new item.