Construct Table from SQL directly?

[manoloweb]

Hello people!!

I'm in the middle of a site planning stage, and while going through the same old scenario where I need some database information to be displayed in tables, I suddenly had an idea which could ease things a lot, and probably increase site performance. Please read and give me your thoughts.

WHAT IF?...

Instead of:
...run a query, bring all the fields, and then make a loop to insert <tr>'s and <td>'s in order to create the table.

Make this:
Create a query that automatically brings every row already formated, so I can just send it to the output. I explain...

SELECT CONCAT('<td>',name,'</td><td>',lastName,'</td>') as tablerow from my_table where condition=true

and then ouptut like this:

 while ($row = mysql_fetch_array($rs)) { 
echo "<tr>\n".$row["tablerow"]."\n</tr>\n";
} 

Hope you can help me to see any complications I could find using this approach.

NOTE: One I have seen is that the query could become a problem when changing layout, but I think I can easily modify my DB class to construct queries with or without format.

[maxor]

it would make more sense to pass a row to a function to add the <td> </td> tags than to store that information in the database.

But where is the benefit of any of this over the traditional method?

[Captain Proton]

Well, for one thing, it seems pretty useless.

You simply move the <td>'s to a different place - the sql query - instead of the PHP code. You don't even do this very well because you still have to add <tr> in your PHP code.

The only reason I can think of why not to do this is that a database is not meant for this kind of thing. But I think that a little common sense will tell you to let PHP do the job of printing HTML.

I don't see how it would make things easier or how it would bring performance up. It would probably even lower performance slightly.

...but I think I can easily modify my DB class to construct queries with or without format.

Do a search on these forums for Separation of Concerns, and you'll learn why a DB class should not be concerned with the 'layout of its queries'.

[EvilKiwi]

Indeed, i'm pretty sure the traditional method is way faster than using concat in SQL

[manoloweb]

Beside all your points of view, I also found it will become difficult to manage html entities.

Anyway, it's worth to think on things from a different perspective once in a while. One day it could pay to do it.

Thanks for your thoughts!

[manoloweb]

Oh, and maxor, yes your opinion makes a lot of sense...

It made me start thinking on something like:

$table = new TableFromDB;
$table->use_style($headerStyle,$evenRowStyle,$oddRowStyle);
$table->parse($recordset);
$table->show;

;-)