Item request system for my website

In my website, I want to add this ability for the users to request an item by submitting several conditions in a form in a specific page. And have items, when available in website(in my Table 1), in their profile.
I think I should have three tables :
Table 1: available_items
Table 2: users_profile_items
Table 3: users_requests
Table 1 includes available items(general website items), Table 2 will include the items selected from Table 1 whith Table 3 conditions and displays it’s content in users profile associated with user id.

What I want is, if there are new items(which means from time of request submission till then) available in table 1 of my database table with table 3 requested conditions, add the items to Table 2.
How do I do the action of inserting into Table 2 from Table 1 according Table 3 conditions by php and mysql?

or even, is my method an efficient way of doing this? (I myself think it’s a little complicated and not efficient method) if not, I appreciate your providing me with a better and more eficcient method and provide even a different db tables structure if needed.
I don’t want any complete code for the above, just an idea to code this, any tutorial etc, would be appreciated.
Sorry if it got a little broad.
Thanks in advance.

I think there are more efficient ways of doing what you want to do. Moving data around between tables, whilst possible, seems like it would be a bad approach. Let me talk through what I would do for an alternative approach to your way.

The problem (for my clarification):
You have items that you want to be able to give to users, and for users to request. When an item becomes available and it has been requested by a user, you then want to give it to that user.

This raises a couple of questions:

  1. Can there be multiples of individual items? i.e. Can an item be in stock by +5?
  2. Can multiple users request the same item?

Assuming the answer is yes to both of the above, then here’s what I’d do:

Database setup
Table: items
columns: {…, item_stock_level, …}
Table: user_profile_items
columns: {…, item_id, …}
Table: user_requests
columns: {…, item_id, queue, …}

The item_stock_level column in items will be the availability number for that item. If it is 0, then it is unavailable. That way, all items reside in one table, rather than having multiple tables containing the same available and unavailable items.
The item_id column in both user_profile_items and user_requests would simply reference an item in the items table.
The queue column in user_requests would be the request number in the user requests queue for a particular item.

When a user makes a new request for an item, you will be required to get the last number in the queue for that item (via a query like “SELECT MAX(queue) FROM table WHERE item_id = :item_id”), and then increment that number by 1, then insert the new request record with that new queue number. If you want to see who is next in the queue for an item, then you can simply use the previous query but with the MIN() aggregate function instead of MAX(). When a user’s request for an item has been fulfilled, simply remove their request form the user_requests table. With this method, you will also be able to see the number of times an item has been requested (to see the popularity of an item).

Now broadly speaking, there’s two ways you can implement the above to ensure that available items are given to the those users who have requested them. You can either setup up a task that is executed every X minutes/hours/days/etc (either as a cron job or a MySQL event), or you can use an eager update approach. The eager update approach means fulfilling any user requests for available items when ever you perform an action that has the potential to make an item available (such as adding a new item to the database, taking away an item from a user, or a user removing an item from their profile themselves).

If you can handle an item being available but not yet given out to a user for a little while, then the cron job/MySQL event option would be the simpler way to go. If, on other hand, you would like requests to be fulfilled as soon as items become available, then you’ll need to go for the eager update approach (which means you’ll need to be aware of every action that has the potential to make an item available, and then run the required module to fulfil user requests).

Those are just my general thoughts on this. If you’d like any clarification or think I’ve missed something, then please reply back!