homepage Welcome to WebmasterWorld Guest from 54.161.200.144
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Storing Complex Data Tables in mysql
Like the old days of html except for real
master w bates III




msg:3702854
 9:12 am on Jul 20, 2008 (gmt 0)

Hello, I am fairly new to mysql and rdb's in general. I have made some blogs and some forums but that's about it. I am currently working on a project in PHP that involves complex data tables. Some of the td elements have different colspans and rowspans.

Here is a very simple example:

<tbody>
<tr>
<th scope='row' rowspan='2'>title</th>
<td>content</td>
<td colspan="3"> content </td>
</tr>
<tr>
<td> content</td>
<td colspan="2"> content </td>
<td> content </td>
<td content</td>
</tr>
<tr>
<td> content</td>
<td colspan="4"> content </td>
</tr>
</tbody>

This is a very simplified example.... Could someone point me to a resource or suggest the best way of putting this in a database. My guess is since it is a table, to store it as it's own table, but what is the best way to store the colspan and rowspan differences in each td and make sure that I output correct amount of table cells with the right attributes? I really have no idea how to store and retrieve complex tables of different shapes.

Am I looking at this completely wrong? Thanks for any help or suggestions. A hint and a book title would be awesome.

 

janharders




msg:3702863
 9:50 am on Jul 20, 2008 (gmt 0)

is there any system behind the content you have to store?

if not, you could just add them to the values in the database, do something like, say a table for the actual tables, mainly just an id and a name, a second table for rows, referencing the table-table, and a third one for the cellsets, referencing the row-table. in the cell-table you could just add rowspan and colspan. it's not the real beauty, but it'd work. maybe someone else can come up with a better solution.

master w bates III




msg:3702890
 11:33 am on Jul 20, 2008 (gmt 0)

Hi janharders. Thanks for the suggestion. :)

I am using a homerolled php framwork and mysql.

Your solution is interesting but I don't think it would scale very well.

I was thinking perhaps I should store the duplicate cells to the right of each row as null and sort it out on the php end.

so the op example would be stored

to make this:

title /// / ___________________________________________
__________________________________________________
____________________________________________________

Like this?

title___ content content nnnnnn nnnnnn
------------------------------------------------
nnnnnn content nnnnnn content content
------------------------------------------------
content content nnnnnn nnnnnn nnnnnn
------------------------------------------------

i just have no idea how to get php to read a null value from sql and i++ the colspan of the previous element. the other issue would be getting the title in the example to adjust to the rowspan=2 since the element below it would be null.

edit#1 : wait a second, wouldn't I have to make a separate query for each table to lay them out separately? That could get grossly inefficient. It it even worth it? should I just include the hardcoded tables in an include file? since it would be just as much work entering them through any interface. This doesn't seem ideal either though.

Weren't these db systems designed to store complex tables in the first place?

Edit#2: for whatever reason typing six x's turns into #*$!#*$!

[edited by: master_w_bates_III at 12:03 pm (utc) on July 20, 2008]

janharders




msg:3702897
 12:07 pm on Jul 20, 2008 (gmt 0)

yes, it wouldn't scale very well and it's not nice ether. That's why more information could help...

do you always have a maximum of 4 cells per row?
how many of those tables do you need to show on a single page request?

if it's 4 cells, you could do something like you suggested. I'd read all the rows necessary for the current page, split them into tables and probably build a datastructure from it. that way, you could then traverse through the arrays and fix everything up, having something like
$tables = array(
0 => array( # first table
0 => array( # first row
0 => array( # first cell
'content' => 'test test',
'rowspan' => 1,
'colspan' => 1
),
1 => array( # second cell
'content' => 'test2 test',
'rowspan' => 1,
'colspan' => 1
),
1 => array( # third cell
'content' => FALSE,
'rowspan' => 1,
'colspan' => 1
),
1 => array( # fourth cell
'content' => FALSE,
'rowspan' => 1,
'colspan' => 1
)
)
)
)

to start with. looping through, you could then check for each cell if it's neighbor is empty, thus deciding wether it's colspan should increase. same for the rowspan. in theory, it's not that hard. needs a little code, though, I guess.

but wether to do that each time you generate a page or to just include tables you built before, that depends on how flexible the whole thing needs to be, now often the data in those tables changes etc

master w bates III




msg:3703198
 4:17 am on Jul 21, 2008 (gmt 0)

yes, it wouldn't scale very well and it's not nice ether. That's why more information could help...

I am in the planning stages and would like to learn how to do this properly. It's wide open.

do you always have a maximum of 4 cells per row?

no, sadly not. the example was greatly simplified. I am dealing with a number of tables that are all in different shapes. From a front end usability perspective, due to the nature of the content (highly technical specifications) that i need to utilize col and row spans to make it remotely comprehendible.

... that depends on how flexible the whole thing needs to be, now often the data in those tables changes etc

Well a table is not going to change very often, however searching values from the database would be an appealing feature.

I appreciate your help and at this point keeping the tables static is my best option. The database is going to be complex enough, without adding a buku of individual specs tables.

Still if anyone has a good book that covers something like this please give me a recommendation. It would be greatly appreciated. :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved