How to export a file from mysql to csv format?

[kool77]

I would like to export the data from php or mysql to excel format. If I click on a button, then export to csv for excel format and display it on the screen!

How do I do that? I found tutorials from excel to php .. not php to excel

Thank you,

[Accurax]

Are you using phpmyadmin?

[spinal007]

Here you go:
http://www.modwest.com/help/kb6-135.html

A CSV file is a very simple text file in the following format:

Code: Select all

id,name,type
1,Diego,Human
2,Ryan,Robot

Just loop through your records and print the fields you want to export separated by commas, then start a new line and do it again...

In order to make the browser recognize your file as a .CSV file, you'll need to modify a couple of headers. The link above suggests the following:

Code: Select all

    header("Content-type: application/vnd.ms-excel");
   header("Content-disposition: data.xls");

I would actually recommend this:

Code: Select all

header("Content-type: text/csv");
   header("Content-disposition: data.csv");

Because I've know Excel to load all the data in cell A1 if you don't use the proper extension.

[masonbarge]

Here's a mysql to Excel method. I don't export to Excel a lot, but I do format delimited strings a lot. This is off the top of my head so there will be a bug/typo or two.

PHP: Select all

$array1=array();// not necessary but helpful in a long mass of code
$array2=array(); 
$i=0;
while($rows=mysql_fetch_array($result, MYSQL_NUM) {
$array1[$i]=$rows[0];
$array2[$i]=$rows[1];
$i++;
} /* You now have as many arrays as you want, ordered in parallel.  If you
 have NULL fields involved you have to add a little code if you want to
 maintain parallelism.*/ 
$csv1=implode(',',$array1); 

It's that simple. I hope I haven't left something out. Of course, if your imput may include commas, you have to strip them out of the array (str_replace is a lot faster and easier than regex).

You may have to flip them around a bit to conform to your Excel format. I find it easiest to put them on an Excel page first and use Excel to reformat. YMMV.

You should also consider tab delimited fields. I'm finding them easier to deal with than csv's and IIRC Excel actually prefers them. ('\t') Also, I think the mysql default is semicolon field delimiters, but that's a pain when PHP is involved.

This comes in handy for a lot of different situations. It's such an easy tool to experiment with. You can implode/explode in two short lines to try a different delimiter.

I use it for db updates, too. Say you have a db with a LOT of columns, most of which can be NULL. You want to handle a POST-method form that can contain data for any combination of one or more fields. (For non-null columns, you have to add some code. You also have to write exceptions for POST values that don't correspond to database fields.) Give the form inputs names which are the same as the column names. (Actually, you can use an abbreviation for the form and then replace it from a second 'key' db, but I'm trying to keep this simple, so let's assume there is an input with a name identical to each column name.)

The form handler receives a nice $_POST array with the column names as the keys and the data as the value. Making these into two parallel arrays is fast and simple,eg:

PHP: Select all

$i=0;
foreach ($_POST as $k => $v) {
         if(($_POST[$k])&&($k!='submit') [&&($k!='(some other input not related to the database')...] ) {
            $data_array[$i]=$v;
            $heading_array[$i]=$k;
            $i++;
        }
    } 

Now all you have to do is implode these into parallel csv strings to do an insert or update in a single query.

PHP: Select all

$headings=implode(',',$heading_array);
$data=implode[','$data_array];
$query="INSERT dbname($headings) VALUES '$data'"; 

I've tried several ways of doing this, but breaking the keys into a separate parallel array is the most transparent. This works like a charm for UPDATE, as well, with appropriate modifications.

There are several ways to handle non-null columns. It is easiest to deal with them if you group them all at the start of the table. Then require form input for all non-NULL fields (throw an error if the value isn't set or is empty); for non-unique fields, if you want to use a default, just make the default value of the form input the same as the default of the corresponding column (but you'll still want to require a value to be set).

To retain more control, you can always set the values of non-NULL fields specifically. Then when you have massaged them into submission, set $i to the first NULL column. I wouldn't recommend it, but it certainly works, eg:

PHP: Select all

$heading_array[0]='id';
$data_array[0]=''; // special case for auto-incremented primary key
$heading_array[1]="serial_number";
$data_array[1]="$serial_number"; // from $serial_number=$_POST['sn'] or
// whatever.
$i=2;
foreach ($_POST as $k => $v) {
        if(($_POST[$k])&&($k!='submit')&&($k!='username')) {
            $data_array[$i]=$v;
            $heading_array[$i]=$k;
            $i++;
        }
    } 

This is a blessing for large reference databases with lots of columns. It lends itself to easy modularization, either with a couple of simple functions or a class to handle variations in non-database form entries and non-null fields. It scales nicely to new non-null columns and should automatically add the form input as well as the insert/update query; a simple function will set $i limits automatically and create if(isset) expressions for non-null fields. Heck, you could write a function to query the database for non-null and unique columns, handle them, and it would be fully automated for any database with an auto-increment primary key in the first slot.


As you may or may not know, I am building a nutritional database that is up to @80 columns and @7500 rows. This method is invaluable in adding new rows and updating existing rows, where I may want to put values into anywhere from 1 to 79 columns (depending on what information is available for the food). Having NULL columns is an easy way to differentiate between nutrient content of 0 and nutrient content of unknown (important for nutrients people want to avoid, like saturated fat), while allowing me to use small and speedy numeric column-types.

[kool77]

Thanks guys for your replays !!! I tried your codes they are working great and I understand now.

[spinal007]

Another happy customer...