使用odbc将SQL查询转换为csv - Convert SQL query to csv using odbc

我正试图使用php的odbc连接从非现场的Miscrosoft SQL数据库中获取数据，将针对它的某些查询转换为数组，然后将这些数组转换为csv，我的cms可以读取并导入。我能够成功地连接并从数据库返回一些结果，但我缺乏php和SQL技能，这让我很痛苦

我现在拥有的东西不多，但做了它应该做的事：

    $result = odbc_tables($connect);
    $tables = array();
    while (odbc_fetch_row($result))
    {
        if(odbc_result($result,"TABLE_TYPE")=="TABLE")
        echo"<br>".odbc_result($result,"TABLE_NAME");
    }

网上有关于如何做我想做的事情的明确资源吗？php官方文档似乎是有史以来最没有帮助的文档。一个基本的例子：我想将这里的条目返回为csv格式。我可以用数组格式得到它们：

$query = "SELECT TOP 10 * FROM Communities"; 
    $result = odbc_exec($connect, $query);
    if ( $result )
    {
        while ( ($row = odbc_fetch_array($result)) )
        {
            print_r($row);
        }
        odbc_free_result($result);
    }
    else 
    {
        echo 'Exec error: ' . odbc_errormsg();
    }
    odbc_close($conn);

希望我有更多，但我有点不知道下一步该去哪里。

使用提示，以下是有效的解决方案：

$theArray = array();
while ( ($row = odbc_fetch_array($result)) )
{
    array_push($theArray, $row);
}

$header = array('Name', 'Hours', 'Fees', 'Notes', 'ShortDescription', 'URL');
$fp = fopen('array.csv', 'w');
fputcsv($fp, $header);
foreach ($theArray as $lines) 
{
    fputcsv($fp, $lines);
}

我刚刚完成了您询问的确切项目。我运行的是php5.2，因此您可以在新版本中更容易地处理csv文件。这是我的代码：

<?php
// Uncomment this line for troubleshooting / if nothing displays
ini_set('display_errors', 'On');
$myServer = "GSRBI";
$myUser = "webuser";
$myPass = "Webuser1";
$myDB = "GSRBI";
$dbhandle = odbc_connect($myServer, $myUser, $myPass)
    or die("Couldn't connect to SQL Server on $myServer"); 
$return = odbc_exec($dbhandle, 'select * from GSRBI.dbo.BounceBackEmail');
$subscribers_array = array(); 
$db_row = '';
$arrayrow = 0;
while ( $db_row = odbc_fetch_array($return) )
{
    $arrayrow++;
    $array[] = array(
                        'card_num' => $db_row['PlayerAccountNumber']
                        ,'last_name' => ucfirst(strtolower($db_row['LastName']))
                        ,'first_name' => ucfirst(strtolower($db_row['FirstName']))
                        ,'email' =>  $db_row['EMailAddress']
                        ,'earned_on_date' => date('m/d/Y', strtotime('-1 days'))
                        ,'free_play' => $db_row['Offer1']
                        ,'valid_through_date' => date('m/d/Y', strtotime('+15 days'))
                    );
    }
echo print_r($arrayrow, true); ## display number of rows for sql array
echo " rows in ODBC ";
// Creates an array with GSR webteams contact info
    $array1[] = array( 
                        'card_num' => "123456789"
                        ,'last_name' => "GSRwebteam"
                        ,'first_name' => "GSRwebteam"
                        ,'email' => "webteam@something.com"
                        ,'earned_on_date' => date('m/d/Y', strtotime('-1 days'))
                        ,'free_play' => "9"
                        ,'valid_through_date' => date('m/d/Y', strtotime('+15 days'))
                    );
$result = array_merge((array)$array, (array)$array1); ## merge the two arrays together

// This will convert the array to csv format then save it
 ## Grab the first element to build the header
$arr = array_pop( $result );
$temp = array();
    foreach( $arr as $key => $data ) 
    {
        $temp[] = $key;
    }
$csv = implode( ',', $temp ) . "'n";
$csv .= to_csv_line( $arr ); ## Add the data from the first element
    foreach( $result as $arr ) ## Add the data for the rest
    {   
        $csv .= to_csv_line( $arr );
    }
//echo print_r($csv, true); ## Uncomment to test output1
$f = fopen('reports/bounceback-'.date('m-d-Y').'.csv', "w");  
fwrite($f, $csv);  
fclose($f);
Echo "The report has ran";
return $csv;
function to_csv_line( $result ) 
    {
    $temp = array();
        foreach( $result as $elt ) 
        {
            $temp[] = '' . addslashes( $elt ) . '';
        }
        $string = implode( ',', $temp ) . "'n";
    return $string;
    }