I am trying to puzzle out my first "more complicatedInch SQL application and am getting a little of the difficult time conceptualizing the easiest method to start something simple. I realize the code, I believe, around various concepts but need to know the easiest way you believe is the best way to proceed, and most importantly what terms/how you can refer to this as method, in order to find more information onto it myself without needlessly disturbing people!
I'm creating a Wordpress wordpress plugin which will let someone add a variety of boxes to a variety of "transactions". Essentially, transaction 1 might have box 3 boxes known as apples, fruits, organes. Transaction 2 might have 10 boxes. And so forth. I would like a method to show this towards the consumer in the easiest way. When just beginning, my (bad, I am sure) instinct would be to have one key known as, say, transactions, the industry comma separated list "1,2,3,4,5". Then, have another table in which the index is 1,2,3 as well as for all these secrets i've the different boxes. I grab the very first CSV list in the db, "explode" it for an array and cycle through, getting the secrets in the second table that be affected by it #. However in the 2nd table, does each "key" indicate other secrets? This is when i recieve confused. Is the easiest method to proceed? What type of code would I have to search for? EDIT: This is a visual picture of what I am searching for:
Transaction 1: #12878321: Apples, Oranges, Pears Transaction 2: #11239089: Berries, Oranges, Apples Transaction 3: #89792834: Spaghetti, Berries, Oranges, Pears, Apples
I must display around the page the data much like that. So I have to keep transaction number, the random code, AND "that which was bought" (the boxes during my example above) all towards the consumer.
I have already done card inserts, updates, etc on a single table but never connected (correct word?) two tables and do not determine if thats the best way to get it done, to produce another Wordpress table.
I am sorry if this sounds like an unusual and lengthy question, but one of the 1,000 of lessons on the internet, its confusing which to follow along with because I am unfamiliar with the terms themselves!
Unsure if I am understanding your description from the data, however it does seem like it would be a many-many relationship (many transactions, each with lots of related boxes).
The fundamental table setup for something of that nature could be:
transactions: userID - who the transaction belongs to transactionID - unique id for the transaction itself boxes: transactionID - which transaction does this box belong to name - oranges, apples, etc...
You do not mention if these boxes contain a number of other things, however it'd you need to be a 3rd table that lists exactly what the items in each box is.
What you would like is (I believe) known as a "many to a lot ofInch relationship. These are typically best handled by a connection table the association table simply has two (relevant) posts the ID of the record in a single table, and also the ID of the record inside a second table. This way, you may create a SQL statement which will find for you personally all the "boxes" (records in table 2) which exist for any given "transaction" (records in table 1) you may also produce a query that will help you to find all the "transactions" (records in table 1) that make reference to confirmed "box" records in table 2.
With this, you'll need three tables:
Transactions ID, Whatever else you keep for transactions Boxes ID, Contents TransactionBoxes TransactionID, BoxID