Convert SQL query to csv using odbc

Question

I'm trying to get data from an off-site Miscrosoft SQL database using php's odbc connection, convert certain queries against it to arrays, and then turn those arrays into a csv that my cms can read and import. I'm able to succesfully conncect and return some results from the database, but my lack of php and SQL skills is killing me.

What I have right now, which is not much, but does what it's supposed to do:

    $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");

    }

Is there any clear resource on the web on how to do what I want to do? The official php documentation seems to be about the most unhelpful documentation ever. A basic example: I want to return the entries here into csv format. I can get them in array format:

$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);

Wish I had more, but I'm a bit lost on where to go next.

Check outfputcsv(), and find supported protocols and wrapper that can be used in fopen(). — Passerby
– Passerby, Commented Jun 24, 2013 at 5:01
Did fputcsv work for you? If not, you could always try implode? $csvline=implode(",",$row); will produce CSV where you have put your print_r($row); — Robert Seddon-Smith
– Robert Seddon-Smith, Commented Jun 24, 2013 at 7:58
worked swimmingly. took a few attempts, but it's working like a charm. — brunam
– brunam, Commented Jun 24, 2013 at 15:55

brunam · Accepted Answer · 2013-06-24 15:57:19Z

2

Using the tips, here's the working solution:

$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);

}

answered Jun 24, 2013 at 15:57

brunam

8352 gold badges14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3447739 · Accepted Answer · 2014-04-01 23:41:28Z

I just got done doing the exact project that you are asking about. I am running php 5.2 so you may be able to deal with the csv file more easily in a newer version. Here is my code:

<?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' => "[email protected]"
                        ,'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;
    }

Collectives™ on Stack Overflow

Convert SQL query to csv using odbc

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related