CODEWITHSUNDEEP
phpmysqlcsv
user2109581
user2109581

Reputation: 197

Calculate values and export to csv from MySQL

In MySQL I have to following table

 ID     col1    
 1      234
 2      987
 3      544

I want to use Php to export this data to a .csv file as below

ID     col1    x     y
1      234   9891   18762
2      987   8763   37269
3      544   1852   86329

where values in x and y are the results from 2 functions using values from col1

function calX (col1){

}

function calY (col1){

}

from a post here I got the following code:

$connection = connectToDB();

$query = "SELECT * FROM mytable";

$result = $connection->query($query);

if (!$result) die('Couldn\'t fetch records');

$headers = $result->fetch_fields();

foreach ($headers as $header) {
     $head[] = $header->name;
}
$fp = fopen('php://output', 'w');

if ($fp && $result) {
   header('Content-Type: text/csv');
   header('Content-Disposition: attachment; filename="export.csv"');
   header('Pragma: no-cache');
   header('Expires: 0');
   fputcsv($fp, array_values($head));
   while ($row = $result->fetch_array(MYSQLI_NUM)) {
       fputcsv($fp, array_values($row));
   }
   die;
}

how do I add the x and y columns?

Upvotes: 0

Views: 124

Answers (3)

Karan Shah
Karan Shah

Reputation: 1324

Based on your current code:

$connection = connectToDB();
$query = "SELECT id,col1 FROM mytable";
$result = $connection->query($query);
if (!$result) die('Couldn\'t fetch records');
$headers = $result->fetch_fields();
$i=0;
foreach ($headers as $header) {
    $head[$i++] = $header->name;
}
$head[$i++]="x";
$head[$i]="y";
$csvrow = array();
$fp = fopen('php://output', 'w');
if ($fp && $result) {
   header('Content-Type: text/csv');
   header('Content-Disposition: attachment; filename="export.csv"');
   header('Pragma: no-cache');
   header('Expires: 0');
   fputcsv($fp, array_values($head));
   while ($row = $result->fetch_array(MYSQLI_NUM)) {
       //if there are more elements in row better do loop 
       $csvrow[0] = $row[0];
       $csvrow[1] = $row[1];
       $csvrow[2] = calX($row[0]);
       $csvrow[3] = calY($row[1]);
       fputcsv($fp, array_values($csvrow));
   }
   die;
}

The code I've edited can be optimised further as well.

Upvotes: 1

liquidacid
liquidacid

Reputation: 118

its hard to say without seeing the functions $sumxy = $x + $y;

echo $sumxy;

also here is the link for the docs for the csv export http://php.net/manual/en/function.fputcsv.php

Upvotes: 0

py563
py563

Reputation: 15

say both functions compute and return x and y values. i am rewriting your code

$connection = connectToDB();
$query = "SELECT * FROM mytable";
$result = $connection->query($query);
if (!$result) die('Couldn\'t fetch records');
$headers = $result->fetch_fields();
$i=0;
foreach ($headers as $header) {
    $head[$i++] = $header->name;
}
$head[$i++]="x";
$head[$i]="y";
$csvrow = array();
$fp = fopen('php://output', 'w');
if ($fp && $result) {
   header('Content-Type: text/csv');
   header('Content-Disposition: attachment; filename="export.csv"');
   header('Pragma: no-cache');
   header('Expires: 0');
   fputcsv($fp, array_values($head));
   while ($row = $result->fetch_array(MYSQLI_NUM)) {
       //if there are more elements in row better do loop 
       $csvrow[0] = $row[0];
       $csvrow[1] = $row[1];
       $csvrow[2] = functionCalX($row[1]); 
       //add Y also , also do typechecking if needed
       fputcsv($fp, array_values($csvrow));
   }
   die;
}

i edited the same code, it can be optimized. I am not that familiar with php

Upvotes: 0

Related Questions