question on joins

[maximisedk]

I need to create a HTML table for a number of users. Each user will have a unique HTML table in regard to number of columns, as the user can choose to append columns to his HTML table.

The way I went about doing this is as follows:

create a table for the row count:
list
------------
id
user_id

The ID is the row of the table.

Then create a table with information on columns attached to the particular users HTML table:

list_label
------------
id
user_id
column_number
type
label


When a user appends a column to his HTML table a new record will be added where user_id = id in the list table, thus allowing me to find the number of columns in a particular users HTML table.

Then I create a table with the cell content for the users HTML table:

list_item
-------------
id
user_id
list_id
label
content_text
content_date
content_number

Now the users HTML table can be drawn:

The header:
SELECT * FROM list_label WHERE user_id = $userid ORDER BY column_number
result = (column1, column2... columnx)

l column 1 l column 2 l column3 l etc...
---+-----------+----------+---------+-------


Now I can fill out the cells in the HTML table from the tables above using data from the list_item table knowing that:
rows in the HTML table: list.id = list_item.list_id
and columns in the HTML table: list_item.label = list_label.label

Furthermore, the datatype in a particular column in the HTML table is given in the list_label.type variable, such that if for instance column 1 contains text the the row retrieved from list_item for that column should be content_text.

Now the question is: Is it possible to retrieve the HTML table in a single SQL query? And if so how do i do it?

[ronverdonk]

All information about db's, tables, columns, contents and the rest can be found in MySQL's INFORMATION_SCHEMA tables. So you don't have to keep track of that.
See MySQL manual INFORMATION_SCHEMA

Ronald

[maximisedk]

All information about db's, tables, columns, contents and the rest can be found in MySQL's INFORMATION_SCHEMA tables. So you don't have to keep track of that.
See MySQL manual INFORMATION_SCHEMA

Ronald

Thanks a lot Ronald, I really appreciate it.

I'll try and check it out and see if I can solve it :-)

Max

[ronverdonk]

You are welcome! See you next time.

Ronald