0

I have data for individual cells of a HTML (or excel) table as

CREATE TABLE table_cols
(
column_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
column_number tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(column_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE table_rows
(
row_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
row_number tinyint(3) unsigned,
title varchar(255),
PRIMARY KEY(row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE table_cells
(
row_id smallint(5) unsigned,
column_id smallint(5) unsigned,
content varchar(255),
PRIMARY KEY(row_id,column_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

What is the best SELECT query to catch data to be displayed in a HTML table as

<table>

<thead> columns titles </thead>

<tbody>
<tr><td>row title</td> cells </tr>
</tbody>

</table>

I don't expert to make the entire table by one query. I get the results by PHP.

1

MySql has no XML data type so it's hardly can help ( more help then app layer code) to generate HTML markup. It can help to create ordered stream of cells. Kind of

select r.row_number, h.column_number, c.content
from table_cells c 
join table_rows r on c.row_id = r.row_d
join table_cols h on c.column_id = h.column_id
order by r.row_number, h.column_number

Or may be to convet sparse array of cells to regular one as directed by table_cols and table_rows, if that is the case.

Not the answer you're looking for? Browse other questions tagged or ask your own question.