I have a MySQL database that consists of a master table with records consisting of a mix of actual data values in columns and columns with pointers to lookup tables very similar to the following:

Database
mastertable
col1 primary key
col2 text data
col3 primary key value of record in lookuptable1
col4 primary key value of record in lookuptable2
lookuptable1
col1 primary key
col2 text data
lookuptable2
col1 primary key
col2 text data

Believe it or not I actually managed to successfully get the lookup tables to display in dropdown lists for the input of data in to the master table.

What I do need help with is how to extract the text data from each lookup table so that the resulting web page displays master table using the text data and not the numeric value of the associated primary key. I will be formatting the output to look like the following:

mastertable
record1 | mastertable col2 | lookuptable1 col2 | lookuptable2 col2
record2 | mastertable col2 | lookuptable1 col2 | lookuptable2 col2
...........
recordn | mastertable col2 | lookuptable1 col2 | lookuptable2 col2

The numeric values in the master table I can get. The asociated text value in the lookup tables I cannot.

Thanks in advance for any help offered.

    You can do this in your query--through prudent use of joins-
    something along the lines of:

    select mastertable.col1,mastertable.col2,lookuptable1.col2,lookuptable2.col2
from mastertable,lookuptable1,lookuptable2 where
mastertable.col3=lookuptable1.col1
and mastertable.col4=lookuptable2.col1

    Hope this helps.

      maxpup979 wrote:

      You can do this in your query--through prudent use of joins-
      something along the lines of:

      select mastertable.col1,mastertable.col2,lookuptable1.col2,lookuptable2.col2
from mastertable,lookuptable1,lookuptable2 where
mastertable.col3=lookuptable1.col1
and mastertable.col4=lookuptable2.col1

      Hope this helps.

      It helps when someone looks at a problem from another angle.

      I was expecting a solution involving PHP code whereas you looked at the problem from the MySQL angle.

      If I craft a query to throw the correct data in to an array then standard PHP code for extracting the data should work.

      The only problem I can foresee is that the example in my original post was a simplistic one and my real world master table has no less than 8 lookup table pointers so the query may get messy or I'll have to break it down in to smaller chunks.

      Thanks for the guidance.

        Write a Reply...