CODEWITHSUNDEEP
phpmysqlsqldatabaseexport-to-csv
kenn
kenn

Reputation: 1

How to export query result to csv file?

Is it possible to create csv file from this?

this table in database look like the one echo below

    id  | category|  supplier   | price
    1   |    A    |  supplier1  | 5
    2   |    B    |  supplier2  | 3
    3   |    B    |  supplier1  | 7
    4   |    C    |  supplier3  | 6
    5   |    C    |  supplier1  | 2
    6   |    B    |  supplier3  | 9

<?php
session_start();
$host = "localhost";
$user = "root";
$pass = "";

$con = mysql_connect($host,$user,$pass);
if($con)
{
      $db = mysql_select_db('stocks');
       if($db)
       {}
       else 
       {
         echo "No Database Found! " ;
        }
}
else 
{
     echo "Failed to Connect! ";
}
?>
<table>
<tr>
    <th>Category</th>
    <th>Stock</th>
    <th>Percentage</th>
</tr>
<?php
$total=6;
        $q="SELECT id,name,supplier,price,COUNT(*) FROM  items GROUP BY supplier";
        $r = mysql_query($q);
        while($row = mysql_fetch_array($r))
        {
                 $ratio=($row['COUNT(*)']/$total)*(100);
            echo "<tr>";
echo "<td>" . $row['category'] . "</td>";
echo "<td>" . $row['COUNT(*)'] . "</td>";
echo "<td>" . round($ratio,2). "%</td>";
echo "</tr>";
        }
echo "</table>";        
         ?>

How can I put that in csv?

I only know to do it for select all in table.

Category Stock Percentage A 1 16.67% B 3 50% C 2 33.33%

Upvotes: 0

Views: 5234

Answers (3)

Davinder Kumar
Davinder Kumar

Reputation: 31

You can use php function fputcsv for this. i am sharing a example working code.

$output = fopen('result.csv', 'w');

$rs=mysql_query($sql,$conn);

while($row = mysql_fetch_assoc($rs)) {
    fputcsv($output, $row);
}

Upvotes: 1

kenn
kenn

Reputation: 1

while($row = mysql_fetch_array($r))
        {   $data=array($row['category'],$row['COUNT(*)'],round(($row['COUNT(*)']/$total)*(100),2));
            fputcsv($output, $data);
        }

@Davinder @Virgilio this works exactly thanks but with error message (Parse error: syntax error, unexpected ')' in C:\xampp\htdocs\revamp\csv.php on line 39) other than that it works perfectly

Upvotes: 0

Virgilio Falla
Virgilio Falla

Reputation: 7

// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('Column 1', 'Column 2', 'Column 3'));

// fetch the data
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
$rows = mysql_query('SELECT field1,field2,field3 FROM table');

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

link to the page http://code.stephenmorley.org/php/creating-downloadable-csv-files/ hope that fit your needs

Upvotes: 0

Related Questions