Associating multiple MySQL queries w/ PHP?

Question

I am trying to create a simple inventory request system. A user can enter multiple SKU's that will query the inventory database.

My problem is I am trying to do is associate these multiple queries into a type of list. This list can later be retrieved and contains all queries that were submitted simultaneously. When the list is filled it can then be deleted.

Is this possible? I am just looking to be pointed in the right direction.

Thanks

Answer 1

Make a table like this:

ListNumber SKU
1          5423
1          5432
1          6542
1          6543
2          5411
3          6542
3          5411
3          5423

Then simply retrieve the SKU for list 1 by doing:

SELECT SKU FROM Table WHERE ListNumber=1;

Answer 2

You can use the (listNo,SKU,TimeStamp) table structure. Timestamp will help you analyse how and when your Inventory is getting queried and stuff.

Answer 3

If I understand the problem correctly, you are looking for a query to retrieve data when someone searches for multiple SKUs in a table. Is that correct? See query below if it is.

SELECT some_data FROM some_table WHERE sku IN ('SSDF3','GHG56',...'HG09');

Answer 4

I can somewhat see what you want here. You want to make sure that you have multiple inserts and somehow ensure that they have some sort of consistent ID associated with each transaction. If you take the timestamp approach, there's no guarantee that they will all be inserted in the same second.

It's very difficult to have rows inserted simultaneously in MySQL but I do have a suggestion that might work. I'm thinking that you should start a transaction, find the next highest ID number, and insert all of those rows afterwards.

1. $transRes = mysql_query("START TRANSACTION");
2. $nextListRes = mysql_query("SELECT max(listNumber) FROM InventoryRequests");
3. Fetch the result from $nextListRes and add 1.
4. Run INSERT queries for the SKUs using this incremented value.
5. $commitRes = mysql_query("COMMIT");

You want to make sure that this is a transaction and not two separate queries because race conditions may exist where two instances of your running PHP code may SELECT at the same time (a very bad thing!).

Also, I was thinking of a subquery way to do it, but that would make it such that each INSERT statement run will have a different MAX(listNumber) each time it's executed, meaning that they won't have the same ID as you would like. You might want to look into prepared statements for the INSERTs if you need them to be a bit faster, too :)

Answer 5

Not sure I'm understanding, but it sounds like you're attempting to build a shopping cart or a rudimentary order fulfillment system?

If that's the case, perhaps create another table that stores the "orders" and a comma separated list (similar to Sjoerd's answer, but you wouldn't need to worry about determining the list ID up front) of SKU's that can later be injected into an IN() clause (as per gsharma). The "orders" could be subsequently deleted, or marked as inactive or something like that.

Answer 6

You will need to associate the table with another table in the database, where it will be a Many to Many Table joining both the Inventory Table and the Type List based on Foreign Key matched to SKU.

YOU CAN QUERY THE FOLLOWING
SELECT inventory.* FROM inventory LEFT JOIN InvLinkedList ON InvLinkedList.SKU = Inventory.SKU WHERE InvLinkedList.typeID = 2

You will need to have Inventory Table with all the items, a InventoryType table with ID and Type as column and a MANY TO MANY connecting TABLE InvLinkedList with two indexed columns (SKU and TYPE ID). You will need to define relationship of these tables, where ON DELETE, it will CASCADE on type list. So that when ever you delete a type it will remove the rows with typeID in the InvLinkedList but will not remove the item in Inventory. The opposite will happen when you remove an item with SKU.

I hope this will be the solution. As for multiple type, you can create a PHP function to return the above mentioned query with a defined type.