Sort CSV file and display in HTML table with PHP

Question

I have a CSV file that has (dummy) data like this:

Mike Judge, Office Space, Comedy
Larry Wachowski, Matrix Zero, Action
Sofia Coppola, Lost In Translation, Drama
Ron Howard, A Beautiful Mind, Drama
Joe Biden, Your Big Hat, Sports
Barack Obama, The Stubborn Goat, Drama
Steve Smith, 2 Beautiful Ants, Suspense
Jared Hess, Napoleon Dynamite, Comedy
Jack Daniels, Field of Dreams, Drama

I need the 1st field sorted alphabetically by last name. That works.

Then I need to show the array in an HTML table and be able to set the number of columns to perhaps 5. I need each cell to hold one line from the CSV file, then move left to right filling the table.

Everything I've tried gets me almost there, but the table does not display properly - it has missing td tag or no   in the last td.

Any help would be appreciated.

Here's what I have so far:

 $file = fopen("dummy-list.csv", "r") or die('cannot open file');

// sort
$surnames = array();
$rows = array();
//build array of surnames, and array of rows
while (false != ( $row = fgetcsv($file, 0, ',') )) {
    //extract surname from the first column
    //this should match the last word before the comma, if there is a comma
    preg_match('~([^\s]+)(?:,.*)?$~', $row[0], $m);
    $surnames[] = $m[1];
    $rows[] = $row;
}
//fclose($file);
//sort array of rows by surname using our array of surnames
array_multisort($surnames, $rows);
//print_r($rows);


// set # of table columns
$numCols = 4;
$i=0;


echo '<table border="1" align="center"><tr>' . "\n";
foreach( $rows as $rows2 )
{

if ($i % $numCols == 0 && $i != 0) 
    echo "</tr>\n<tr>"; // every time but the first

    echo '<td>';

    foreach( $rows2 as $key )
    {
            echo $key . '<br />';
        $i++;
    }
    echo "</td>\n";
}


while ($i++ % $numCols != 0) 
echo '<td>&nbsp;</td>' . "\n"; // fill in empty cells


// end table
echo '</tr></table>' . "\n\n\n";

I achieve this by converting the CSV to JSON and using DataTables for this — Derek Pollard
– Derek Pollard, Commented Feb 24, 2016 at 23:12
GolezTrol - your comment is not helpful unless you are going to recommend what you use now in 2016... — user888864
– user888864, Commented Feb 24, 2016 at 23:40

Ríder Cantuária · Accepted Answer · 2016-02-25 00:18:43Z

1

Stay with your code:

Change this:

while ($i++ % $numCols != 0) 
echo '<td>&nbsp;</td>' . "\n"; // fill in empty cells

For it:

$mod = $numCols-count($rows)%$numCols;

for($i=0;($i < ($mod) && $mod != $numCols);$i++)
    echo '<td>&nbsp;</td>' . "\n";

And solve your problems!

answered Feb 25, 2016 at 0:18

Ríder Cantuária

316 bronze badges

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

Comments

dval · Accepted Answer · 2016-02-25 04:43:42Z

1

You just need to modify your logic slightly. Currently, your script doesn't track which column your in, so it has no way of knowing how many cells to pad.

The biggest logical problem is:

while ($i++ % $numCols != 0) 
echo '<td>&nbsp;</td>' . "\n"; // fill in empty cells

All that is doing is checking that the number of columns is devisable by $i. so, after $i gets to 3, it stops. (in this case 4/3 != 0)

The following works:

$file = fopen("dummy-list.csv", "r") or die('cannot open file');

// sort
$surnames = array();
$rows = array();
//build array of surnames, and array of rows
while (false != ( $row = fgetcsv($file, 0, ',') )) {
   //extract surname from the first column
   //this should match the last word before the comma, if there is a comma
   preg_match('~([^\s]+)(?:,.*)?$~', $row[0], $m);
   $surnames[] = $m[1];
   $rows[] = $row;
}
//fclose($file);
//sort array of rows by surname using our array of surnames
array_multisort($surnames, $rows);
//print_r($rows);


// set # of table columns
$numCols = 4;
$i=0;
$cellpos = 0; //count columns

echo '<table border="1" align="center"><tr>' . "\n";
//use the amount of rows as a divisor
for($c=0; $c<Count($rows); $c++ )
{

    if ($i % $numCols == 0 && $i != 0){
       echo "</tr>\n<tr>"; // every time but the first
       $cellpos=0;  //reset our column position
    }

    echo '<td>';
    //then, call each csv row explicitly
    foreach( $rows[$c] as $key )
    {
        echo $key . '<br />';
        $i++;
    }
    echo "</td>\n";
    $cellpos++;  //track our column position
}

while ($cellpos++ < $numCols)
echo '<td>&nbsp;</td>' . "\n"; // fill in empty cells


// end table
echo '</tr></table>' . "\n\n\n";

I added $cellpos variable to count the td in each row, as your populating. It gets reset every time you start a new row. if you track that var through the script you will see my changes.

Additionally, if you want to do something more specific with those keys, you can call them directly instead of just looping over the row. Just change the code between <td> tags.

echo '<td>';

//then, call each csv celldata explicitly

    echo '<a href="' . $rows[$c][0]. '"><img src="'. $rows[$c][1] . '" /><br />' .$rows[$c][2] . '</a>';
    $i++;

echo "</td>\n";

edited Feb 25, 2016 at 4:43

answered Feb 24, 2016 at 23:44

dval

3965 silver badges15 bronze badges

6 Comments

user888864 Over a year ago

dval - if I change my csv to: Mike Judge, artists/judge.php, images/bikini.jpg Larry Wachowski, artists/wachowski.php, images/wachowski.jpg Sofia Coppola, artists/coppola.php, images/coppola.jpg

user888864 Over a year ago

this code does not work: //echo $key . '<br />'; echo "<a href=" . $rows[$c][1]. "><img src=". $rows[$c][2] . " /><br />"; echo $rows[$c][0] . "</a>";

dval Over a year ago

@user888864 you need to format that line correctly, your using double quotes to define a string, but the string also has double quotes. you need to either use single quotes around your HTML, or escape all the double quotes in the string. Here is the string correctly formatted: echo '<a href="' . $rows[$c][0]. '"><img src="'. $rows[$c][1] . '" /><br />' .$rows[$c][2] . '</a>'; Additionally, you need to comment out the foreach loop, because instead of looping through the row, you are now explicitly declaring the keys $rows[$c][0] , etc... I'll update my answer to reflect this

dval Over a year ago

and by 'explicitly declaring the keys' I meant 'explicitly calling'.

user888864 Over a year ago

When I comment out the foreach loop, I get extra td cells......sorry to be a pain.....but thank you so much for your help. Is there a site where we can demo this code so that it's easier for me to see it in full? ideone.com or codepen.com?

|

Steve · Accepted Answer · 2016-02-24 23:19:32Z

0

You can use the MOD operator to check if you are at the end of a column and then end the row, and start a new one:

$cols = 4;
$count = count($rows);

$table_html ="<tr>";
for($i=0; $i < $count; ++$i){
    $table_html.= '<td>' . implode("<br>", $rows[$i]) . '</td>';
    if($i != 0 && ($i % $cols == 0)){
        $table_html.="</tr>";
    }
}
$table_html.="</tr>";
echo $table_html;

answered Feb 24, 2016 at 23:19

Steve

20.5k5 gold badges47 silver badges71 bronze badges

Collectives™ on Stack Overflow

Sort CSV file and display in HTML table with PHP

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related