MySQL .csv export script. Need to include column names.

[mrwood]

I have looked everywhere for a solution but my lack of php and mysql knowledge has left me pulling my hair out =)

 

I am trying to export a mysql table to a .csv including the column names. I managed to get the .csv to export and is formatted great, except I cannot get the column names included as the first row of the .csv file.

 

Here is the script I am using which I found online:

 

<?

$host = 'localhost'; //your Hostname

$user = 'xxxxxxxx'; //Database Username

$pass = 'xxxxxxxx'; //Database Pasword

$db = 'mapdb'; //Database name

$table = 'maptable'; //Table name

$file = 'csvexport'; //CSV File name

$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."");

$i = 0;

if (mysql_num_rows($result) > 0) {

while ($row = mysql_fetch_assoc($result)) {

$i++;

}

}

$values = mysql_query("SELECT * FROM ".$table."");

while ($rowr = mysql_fetch_row($values)) {

for ($j=0;$j<$i-1;$j++) {

$csv_output .='"'.$rowr[$j]."\",";

}

$csv_output .='"'.$rowr[$j]."\"";

$csv_output .= "\n";

}

$fh = fopen($file.'.csv', 'w') or die("can't open file");

fwrite($fh, $csv_output);

fclose($fh);

readfile('csvexport.csv');

if(file_exists($file.'.csv')){return (1);}else{return(0);}

?>

 

Any help is greatly appreciated!

 

[sunfighter]

I am going to try and do this the way you have started and will try to explain what i did. First I'm not going to show the login for the database so the file starts at line : $table = 'maptable';  //Table name

First I added this line on top: $csv_output = ""; because the first time you use the string we get an error. Your adding to a string that is not defined so this defines it. And changed this line: $file = 'csvexport.cvs'; I added the extension here (easier) and changed the lines in code to reflect this.

Next  all this:

$result = mysql_query("SHOW COLUMNS FROM ".$table."");

$i = 0;

if (mysql_num_rows($result) > 0) {

echo mysql_num_rows($result).'<br />';

while ($row = mysql_fetch_assoc($result)) {

$i++;

}

}

is just taking up space and time setting $i to the number of columns. I deleted it and used this:

$query = "SELECT * FROM $table";

$result = mysql_query($query);

$i = mysql_num_fields($result);    this line is a lot less work.

 

At the end of the code I removed : readfile('csvexport.cvs'); because the display has no line feeds and looks bad.

And : if(file_exists($file.'.csv')){return (1);}else{return(0);} If you want it back do it. A simple echo file saved is good here.

 

lastly you aren't writing headers cause you never got the headers.  $rowr = mysql_field_name($result, $j); will get them.

 

Here's the final code:

----------------------------

 

<?php

$csv_output = "";

$table = "clan_registry";  //Table name

$file = 'csvexport.cvs';    //CSV File name

 

$query = "SELECT * FROM $table";

$result = mysql_query($query);

$colCount = mysql_num_fields($result);  //this is the colCount

$i = $colCount;

 

for($j = 0; $j < $i; $j++)

{

$rowr = mysql_field_name($result, $j);

$csv_output .= '"' . $rowr . "\",";

}

//$csv_output .= '"' . $rowr. "\"";

$csv_output .= "\n";

 

while ($rowr = mysql_fetch_row($result))

{

for ($j=0;$j<$i-1;$j++)

{

$csv_output .='"'.$rowr[$j]."\",";

}

$csv_output .='"'.$rowr[$j]."\"";

$csv_output .= "\n";

}

$fh = fopen($file, 'w') or die("can't open file");

fwrite($fh, $csv_output);

fclose($fh);

?>

 

 

[mrwood]

Thank you very much for helping me out! I really appreciate your help and explaining exactly what was going on with the code itself.

[mrwood]

Actually after running the script there is a slight error in the column names. For some reason it leaves a ',' (comma) at the end of the last column name. I can't figure out how to get the script to leave the last column name without a ',' at the end when it creates the .csv file. I greatly appreciate the help.

 

Here is the entire code I am using:

 

<?php

$host = 'localhost'; //your Hostname

$user = 'xxxxx'; //Database Username

$pass = 'xxxxx'; //Database Pasword

$db = 'xxxxx'; //Database name

$csv_output = "";

$table = 'maptable'; //Table name

$file = 'csvexport.csv';    //CSV File name

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

mysql_select_db($db) or die("Can not connect.");

$query = "SELECT * FROM $table";

$result = mysql_query($query);

$colCount = mysql_num_fields($result);  //this is the colCount

$i = $colCount;

 

for($j = 0; $j < $i; $j++)

{

  $rowr = mysql_field_name($result, $j);

  $csv_output .= '"' . $rowr . "\",";

}

//$csv_output .= '"' . $rowr. "\"";

$csv_output .= "\n";

 

while ($rowr = mysql_fetch_row($result))

{

  for ($j=0;$j<$i-1;$j++)

  {

      $csv_output .='"'.$rowr[$j]."\",";

  }

  $csv_output .='"'.$rowr[$j]."\"";

  $csv_output .= "\n";

}

$fh = fopen($file, 'w') or die("can't open file");

fwrite($fh, $csv_output);

fclose($fh);

readfile('csvexport.csv');

?>

[sunfighter]

Sorry about that. Add this line to the first for loop:

 

 

 

for($j = 0; $j < $i; $j++)

{

$rowr = mysql_field_name($result, $j);

$csv_output .= '"' . $rowr . "\",";

}

$csv_output = rtrim($csv_output, ",");  <-- It was supposed to go here instead of the commented out line...

$csv_output .= "\n";

[mrwood]

It works great now! Thank you very much for all the help!