MNM MNM - 2 months ago 12
SQL Question

How to assign an item to a customer in mysql?

I have a table that basically holds the a connection between two tables. One table is a list of customers and the other table is a list of items that I want to assign to customers.
What I need to do here is assign a item to a customer based on both there id numbers. So the customer has a customerID which I use to identify them. And each item has a id number that I want to use to identify the item.

So my question is how do I assign different coupons to each person.
I am thinking of just either going though each customer and assigning them all the items i want them to have. And do this by each coupon. But how do I do that. I want to get the information from two different tables and combine them into one single table.

Any help on this would be appreciated.


I think you are talking about M2M relationship of 2 tables.

M2M : Many to Many.

for that you should have data table structures Customers (customerId, other fields here) Coupons (couponId, other fields here)

create a new table Coupons_to_Customers (customerId, couponId)

for interface, you need to make a form based on check boxes.

suppose, you are going to assign customers for a coupon.

Then show all customers with check boxes.

On submit, remove all records against couponID, and insert all selected CustomerId with that one couponID.

I hope it will resolve your issue.