I regularly export table data from MySQL using PHP, however a question on StackOverflow.com came up where the user wanted to export data from MS SQL rather than MySQL.

Handily, PHP has support for many of the same functions for MS SQL as it does for MySQL.

So with a bit of reworking I was able to come up with the following script;

    

    $servername = '.SQLSERVER';

    $username = 'databaseuser';

    $password = 'database';

    $database = 'CriticalPath';

    $table = 'Users';

 

    if (!function_exists('mssql_fetch_row'))

    {

        $output .= "MSSQL functions are not available.
n"
;

        exit;

    }

 

    // Connect database

    if(!$dbconnect = mssql_connect($servername, $username, $password))

    {

        $output .= "Connection failed to the host 'localhost'.";

        exit;

    } // if

        

    if (!mssql_select_db($database))

    {

        $output .= "Cannot connect to database '$database'";

        exit;

    } // if

    

    $result=mssql_query("select * from $table");

    

    $out = '';

 

    $columns = mssql_num_fields($result);    

 

    for ($i = 0; $i < $columns; ++$i) {

        // Fetch the field information

        $field = mssql_fetch_field($result, $i);

        $out .= '"'.$field->name.'",';

    }

       

    $out .= "r";

    

    // Add all values in the table to $out.

    while ($l = mssql_fetch_array($result)) 

    {

       for ($i = 0; $i < $columns; $i++) 

       {

          $out .='"'.$l[fusion_builder_container hundred_percent="yes" overflow="visible"][fusion_builder_row][fusion_builder_column type="1_1" background_position="left top" background_color="" border_size="" border_color="" border_style="solid" spacing="yes" background_image="" background_repeat="no-repeat" padding="" margin_top="0px" margin_bottom="0px" class="" id="" animation_type="" animation_speed="0.3" animation_direction="left" hide_on_mobile="no" center_content="no" min_height="none"]["$i"].'",';

       }

 

       $out .= "r";

    }

    

    // Open file export.csv.

    $f = fopen ('export.csv','w');

    

    // Put all values from $out to export.csv.

    fputs($f, $out);

    fclose($f);

    

    header('Content-type: application/csv');

    header('Content-Disposition: attachment; filename="export.csv"');

    readfile('export.csv');

 

    mssql_free_result($result);

 

?>

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

By | 2017-07-24T08:33:18+00:00 January 24th, 2013|CSV, Data, Excel, PHP, SQL Server, Web|0 Comments

About the Author:

Leave A Comment