PHP: Creating big HTML-table efficiently

Question

I'm making a HTML table based on certain information in my MySQL database. The tables I need from the database hold the data for rows (rowid, rowname), columns (columnid, columnname) and cell data. A table, that links IDs from rows and columns together (cellid, rowid, columnid, somedata). The table looks something like this:

__________| column1  | column2  | column3  | ...
     row1 | somedata | somedata |          |
----------|----------|----------|----------|-----
     row2 | somedata |          | somedata |
----------|----------|----------|----------|-----
...       |          |          |          |

My approach was to use several nested foreach loops, something like this:

$rows = new Rows();        //These objects are containers for objects
$columns = new Columns();  //that hold some of the database data. 
$alldata = new Alldata();  //MySQL queries within these objects are irrelevant, I think. They already get the relevant data

$count = count($alldata);

echo "<table>";
echo "<tr>";
echo "<td>&nbsp;</td>";

foreach ($columns->getColumns() as $column) {
    echo "<td>".$column->getColumnname()."</td>";
}

echo "</tr>";

foreach ($rows->getRows() as $row) {
    echo "<tr>";
    echo "<td>".$row->getRowname()."</td>";

    foreach ($columns->getColumns() as $column) {
        $last = 1;

        foreach ($alldata->getAlldata() as $data) {
            if ($data->getCID() == $column->getID() & $data->getRID() == $row->getID()) { //If the column has data the related to the row
                echo "<td>".$data->getSomedata()."</td>";
                break;
            }
            if ($last == $count) { //if loop couldn't find any entries, it prints an empty cell
                echo "<td>&nbsp;<td>";
            }
            $last++
        }
    }
    echo "</tr>";
}
echo "</table>";

Bruteforcing, obviously this is not very efficient method when there are several hundred rows of data on any table in the database and I don't like this. Any ideas how to make this more efficient? Is there better way of creating a table like I need?

EDIT:
Pondering about this problem for a week has finally given me some answers. It was so simple!
I did a slight modification to my Column object class. Previously, my code went through all possible entries on celldata table in the database. Now Column object gets an array of celldata where columnid's match and I can do the comparison using only that data.

$rows = new Rows();
$columns = new Columns();

echo "<table>";
echo "<tr>";
echo "<td>&nbsp;</td>";

foreach ($columns->getColumns() as $column) {
    echo "<td>".$column->getColumnname()."</td>";
}

echo "</tr>";

foreach ($rows->getRows() as $row) {
    echo "<tr>";
    echo "<td>".$row->getRowname()."</td>";

    foreach ($columns->getColumns() as $column) {
        $last = 1;
        $count = count($column->getAlldata());

        foreach ($column->getAlldata() as $data) {
            if ($data->getCID() == $column->getID() & $data->getRID() == $row->getID()) {
                echo "<td>".$data->getSomedata()."</td>";
                break;
            }
            if ($last == $count) {
                echo "<td>&nbsp;<td>";
            }
            $last++
        }
    }
    echo "</tr>";
}
echo "</table>";

Much faster, although still bruteforcing but there's less amount of data to go through. Of course, now the problem is that Column objects may do quite a lot of MySQL queries, depending on how many Columns there are.

Are the row and column IDs guaranteed to be in numerical order? — eggyal
– eggyal, Commented Jun 30, 2012 at 7:37

eggyal · Accepted Answer · 2012-07-01 10:11:06Z

1

If you ensure that the data fetched from MySQL is correctly ordered in $alldata by joining the tables and specifying an ORDER BY clause in your query:

SELECT   data.*
FROM     data RIGHT JOIN rows USING (rowid) RIGHT JOIN columns USING (columnid)
ORDER BY rowname, columnname;

Then you only need to output each cell in order:

echo '<table>';

echo '<thead>'
echo '<tr>';
echo '<th>&nbsp;</th>';
foreach ($columns->getColumns() as $column) {
    echo '<th scope="col">', htmlentities($column->getColumnname()), '</th>';
}
echo '</tr>';
echo '</thead>';

echo '<tbody>';
$data = $alldata->getAlldata();
foreach ($rows->getRows() as $row) {
    echo '<tr>';

    echo '<th scope="row">', htmlentities($row->getRowname()), '</th>';
    foreach ($columns->getColumns() as $column) {
        $d = each($data);
        echo '<td>', ($d ? htmlentities($d[1]->getSomedata()) : '&nbsp;'), '</td>';
    }

    echo '</tr>';
}
echo '</tbody>';

echo '</table>';

edited Jul 1, 2012 at 10:11

answered Jun 30, 2012 at 7:42

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RLXi Over a year ago

Thanks, I'll try that and see what happens.

deej Over a year ago

if it's really big table and you want to try some micro optimization you can try using comma "," instead of concatenation "." in your echo statements.

RLXi Over a year ago

That MySQL query seemed to do pretty good job. As I examined what some of the objects hold, there were some objects with null values. Perhaps that was the thing I was looking for. However, your code doesn't go work with mine. The problem lies in $d = $data->getSomedata(). $data is not an object but an array so it can't use that function.

RLXi Over a year ago

@eggyal: Did it? Function getAlldata() gets an array of objects and my third foreach loop (foreach ($alldata->getAlldata() as $data)) goes through the objects in that array. The objects inside the array represents rows in the database.

RLXi Over a year ago

Yes. As I said, $alldata->getAlldata() gets an array of objects. I can't use getSomedata() function on that array. I have to break it apart first in order to use the getSomedata() function. Just to make it clear, Alldata and Data are separate objects and they don't share any functions.

Kaivosukeltaja · Accepted Answer · 2012-06-30 08:26:46Z

0

The best way to solve this would be to rewrite the query for $alldata so that it is in the order you want like eggyal suggested. If you can't do that for some reason, you could iterate through $alldata in the beginning and create a properly indexed array from it. Something like this:

$ordereddata = array();
foreach($alldata as $d) {
    $ordereddata[$d->getCID()][$d->getRID()] = $d->getSomedata();
}

Now you can replace your foreach($alldata) loop with this:

if(isset($ordereddata[$column->getID()][$row->getID()])) {
    echo "<td>{$ordereddata[$column->getID()][$row->getID()]}</td>";
} else {
    echo "<td>&nbsp;</td>";
}

answered Jun 30, 2012 at 8:26

Kaivosukeltaja

15.8k5 gold badges45 silver badges71 bronze badges

1 Comment

RLXi Over a year ago

I have full access to any part of the code. I actually tried something similar before but it didn't work. I got all the data but I couldn't print empty cells. Every cell just aligned to the left with no space between them.

Collectives™ on Stack Overflow

PHP: Creating big HTML-table efficiently

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related