Best way to put a single record from multiple tables on a single page
Best way to put a single record from multiple tables on a single page
enjoypb
Posts: 31Questions: 12Answers: 0
I have several tables that may or may not have unique information about a single business that has a unique ID (in all tables).
My goal is to allow users to view one of those individual tables and click a link that will take them to a Profile Page of that single business, which would then display any records found from the other DataTables on that single page.
Data may be found in 4-8 different (mySQL) tables that range in size of 1k to 100k records. I have it setup where users will be able to visit individual tables so they can look at that one facet against all their peers. I'm using both client-side and server-side processes to accomplish that.
My thinking is that I might have to merge all the tables into one and let users filter/search/find the business they want to focus on. That would be a massive database (I'm already at about 80 fields for 100k businesses in the core mySQL db), then theoretically I could load the whole record from that massive database.
It would be great to get an understanding of which direction I should point and I'd appreciate your thoughts and ideas. Thanks!
Answers
Any help with this would be great. Just need some direction. Thanks!
With a relational database, it does not matter how many tables, records, or fields there are.
Me, I'd make a class object. The class object would then load every single bit of information that I could drill out of every table and put that information then into elements and or other classes that logical for my use.
I would make a single SQL statement. If, because of left joins I get more than one records set on the left, I would check for the presence of the class (objects) already instantiated and then add children objects (class) as necessary.
The point, make only one data connection, make only one data request. Never load to a data base table object, always use a recordset object.
If you are asking how to load the data into dataTables, then I would write my own ajax page that returns data that is json formatted. I would iterate through my object I created previously to populate the json response. I would also save the object to a cache or session variable so I can access it quicker.
Ok cool that gives me some great direction. Thank you!