Dynamically build a table with PHP and mysql

Question

I've been reading some q&a but still haven't managed to build my algorithm, so I'm asking for some help/suggestions on how to.

So, I have a MySQL table and for each mysql table row I want to display it in a table. I want my table to have only 4 cols (meaning the number of rows is variable depending on the data in the database). So, in html table's (row 1, col 1) would be the data from db table row 1, html (row 1, col 2) data from db table row 2 ... (row 2, col 1) data from db table row 5 and so on.

So, the table would be something like:

[data from db row #1] [data from db row #2] [data from db row #3] [data from db row #4]

[data from db row #5] [data from db row #6] [data from db row #7] [data from db row #8]

and so on...

This is what I have to get the data from db:

function get_albums() {
    $albums = array();
    
    $albums_query = mysql_query("SELECT 'albums'.'album_id', 'albums'.'role_id', 'albums'.'timestamp', 'albums'.'name', LEFT('albums'.'description',50) as 'description', COUNT('images'.'image_id') as 'image_count'
    FROM 'albums'
    LEFT JOIN 'images'
    ON 'albums'.'album_id' = 'images'.'album_id'
    GROUP BY 'albums'.'album_id'");
        
    // Loop through all images
    while ($albums_row = mysql_fetch_assoc($albums_query)) {
        // multidimensional array
        $albums[] = array(
                    // associative array
                    'id' => $albums_row['album_id'],
                    'role' => $albums_row['role_id'],
                    'timestamp' => $albums_row['timestamp'],
                    'name' => $albums_row['name'],
                    'description' => $albums_row['description'],
                    'image_count' => $albums_row['image_count']
        );
    }   
    return $albums;
}

To display that info:

$albums = get_albums();

echo '<table>';
for ($i=0; $i<(count($albums)/4); $i++) {
    echo '<tr>';

    //HERE IS WHERE I'M LOST

    foreach ($albums as $album) {
        echo '<a href="view_album.php?aid=',$album['id'],'">',$album['name'],'</a> (',$album['image_count'],') image(s)<br />',$album['description'],'...';

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

So, any ideas?

You're forgetting your <td> tags and concatenating with , instead of .. — Matt
– Matt, Commented Aug 2, 2012 at 16:59
You'll want to use the modulus operator and remove the division by 4 in the for loop. And +1 to what Matt said. — Josh
– Josh, Commented Aug 2, 2012 at 17:00
Firstly, your concatting in your query is not correct. Secondly, you're selecting multiple columns per data entry, but you want to display all of it as one cell? Am I understanding this correctly? — Palladium
– Palladium, Commented Aug 2, 2012 at 17:00
I 'forgot' the <td> tags on purpose because it's where I don't know what to code. — Bruno
– Bruno, Commented Aug 2, 2012 at 17:04

Matt · Accepted Answer · 2012-08-02 20:03:13Z

3

// keep calculations outside of the for loop if you can

// this will display all albums (count($albums) / 4) times.

$limit = count($albums) / 4;
for ($i = 0; $i < $limit; $i++) {
    echo '<tr>';

    foreach ($albums as $album) {
        echo '<td><a href="view_album.php?aid=' . 
            $album['id'] . '">' . $album['name'] . '</a> (' . 
            $album['image_count'] . ') image(s)<br />' . 
            $album['description'] . '...</td>';
    }
    echo "</tr>";
}

What you're probably looking for is

<?php
$counter = 0;

foreach($albums as $album) :
    // start new row 
    if ($counter == 0): ?>
        <tr>
    <?php endif; ?>
    <td><a href="view_album.php?aid=<?= $album['id'] ?>"><?= $album['name'] ?></a> (<?= $album['image_count'] ?>) image(s)<br />         
        <?= $album['description'] ?>...</td>
    <?php if ($counter++ == 3) : ?> <!-- 4 records printed in row; end row & reset counter -->
        </tr>
    <?php
        $counter = 0;
    endif;
endforeach;

$lastCount = $counter;
// print remaining empty cells
while ($counter ++ < 4) : ?>
    <td>&nbsp;</td>
endwhile;
// close row if necessary
if ($lastCount != 3) :
    </tr>
endif;

edited Aug 2, 2012 at 20:03

answered Aug 2, 2012 at 17:03

Matt

7,0504 gold badges31 silver badges50 bronze badges

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

5 Comments

Josh Over a year ago

This isn't quite right. This will output as many <td> as there are albums in each row and have as many rows as 25% of the albums.

Matt Over a year ago

@Josh I just noticed that and edited for completeness & clarity. Thx!

Josh Over a year ago

Your update will create 1 album per row, as opposed to the OP's aim, which is 4 albums per row.

Josh Over a year ago

+1 I haven't tested it, but you may want to use prefix increment (++$a), rather than postfix ($a++). Otherwise, it seems like it should work.

Matt Over a year ago

I did postfix because I want comparison before incrementation.

Collectives™ on Stack Overflow

Dynamically build a table with PHP and mysql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related