Creating csv output with PHP from MySQL table using semicolon as delimiter

Question

I've encounter the problem of creating an csv output with php and mysql. The problem is that I cannot show different output based on the number of filled elements on my page.

INTRODUCTION: I have several textareas, users fill in their text or number about something. Users answers of one question are split in database by semicolon.

MySQL table: finish
Columns(fields): pt1, pt2, pt3

GOAL: I want to generate csv that will show users answers in separate columns. If user answers only 1 textarea of each question, the result should be like this:

Users fill in 1 textarea for each question

If user fill in all of the textareas for example with number from 1 to 8 - my code below gives the result in csv file like this:

Current code prints out this (below)

PROBLEM: Is there something I can do, in order to display result like this:

Make separate columns using delimiter

I was wondering if I can do it, to make it somehow add additional columns in excel output and use delimiter (;) to separate the values of the answers from mysql table.

Here is my code.

INDEX.PHP

<form action="process.php" method="post">
First point: <br>
    <textarea rows="1" cols="50" name="point11"></textarea><br>
    <textarea rows="1" cols="50" name="point12"></textarea><br>
    <textarea rows="1" cols="50" name="point13"></textarea><br>
    <textarea rows="1" cols="50" name="point14"></textarea><br><br>
Second point: <br>
    <textarea rows="1" cols="50" name="point21"></textarea><br>
    <textarea rows="1" cols="50" name="point22"></textarea><br>
    <textarea rows="1" cols="50" name="point23"></textarea><br><br>
Third point: <br>
    <textarea rows="1" cols="50" name="point31"></textarea><br>
<input type="submit">
</form>

PROCESS.PHP

<?php
for($j=1;$j<101;$j++) {
    $point[$j] = "";
}
for($i=1;$i<4;$i++) {
    for($k=1;$k<10;$k++) {
    if(isset($_POST['point'.$i.$k]) && !empty($_POST['point'.$i.$k])) {
        $point[$i] .= $_POST['point'.$i.$k].'; ';
    }}}
for ($i=1;$i<4;$i++) {
    $pt[] = "pt".$i;
}
$pt_list = implode(',', $pt);
for ($i=1;$i<4;$i++) {
    $point_ids[] = $point[$i];
}
$point_list = implode("','", $point_ids);
?> 
<?php
$host = 'localhost';
$user = 'root123';
$pass = '123';
$db   = 'exampledb';
$table = 'finish';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("INSERT into ".$table."({$pt_list}) VALUES ('{$point_list}')");

echo "The database has been updated";
?>

<form action="export.php" method="post"><input type="submit" value="Export as CSV"></form>

EXPORT.PHP

<?php
$host = 'localhost';
$user = 'root123';
$pass = '123';
$db = 'exampledb';
$table = 'finish';
$file = 'export_';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
if (mysql_num_rows($result) > 0) {
 while ($row = mysql_fetch_assoc($result)) {
  $csv_output .= $row['Field'].", ";
  $i++;
 }}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
 for ($j=0;$j<$i;$j++) {
  $csv_output .= $rowr[$j].", ";
 }
 $csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d");
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

Ajay Chauhan · Accepted Answer · 2014-04-26 05:06:01Z

1

Please replace the content of your export.php file with the following code hope this helps

<?php
$host = 'localhost';
$user = 'root123';
$pass = '123';
$db = 'exampledb';
$table = 'finish';
$file = 'export_';
$i = 0;
$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");
$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$columnName = array();
if (mysql_num_rows($result) > 0) 
{
    while ($row = mysql_fetch_assoc($result)) 
    {
        $columnName[] = $row['Field'];
        $i++;
    }
}
$needle = ';';
$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) 
{
    for ($j=0;$j<$i;$j++) 
    {
        $colName = $columnName[$j];
        $count = strlen($rowr[$j]) - strlen(str_replace(str_split($needle), '', $rowr[$j]));
        if ($count > 1)
        {
            for($p=0;$p<$count;$p++)
            {
                $colName .= ",";
            }
            $columnName[$j] = $colName;
            $csv_output_column_names .= $columnName[$j].", ";
            $csv_output_column_values .= str_replace(';',',',$rowr[$j]).", ";
        }
        else
        {
            $csv_output_column_names .= $columnName[$j].", ";
            $csv_output_column_values .= $rowr[$j] .", ";
        }
    }
}
$csv_output = $csv_output_column_names."\n".$csv_output_column_values;
$filename = $file."_".date("Y-m-d");
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>

answered Apr 26, 2014 at 5:06

Ajay Chauhan

963 bronze badges

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

2 Comments

mpavlovic89 Over a year ago

The rows under the columnNames are not every in each line in excel? Do you encounter the same problem... Obviously somewhere a \n is needed. Btw, you're code is very helpful, because maybe this can be a solution

mpavlovic89 Over a year ago

Actually, the bigger problem is when there is more then one row in a db, it prints out multiple columnNames. It should be one columnNames (pt1,pt2,pt3) and beneath the content

Collectives™ on Stack Overflow

Creating csv output with PHP from MySQL table using semicolon as delimiter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related