I have 4 tables (with a lot of fields, but only a few important ones in each) that I'm working with.
What I've been doing is getting a customer or item's id.
If it's a customer, I take their id to get their information from the customer table. Now, in order to get the item's they carry I have to look at each invoice with that customer's id. So I grab all of those. Each invoice has and id that links to an invoice detail table, so I take that invoice id and grab the invoice details that match with the invoice. Then the items are in the iteminventory table, so I take the item id from the invoice detail and get all of the items for every invoice associated with that customer. Then I run that data through a loop to get rid of any empty values and pass the item array to the view.
Now if it's an item the actions are performed vice versa. I take the item, look at each invoice detail containing that wine, grab the invoice id from it, and grab the customer id from each invoice respectively.
Somehow I wind up with customer's carrying a product, but when I go to the product to see what customer's carry it, the previously mentioned customer won't be listed under the product.
Is there an easier way to do this?
Most SQL programmers solve this kind of problem with a single query using a series of JOIN directives and an appropriate ORDER BY directive. They write a bit of php code to read the resultset row by row, detect rows in which the invoice id changes, and format the information appropriately.
It seems like a good idea for you to read up on how to use JOIN directives if you don't already know.