Displaying a table in PHP with repeated columns

Question

I have the following data in a MySQL database:

Autonum  ID  Name  MetaValue
1         1  Rose  Drinker
2         1  Rose  Nice Person
3         1  Rose  Runner
4         2  Gary  Player
5         2  Gary  Funny

I am working in PHP now but I have encountered this problem several times using C#, Java and other languages.

Now my goal in the past and present has been to display the data in the following format:

<table>
    <thead>
    <th>Name</th>
    <th>MetaValue1</th>
    </thead>
    <tbody>
           <tr>      
               <td>Rose</td>
                <td>
                    <ul>
                        <li>Drinker</li> 
                        <li>Nice Person</li>
                        <li>Runner</li>
                     </ul>
                </td>
            </tr>
            <tr>
                <td>Gary</td>
                <td>
                    <ul>
                         <li>Player</li>
                         <li>Funny</li>
                 </td>
                </tr>
        </tbody>

</table> 

I have tackled this problem before by creating a class that represents my SQL table. Then I created a Dictionary to hold EmployeeId and the class.

Dictionary<string,MyTable> MyData = new <string,MyTable>();
Table MyMetaData = new Table();
MyMetaData SomeMetaData=getMetaValueList();//imagine a web service that does that
MyData.add(EmployeeId,SomeMetaData);

I am skipping steps but I hope you get my point. I probably just need keywords of what to call this type of problem. What is the preferred way to accomplish this?

Answer 1

I do not like SQL solution for one reason. You ask database engine to join string and then you use PHP to split it. It is like repeated work. Another down side is that what if metavalue contains seperator character.

My code just demonstrates how you can easily group data with 2 dimensional array in PHP and it is up to coder to decide which method to retrieve data from database either legacy mysql_* or PDO.

// get result
$result = mysql_query("SELECT autonum,id,name,metavalue FROM table");

// loop through each row
while ($row = mysql_fetch_assoc($result)) {
    $table[$row['name']][] = $row['metavalue'];
}

// print it out
print_r($table);

There is no need to temporarily store the whole resultset into an array. You can iterate straight ahead by first sort data by what field you want to group your data and keep track of state.

However, there are two reasons why I prefer this style and it is personal preference (it is not the must-must do solution):

1. Usually, everyone want to separate logic and presentation apart. Storing data in array can be easily passed to your template engine. I prefer using PHP template engine for it is easy to implement and it runs at light speed.

2. I would rather trade slight performance speed and memory usage for readability and maintainability. The algorithm is short and simple to understand. If performance is your top priority, you can always use cache such as storing in file, storing in memcached, or storing calculated result in database.

True, we are grouping data from different side, but you assume that the metavalue does not contain separator (which in your code is |). It can be easily fixed by choosing separator that almost impossible to be used by metavalue. Anyway, your solution will works perfectly fine in almost every case because metavalue that @jvelez has shown will never contain character |.