I have a table in which there are many fields that are foreign keys that reference a linked table. I am writing a script in PHP that will execute db requests. When I query this table for my data, I need to know the values associated with these keys, not the key.
How is this done for most people?
. A 101 way to do this is to query this table for your data, including foreign keys, and then query related tables to get each key value. It can be many requests (~ 10).
Question 1: I think I could write 1 query with a bunch of joins. Would be better?
This approach also requires the script request to know which fields in the table are foreign keys. Since I have many tables like this, but all with different fields, this means that writing good general functions is difficult. MySQL InnoDB tables allow external constraints. I know that the database is configured correctly.
Question 2: . Regarding the idea of querying the table and determining which constraints exist, then matching them using any process that I solve in question 1. I like this idea, but I never see it used in the code. For some reason it seems to me that this is not a good idea. I would use something like SHOW CREATE TABLE tbl_name; to find what constraints / relationships exist for this table.
Thanks for any suggestions or tips.