How to store multiple record in Mysql variable?

MySQL Database Relational Correlation help?

• I'm not sure if that's even what it's called so I think it's best to explain my question with a hypothetical: Let's say I wanted to make a database of shopping malls, the stores in each and what each store sells. So, 1) Shopping Mall A would have Stores A, B, C and D; etc, 2) Stores A and C sells items 1, 2, 3, and 4, etc. 3) Items 1, 2, are electronics, or some other item type Now, I created a table with a list of Shopping Malls. In this table there are fields like Year Built, SQFT, Location, Number of floors, and Stores. I also created a table with a List of Stores and a Table with a list of Items; each with their respective fields. Ok, Question #1: Using MySQL, how can you link or how can 1 record from 1 table (Shopping Mall) be linked to multiple records in another table (Stores)? And Question #2*: By using a web application to input new shopping malls, is there a way to simultaneously input stores and by extension items? *Example - In the Add Shopping Mall PHP form, there is a menu (list or dropdown - remember this is all hypothetical) with a list of all the stores in the Stores table. I would like for the user to click any and all of the Stores that exist in the Shopping Mall being added. If the user does not see the store they may elect to add a new one - which would then take them to the Add Store PHP form which may then take them to the Add Item PHP form. Then the Web App returns them to the Add Shopping Mall PHP form where they would complete the remainder of the form. You don't have to answer the question specifically, I'll be happy with a link to direct me to the information I need. Thanks.

• Answer:

  Typically any multi-way association is easiest with an inbetween table to store the associations. Assuming stores all have a unique ID number, and all Items have a unique ID number, then you have a 3rd table with just StoreID#|ItemID# pairs. [StoreA|Item1] [StoreA|Item2] [StoreA|Item3] [StoreC|Item1] [StoreC|Item2] [... Since there will be repeat values, only both values combined make a tangible key. You also don't want anything else in there is there will potentially be many records. But then you can use outer joins etc to pull what you need thru all 3 tables. I can't write a whole book on Answers, but maybe that'll get you started? Your second question may answer itself as you build the underlying structure. It's merely a preference as to how much user-convenience you can provide conveniently from what you have to work with. ;)