How to export query result to csv file?

Question

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%

@ using toad for oracle you can do it .. is that option help you if yes ill tell you how to do it — sam
– sam, Commented Feb 25, 2017 at 13:55
@Gordon thank you but i'm new to programming just stumbled table-csv topic and i wonder if it can do this too would be very useful in the way — kenn
– kenn, Commented Feb 25, 2017 at 14:08

Emeeus · Accepted Answer · 2020-05-19 18:56:57Z

1

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);
}

edited May 19, 2020 at 18:56

Emeeus

5,2922 gold badges30 silver badges42 bronze badges

answered Feb 25, 2017 at 14:03

Davinder Kumar

315 bronze badges

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

1 Comment

kenn Over a year ago

it works but it output everything in database how to make it look like the above echo

Virgilio Falla · Accepted Answer · 2017-02-25 14:06:31Z

0

// 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

answered Feb 25, 2017 at 14:06

Virgilio Falla

76 bronze badges

3 Comments

kenn Over a year ago

but $ratio is not in database table? ok i will try i hope it does

kenn Over a year ago

it outputs everything in table

Virgilio Falla Over a year ago

use isnull(column_name,'0') as column_name in select query for handle nulls and that's the final tip to remove errors enjoy

kenn · Accepted Answer · 2017-02-25 15:17:05Z

0

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

answered Feb 25, 2017 at 15:17

kenn

11 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to export query result to csv file?

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related