How to Export MySQL Data to Excel in a PHP Function

Written by robert allen
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Export MySQL Data to Excel in a PHP Function
Convert MySQL data to Excel by using PHP to create a comma-separated values file. (Stockbyte/Stockbyte/Getty Images)

Data stored in a MySQL database are easily sortable by computers, but the table file itself may be difficult for humans to read and isn't necessarily compatible with other computers. To share your data easily, you can export MySQL information to a spreadsheet by converting it into what's known as "comma-separated" format, a system of dividing tabular data so that it can parsed by an application like Microsoft Excel.

Skill level:

Other People Are Reading


  1. 1

    Create a new blank text file and name it "export.php." Open this file in a text editor -- or PHP development environment if you have one -- typing "<?php" on the first line and "?>" (do not include the quotation marks) on the last line to demarcate the PHP code -- everything else will be written between those lines.

  2. 2

    Define a PHP link variable using the mysql_connect() function and your MySQL database location, your username and your password. If the database were located at "" and the user "johndoe" had the password "12345," you would type (include the quotation marks):

    $link = mysql_connect("","johndoe","12345").

    This should be the second line in your PHP file, immediately after "<php".

  3. 3

    Select the database with the mysql_select_db() command, placing the name of your database between the parentheses. If the database were called "my_database," you would type the following as the third line of your PHP code (include the quotation marks around the database name):


  4. 4

    Create the variable "$getdata," and use PHP's mysql_query() function to create a valid MySQL query. To select everything from the table "my_table," you would type (include the quotation marks around the MySQL query):

    $getdata = mysql_query("SELECT * FROM my_table");

    Change the query between the parentheses of mysql_query() if you want to export something less than your complete table.

  5. 5

    Copy and paste the following code directly into your PHP file just after line 5 and before the "?>" closing tag, making no changes to the code:

    $rowpr = "";

    while($row = mysql_fetch_array($getdata, MYSQL_NUM)) {

    foreach($row as $el) {

    $rowpr = $rowpr . $el . ",";


    $rowpr = substr($rowpr,0,-1) . "\n";



    echo $rowpr;

  6. 6

    Save the file and close your text editor. If your PHP server is not located on the same computer, transfer the file to the PHP server.

  7. 7

    Open the file in a Web browser; you will see a long string of text consisting of your data. Choose "Save as" (Control-S on most PCs; Command-S on Apple OSX) and save the file with the file extension ".csv". This file can now be opened in Excel, modified and shared with others who also have Excel on their computer.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.