Looking to Build/Redesign a Website or Blog ?

To speak to Rakshit, call +91 968 704 2303

Export MYSQL data into Excel/CSV via php

Hello Friends,

Today i came across a functionality where i need to Export the MYSQL data into CSV/Excel file via PHP function/script. There are such requirement where client needs to Export the MYSQL data (Order data,Member data,Newsletter emails etc) into Excel sheet or CSV file for future reference or need to send to other team for future work. You can give a button or link from where client can click on it and get a Excel or CSV file with all data from MYSQL database tables using(through) PHP.

Here i am sharing a function using which you can easily export the MYSQL data into Excel/CSV with a single click on button or link. If you are looking to export the filtered data than you can pass parameters into function and make a sql query accordingly. Do you know how to Import CSV/Excel data into MYSQL ?
 

<?php
function export_excel_csv()
{
    $conn = mysql_connect("localhost","root","");
    $db = mysql_select_db("database",$conn);
   
    $sql = "SELECT * FROM table";
    $rec = mysql_query($sql) or die (mysql_error());
   
    $num_fields = mysql_num_fields($rec);
   
    for($i = 0; $i < $num_fields; $i++ )
    {
        $header .= mysql_field_name($rec,$i)."\\t";
    }
   
    while($row = mysql_fetch_row($rec))
    {
        $line = '';
        foreach($row as $value)
        {                                           
            if((!isset($value)) || ($value == ""))
            {
                $value = "\\t";
            }
            else
            {
                $value = str_replace( '"' , '""' , $value );
                $value = '"' . $value . '"' . "\\t";
            }
            $line .= $value;
        }
        $data .= trim( $line ) . "\\n";
    }
   
    $data = str_replace("\\r" , "" , $data);
   
    if ($data == "")
    {
        $data = "\\n No Record Found!\n";                       
    }
   
    header("Content-type: application/octet-stream");
    header("Content-Disposition: attachment; filename=reports.xls");
    header("Pragma: no-cache");
    header("Expires: 0");
    print "$header\\n$data";
}
?>

What you need to do is…
1) Copy above function and paste it into your file.
2) Change MYSQL connection settings in mysql_connect("localhost","root","").
3) Change database name in mysql_select_db("database",$conn)
4) Change table name in $sql = "SELECT * FROM table".
5) Thats it.

Let me know your thoughts for the same. If you face any problem in this than let me know via comment or contact us form.

To know more about programming,JavaScript issues,jQuery,Expression Engine,MYSQL database and Open-source, enter your email address below. We will send you free tutorials.

Enter your email address:
 
Feel free to ask any question. Just leave your comment below and we will answer your comment with in 24 hours.
Share:

62 Comments

  • when i use this code it simply print the db data not exporting excel .what can i do now

  • sojourner

    Where will the excel file be and name of the excel file?

  • anup

    when i use this code it simply print the db data not exporting excel .what can i do now

  • Rohan Mathew

    Hi
    I already have tried the above code and it is working perfectly fine. But what i need is to add multiple conditions for the sql query say for example if it is today then export today’s data otherwise yesterday’s. i am having problem when pass if and else condition on the sql query. when i click the button file does not download to excelsheet but it is displayed on the screen. I would be thankful to you if you help me on this problem

  • Gladys

    I have a file with a field that is a big text (description) and it is not getting in one row it goes to the next row and it is not easy to understand . Can I export big large size text to an excel>??

Leave a Reply

Your email address will not be published.