(Code support) Add item to shop & get shop items

[boionfire81]

I'm modifiying the shop items as with over 300 items and close to 200 locations the single line insert will be overwhelming on the server.

 

I've decided to use an array in a new shops column called shopITEMS.  I'd like to create an array like this:

 

itemid => price

 

problem here is I don't know how to store it with each new insert and pull it from the database to display each on their own line in the shop page.

 

Any help appreciated.

[gamble]

Seems weird the way you're doing it, I'll suggest a different way then answer your question. 

 

Why not add a new table called shopITEMS or whatever and then have the columns: id, itemID, shopID, price

 

Then add from there and modify as needed. Then you can use a join statement or do a multi query solution to link the shops table and shop items table.

 

For making your way work:

 

You could use json_encode and json_decode to stringify an object of itemID=>price. Then when you need to append to it or remove from it just pull the field, json_decode it and append to it or remove as needed

[Dayo]

for a database even if each shop location had 300 items in it that is only 60,000 records, this is next to nothing for modern machines (and even old ones). Just for context i have MySQL databases at work that are over 1TB in size with certain tables having billions of records.

If planned correctly doing it in MySQL will be much better to maintain going forward.

Have a table for your items

Have a table for your shops

Have a table to link items to shops

The query to select shop items would be like

SELECT * FROM shopItems
INNER JOIN items on shopItems.item = items.id
WHERE shopItems.shop = :shopID

shopItems could even have different selling price if you structured it like this

shopItems
---------
id 
shop
item
sellPrice
buyPrice
maxStock
restockInterval