Export MYSQL data into Excel/CSV via php

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

PHP: Export Database Schema as XML

Sometimes it can be useful to have a dump of the current database schema. The script below reads the schema from a MySQL database and outputs XML that describes the schema.

At first we connect to a MySQL database and use the SHOW TABLES command to return all the tables in the database. Next, we iterate over each table and return the fields for each table using the SHOW FIELDS command. Finally, we put all of the returned information into XML.

Have a look at the code:

<?php

// database constants

// make sure the information is correct

define("DB_SERVER", "localhost");

define("DB_USER", "root");

define("DB_PASS", "password");

define("DB_NAME", "tutorials");

// connection to the database 

$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS) 

   or die("Unable to connect to MySQL"); 

// select a database to work with 

$selected = mysql_select_db(DB_NAME, $dbhandle) 

   or die("Could not select examples"); 

// return all available tables 

$result_tbl = mysql_query( "SHOW TABLES FROM ".DB_NAME, $dbhandle ); 

$tables = array(); 

while ($row = mysql_fetch_row($result_tbl)) { 

   $tables[] = $row[0]; 

} 

$output = "<?xml version=\"1.0\" ?>\n"; 

$output .= "<schema>"; 

// iterate over each table and return the fields for each table

foreach ( $tables as $table ) { 

   $output .= "<table name=\"$table\">"; 

   $result_fld = mysql_query( "SHOW FIELDS FROM ".$table, $dbhandle ); 

   while( $row1 = mysql_fetch_row($result_fld) ) {

      $output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";

      $output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";

   } 

   $output .= "</table>"; 

} 

$output .= "</schema>"; 

// tell the browser what kind of file is come in

header("Content-type: text/xml"); 

// print out XML that describes the schema

echo $output; 

// close the connection 

mysql_close($dbhandle); 

?>

You should note that this code is specific to MySQL database. The commands such as SHOW TABLESand SHOW FIELDS are also available for other databases but are specified slightly differently