Convert MySQL to CSV using PHP

[homeslice]

HELP! (& hello!)

 

First post here because I'm tearing what little hair I have left out.

 

I need some help extracting data from a MySQL table and inserting it into a CSV file using a PHP script. I can do this.

 

Problem is, I have a MySQL table with 8 fields. I only want to extract 2 of these fields and their results into the CSV file.

 

Please help.

 

Thanks

[derrick1123]

Can you post what you have so far?

[homeslice]

This is what I have so far.

 

Its modified from some other code and exports perfectly the entire table

 

<?
// Connect database
$database="fyp";
$table="siteform";
mysql_connect("localhost","root","");
mysql_select_db("fyp");

$result=mysql_query("select * from $table");

$out = ''; 

// Get all fields names in table "siteform" in database "fyp".
$fields = mysql_list_fields(fyp,$table);

// Count the table fields and put the value into $columns. 
$columns = mysql_num_fields($fields);


// Put the name of all fields to $out. 
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="n";

// Add all values in the table to $out. 
while ($l = mysql_fetch_array($result)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="n";
}

// Open file export.csv.
$f = fopen ('export.csv','w');

// Put all values from $out to export.csv. 
fputs($f, $out);
fclose($f);

header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
?>

[kenrbnsn]

If you only want to put two of the fields in the table, only retrieve those two fields with your query:

<?php
$q = "select field1,field2 from $table";
$rs = mysql_query($q) or die("Problem with the query [$q]<br>" . mysql_error());
$fp = fopen('export.csv','w');
fwrite($fp,"field1,field2\n");
while ($rw = mysql_fetch_assoc($rs))
    fwrite($fp,'"'.implode('","',$rw) . '"' . "\n");
fclose($fp);
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
?>

 

Ken

[homeslice]

Nice, thanks

 

Problem is, it asks me if I want to download or save the file. I didnt get that before. Any idea how to just create it?

[kenrbnsn]

Remove these lines:

<?php
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename="export.csv"');
readfile('export.csv');
?>

 

Ken

[homeslice]

Thanks Ken, you're a great help.

 

Can I ask one last thing.

 

If I want just the field names and results to print to the screen as CSV data without creating a file, how would I do that?

 

My first snippet of code does this.

[homeslice]

Anyone?

[kenrbnsn]

Use "echo" instead of "fwrite".

 

Ken

[homeslice]

hi Ken,

 

Thanks. I've changed the fwrite to echo but I get an error now saying Unexpected ',' on line 12.

 

Is this all I have to do?

[kenrbnsn]

fwrite uses a certain syntax, echo uses another. You have to use the correct syntax. If you don't know the syntax, use the fine manual for fwrite() and echo.

 

Ken