Friday, 19 April 2013

Output database tables in CSV

For lattest updates please visit, http://web2students.com/web2blog/
Code to output all tables with data in CSV (comma seperated version) without using phpmyadmin.
<?php
define("DB","test_db");
define("LB","\n\r");
define("SEPERATOR","\t");
function init(){
    mysql_connect("localhost","root","");
    mysql_select_db(DB);
}

function get_tables(){
    $query = mysql_query("show tables");
    $tables = array();
    $col_name = "Tables_in_".DB;
    while($result=mysql_fetch_assoc($query)){
        $tables[] = $result[$col_name];
    }
    return ($tables);
}

init();

function table_header($table){   
    $out = "";
    $result = mysql_query("SHOW COLUMNS FROM $table");
    while($header = mysql_fetch_assoc($result)){
        $out .= $header['Field'].SEPERATOR;
    }
    $out = substr($out,0,strlen($out)-1);//remove last comma
    return($out);
}

function table_content($table){
    $out = "";
    $query = mysql_query("select * from $table");
    while($result=mysql_fetch_assoc($query)){
        $row = "";
        foreach($result as $column){
            $row .= $column.SEPERATOR;
        }
        $row .= substr($out,0,strlen($row)-1);//remove last comma
        $out .= $row.LB;
    }
    return($out);
}

$out = "";
$tables = get_tables();
foreach($tables as $table){
    $out .= "Data in table $table".LB;
    $out .= table_header($table);
    $out .= LB;
    $out .= table_content($table);
    $out .= LB;
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=output.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $out;
?>

No comments:

Post a Comment