Make a multi-column table from a CSV file using PH

问题:

I'm trying to create a multi-column table from a CSV file using PHP. I would like the table cells to list vertically. For example, if I have a CSV file with the names:

bill
mike
sarah
steve
kim
dave

Assuming I need 2 columns, I would like the table to list vertically like this:

bill   steve     
mike   kim   
sarah  dave

I DO NOT want the table to list horizontally, like this:

bill   mike     
sarah  steve   
kim    dave

I have used the following code to make a one column table from a CSV file of player names, team names - and I also passed in a team position attribute. I would like to be able to split the column vertically into 3 columns. Here is the code:

<?PHP

$file_handle = fopen("CSV Team Example.csv", "r");

echo "<table border = '0' cellspacing='1' cellpadding='3'>\n";

while (!feof($file_handle) ) {

$line_of_text = fgetcsv($file_handle);

echo "<tr>\n<td width = '200' position = '" . $line_of_text[2] . "'>". $line_of_text[0]. "  <br> &nbsp;" . $line_of_text[1] . "</td>\n</tr>\n";

}

fclose($file_handle);

echo "</table>";

?>

Here is the CSV file I'm working from. Thank you very much in advance for your consideration.

CSV file:

Scott L. Aranda,Red Devils,Offense
Todd D. Smith,Blue Streaks,Offense
Edward M. Grass,Red Devils,Defense
Aaron G. Frantz,Blue Streaks,Defense
Ryan V. Turner,Red Devils,Offense
Belinda J. Bridges,Red Devils,Defense
Raymond P. Webb,Blue Streaks,Offense
Allison M. Elwell,Blue Streaks,Defense
Melinda B. Savino,Blue Streaks,Offense
Wendy R. Lane,Red Devils,Offense
Gordon Q. Farmer,Blue Streaks,Defense
William F. Lawrence,Red Devils,Offense
Christa L. Limones,Blue Streaks,Offense
Sandra C. Singleton,Red Devils,Offense
Keshia M. Garcia,Blue Streaks,Defense
Margaret A. Arnold,Red Devils,Defense
Paul S. Gonzalez,Blue Streaks,Offense
Mark V. Stocks,Red Devils,Defense
Elizabeth J. Quinn,Red Devils,Offense
Rusty M. Collette,Red Devils,Offense
Myra L. Armstrong,Blue Streaks,Defense
William B. Stewart,Blue Streaks,Defense
Erin J. Hoch,Red Devils,Defense
Robin S. Meredith,Blue Streaks,Offense
Sherie D. Lee,Red Devils,Offense
Michael A. Whitney,Blue Streaks,Defense
Louis R. Ochoa,Red Devils,Defense
Paul R. Garcia,Blue Streaks,Offense
Chester A. Bailey,Red Devils,Defense
Johnny B. Coover,Red Devils,Defense
Emily K. Wright,Red Devils,Offense
Perry D. Desmarais,Red Devils,Offense
Judie J. Burns,Blue Streaks,Defense
Martin L. Dunn,Blue Streaks,Defense
Stephanie C. Rose,Blue Streaks,Defense
Don T. Grimes,Blue Streaks,Offense
Robert C. Devito,Blue Streaks,Offense
Michael J. Taylor,Red Devils,Defense
Melissa D. Bush,Red Devils,Offense

回答1:

Read the entire file into an array of items first, then split the array into X parts (where X is the number of columns you want) and then output one item from each part into a row, repeating for each row.

Your current approach of outputting within the same loop as you read from the file won't work for how you're trying to break it up, since you don't know what the first item in column 2 will be until you've seen all of the items.

回答2:

Sorry, I misunderstood the question.

$csv = 'bill,mike,sarah,steve,kim,dave';
$csv = str_getcsv(implode(',', explode("\n", trim($csv))), ',', '');
$columns = 2;

echo '<table border="1" cellspacing="1" cellpadding="3">';

for ($i = 0; $i < count($csv) / $columns; $i++)
{
    echo '<tr>';

    for ($j = 0; $j < $columns; $j++)
    {
        echo '<td width="200">' . $csv[$i + ($j * ($columns + 1))] . '</td>';
    }

    echo '</tr>';
}

echo '</table>';

Returns:

bill    steve
mike    kim
sarah   dave

回答3:

see the following pseudo-code, you can avoid splitting and copying the array by using the mod operator in php.

Edit: Forgot to initialize the $numcols variable!! should work for any number of columns

echo '<table>';
$f = file('..');
$numcols = 2;
for($i=0;$i<count($f);$i++)
{
$data = str_getcsv($f[$i]);
if($i % $numcols == 0) echo '<tr>';

// cell diplay here..
echo '<td>' . $data[???] . '</td>';

if($i+1 < count($f) && ($i+1 % $numcols) == 0) echo '</tr>';
}

echo '</table>';

or a variation of that.

回答4:

Thank you all very much for your help. I managed to get this working by creating an array as Dav suggested above. The code is a bit ugly, but it does the job - I'm pretty much a php newbie and I put this together from examples I found on the web.

One last question, I'm using the explode function to separate the CSV file based on commas. I'm afraid this won't work if one of my fields has multiple commas, such as an address: 123 elm street, bethesda, maryland 20816. I would prefer to use fgetcsv when I create the array, but I'm not sure how to do it. Can someone help me out?

Also, str_getcsv does not seem to be supported by the version of php on my hosting service so it doesn't work for me.

Here's the code I've used so far - it gives me a perfect result based on my simple CSV file, which I'll post again below.

Code:

<?PHP
// set the number of columns you want
$columns = 4;

// count up number of lines in your CSV file
$file_handle = fopen("CSV Team Example.csv", "r");
$row = 0;
while (!feof($file_handle) ) {
$line_of_text = fgetcsv($file_handle, 1024);
$row++;
}
$number_of_rows = $row;
fclose($file_handle);

// calculate number of rows per column
$rows_per_column = ceil($number_of_rows / $columns);

// create your array
$lines =file('CSV Team Example.csv');

foreach($lines as $data)
{
list($name[],$team[],$team_position[])
= explode(',',$data);
}

// make your table
echo "<TABLE BORDER=\"0\">\n";

//here we changed the condition to $i < $rows_per_column
for($i = 0; $i < $rows_per_column; $i++) {

    echo "<TR>\n";

    //here will run another loop for the amount of columns
    for($j = 0; $j < $columns; $j++) {
        echo "<td width = '200' position = '" . $team_position[$i + ($j * $rows_per_column)]  . "'>". $name[$i + ($j * $rows_per_column)] . "  <br> &nbsp;" . $team  [$i + ($j * $rows_per_column)] . "</td>\n";
        }
    echo "</TR>\n";
}
echo "</TABLE>\n";
?>

Here is my CSV file: CSV Team Example.csv. The first column is player name, second column is team name, and third column is position they play.

Scott L. Aranda,Red Devils,Offense
Todd D. Smith,Blue Streaks,Offense
Edward M. Grass,Red Devils,Defense
Aaron G. Frantz,Blue Streaks,Defense
Ryan V. Turner,Red Devils,Offense
Belinda J. Bridges,Red Devils,Defense
Raymond P. Webb,Blue Streaks,Offense
Allison M. Elwell,Blue Streaks,Defense
Melinda B. Savino,Blue Streaks,Offense
Wendy R. Lane,Red Devils,Offense
Gordon Q. Farmer,Blue Streaks,Defense
William F. Lawrence,Red Devils,Offense
Christa L. Limones,Blue Streaks,Offense
Sandra C. Singleton,Red Devils,Offense
Keshia M. Garcia,Blue Streaks,Defense
Margaret A. Arnold,Red Devils,Defense
Paul S. Gonzalez,Blue Streaks,Offense
Mark V. Stocks,Red Devils,Defense
Elizabeth J. Quinn,Red Devils,Offense
Rusty M. Collette,Red Devils,Offense
Myra L. Armstrong,Blue Streaks,Defense
William B. Stewart,Blue Streaks,Defense
Erin J. Hoch,Red Devils,Defense
Robin S. Meredith,Blue Streaks,Offense
Sherie D. Lee,Red Devils,Offense
Michael A. Whitney,Blue Streaks,Defense
Louis R. Ochoa,Red Devils,Defense
Paul R. Garcia,Blue Streaks,Offense
Chester A. Bailey,Red Devils,Defense
Johnny B. Coover,Red Devils,Defense
Emily K. Wright,Red Devils,Offense
Perry D. Desmarais,Red Devils,Offense
Judie J. Burns,Blue Streaks,Defense
Martin L. Dunn,Blue Streaks,Defense
Stephanie C. Rose,Blue Streaks,Defense
Don T. Grimes,Blue Streaks,Offense
Robert C. Devito,Blue Streaks,Offense
Michael J. Taylor,Red Devils,Defense
Melissa D. Bush,Red Devils,Offense

回答5:

Okay, got it working with fgetcsv only - no 'explode' function used. Thanks everyone.

<?PHP
$columns = 4;

$file = fopen('CSV Team Example.csv', 'r');
$row = 0;
while (($line = fgetcsv($file)) !== FALSE) 
{  
//$line is an array of the csv elements  
list($name[], $team[], $team_position[]) = $line;
$row++;
}
$number_of_rows = $row;
fclose($file);

// calculate number of rows per column
$rows_per_column = ceil($number_of_rows / $columns);
// make your table
echo "<TABLE BORDER=\"0\">\n";

//here we changed the condition to $i < $rows
for($i = 0; $i < $rows_per_column; $i++) {

echo "<TR>\n";

//here will run another loop for the amount of columns
for($j = 0; $j < $columns; $j++) {
    echo "<td width = '200' position = '" . $team_position[$i + ($j * $rows_per_column)]  . "'>". $name[$i + ($j * $rows_per_column)] . "  <br> &nbsp;" . $team[$i + ($j * $rows_per_column)] . "</td>\n";
    }
echo "</TR>\n";
}
echo "</TABLE>\n";
?>