Trying to take results of query, create CSV and write it to file... close but stuck

Hi,
I'm querying my db with the intention of creating a csv file and saving it to a directory. I came across some code to get it to csv format (currently its prompting to download the result ie:
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');

The query is getting the info I am after now, but how can I amend the code to write to file instead of prompt to download the csv?
<?php

$query = sprintf('SELECT jos_vehicles_dealers.dealer_name AS dealer, jos_vehicles.vin_number AS vin, jos_vehicles.construction_year AS retail_year, jos_vehicles_makes.make_name AS retail_make, jos_vehicles_models.model_name AS retail_model, jos_vehicles.trim, jos_vehicles.mileage, jos_vehicles.cylinder_count AS engine, jos_vehicles.transmission_type AS transmission, jos_vehicles.drive AS driveType, jos_vehicles.vehicle_price AS price, jos_vehicles.discount_price AS discount, jos_vehicles.id AS pictureURL from jos_vehicles left join jos_vehicles_dealers on jos_vehicles.dealer_id = jos_vehicles_dealers.id left join jos_vehicles_makes on jos_vehicles.make_id = jos_vehicles_makes.id left join jos_vehicles_models on jos_vehicles.model_id = jos_vehicles_models.id where jos_vehicles.published = 1');
$result = mysql_query($query, $conn) or die(mysql_error($conn));


header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');

/*
 * output header row (if at least one row exists)
 */

$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

/*
 * output data rows (if atleast one row exists)
 */

while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}

/*
 * echo the input array as csv data maintaining consistency with most CSV implementations
 * - uses double-quotes as enclosure when necessary
 * - uses double double-quotes to escape double-quotes 
 * - uses CRLF as a line separator
 */

function echocsv($fields)
{
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
        echo $separator . $field;
        $separator = ',';
    }
    echo "\r\n";
}
?>

Open in new window

LVL 1
tjyoungAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
For security reasons, you cannot written to users computer from web  page ..

Long answer is that you can, using client side components (certified) ...
0
tjyoungAuthor Commented:
Papery I meant writing to a directory on the server
0
tjyoungAuthor Commented:
Meant to say sorry
Damn spellcheck
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

GaryCommented:
The basics of it are this, but I'm not sure what you are writing but you should get the gist of it.

$handle = fopen("myfile.csv", 'w');
$separator = ',';

foreach($fields as $field){
	if (preg_match('/\\r|\\n|,|"/', $field)) {
		$field = '"' . str_replace('"', '""', $field) . '"';
	}
	$item = $separator . $field;

	fwrite($handle, $item);
	fflush($handle);
}
fclose($handle);

Open in new window

0
Ray PaseurCommented:
Fopen(), Loop to retrieve records, Fputcsv(), Fclose() at end of record set.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tjyoungAuthor Commented:
Seems simple enough but I tried the first suggestion and only getting one line like this (starting with a comma):
,Steele Chrysler,JN8BT08V46W212295,2006,NISSAN,X-TRAIL,BONAVISTA,103231,2.5L 4 Cylinder Engine,Automatic,All Wheel Drive,9980,0,8779,P59703A

<?php

$conn = mysql_connect('localhost', 'username', 'password') or die(mysql_error());
mysql_select_db('steele_main', $conn) or die(mysql_error($conn));

$query = sprintf('SELECT jos_vehicles_dealers.dealer_name AS dealer, jos_vehicles.vin_number AS vin, jos_vehicles.construction_year AS retail_year, jos_vehicles_makes.make_name AS retail_make, jos_vehicles_models.model_name AS retail_model, jos_vehicles.trim, jos_vehicles.mileage, jos_vehicles.cylinder_count AS engine, jos_vehicles.transmission_type AS transmission, jos_vehicles.drive AS driveType, jos_vehicles.vehicle_price AS price, jos_vehicles.discount_price AS discount, jos_vehicles.id AS pictureURL, jos_vehicles.stock_number AS LocationUrl from jos_vehicles left join jos_vehicles_dealers on jos_vehicles.dealer_id = jos_vehicles_dealers.id left join jos_vehicles_makes on jos_vehicles.make_id = jos_vehicles_makes.id left join jos_vehicles_models on jos_vehicles.model_id = jos_vehicles_models.id where jos_vehicles.published = 1');
$result = mysql_query($query, $conn) or die(mysql_error($conn));


//header('Content-Type: text/csv');
//header('Content-Disposition: attachment;filename=export.csv');

/*
 * output header row (if atleast one row exists)
 */

$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

/*
 * output data rows (if atleast one row exists)
 */

while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}

/*
 * echo the input array as csv data maintaining consistency with most CSV implementations
 * - uses double-quotes as enclosure when necessary
 * - uses double double-quotes to escape double-quotes 
 * - uses CRLF as a line separator
 */

function echocsv($fields)
{
$handle = fopen("myfile.csv", 'w');
$separator = ',';

    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
&#9;$item = $separator . $field;
&#9;fwrite($handle, $item);
&#9;fflush($handle);
    }
    fclose($handle);
}
?>

Open in new window

0
GaryCommented:
function echocsv($fields)
{

$separator = ',';
$item="";
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
	$item += $separator . $field;

    }
	fwrite($handle, substring($item,1));
}

Open in new window


Amend your $row loop like so
$handle = fopen("myfile.csv", 'w');
while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}
fclose($handle);

Open in new window

0
tjyoungAuthor Commented:
Tried the second version but I'm sure I'm missing something obvious. Writes a file but no data in it:
<?php

$query = sprintf('SELECT jos_vehicles_dealers.dealer_name AS dealer, jos_vehicles.vin_number AS vin, jos_vehicles.construction_year AS retail_year, jos_vehicles_makes.make_name AS retail_make, jos_vehicles_models.model_name AS retail_model, jos_vehicles.trim, jos_vehicles.mileage, jos_vehicles.cylinder_count AS engine, jos_vehicles.transmission_type AS transmission, jos_vehicles.drive AS driveType, jos_vehicles.vehicle_price AS price, jos_vehicles.discount_price AS discount, jos_vehicles.id AS pictureURL, jos_vehicles.stock_number AS LocationUrl from jos_vehicles left join jos_vehicles_dealers on jos_vehicles.dealer_id = jos_vehicles_dealers.id left join jos_vehicles_makes on jos_vehicles.make_id = jos_vehicles_makes.id left join jos_vehicles_models on jos_vehicles.model_id = jos_vehicles_models.id where jos_vehicles.published = 1');
$result = mysql_query($query, $conn) or die(mysql_error($conn));


//header('Content-Type: text/csv');
//header('Content-Disposition: attachment;filename=export.csv');

/*
 * output header row (if atleast one row exists)
 */

$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

/*
 * output data rows (if atleast one row exists)
 */

while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}

/*
 * echo the input array as csv data maintaining consistency with most CSV implementations
 * - uses double-quotes as enclosure when necessary
 * - uses double double-quotes to escape double-quotes 
 * - uses CRLF as a line separator
 */

function echocsv($fields)
{
$fp = fopen('file.csv', 'w');

foreach ($fields as $field) {
    fputcsv($fp, $field);
}
}
fclose($fp);

?>

Open in new window

0
GaryCommented:
See my snippet just above.
0
tjyoungAuthor Commented:
I'm trying this but getting a file with no data:
$query = sprintf('SELECT jos_vehicles_dealers.dealer_name AS dealer, jos_vehicles.vin_number AS vin, jos_vehicles.construction_year AS retail_year, jos_vehicles_makes.make_name AS retail_make, jos_vehicles_models.model_name AS retail_model, jos_vehicles.trim, jos_vehicles.mileage, jos_vehicles.cylinder_count AS engine, jos_vehicles.transmission_type AS transmission, jos_vehicles.drive AS driveType, jos_vehicles.vehicle_price AS price, jos_vehicles.discount_price AS discount, jos_vehicles.id AS pictureURL from jos_vehicles left join jos_vehicles_dealers on jos_vehicles.dealer_id = jos_vehicles_dealers.id left join jos_vehicles_makes on jos_vehicles.make_id = jos_vehicles_makes.id left join jos_vehicles_models on jos_vehicles.model_id = jos_vehicles_models.id where jos_vehicles.published = 1');
$result = mysql_query($query, $conn) or die(mysql_error($conn));



$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

$handle = fopen("myfile.csv", 'w');
while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}
fclose($handle);
                                            


function echocsv($fields)
{

$separator = ',';
$item="";
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
	$item += $separator . $field;

    }
	fwrite($handle, substring($item,1));
}
?>

Open in new window

0
GaryCommented:
I'm not sure about this, maybe Ray will confirm if you can make an object global in a function.
but try the bolded amendment.

function echocsv($fields)
{
global $handle;
$separator = ',';
0
tjyoungAuthor Commented:
Hi,
I've dumped 99% of my overly complicated copy and paste code and am now working with this much simpler method (thanks Ray).
My only question now is: how can I retain the column headers? It makes the file great but I need to keep my column headers in there.
$query = sprintf('SELECT jos_vehicles_dealers.dealer_name AS dealer, jos_vehicles.vin_number AS vin, jos_vehicles.construction_year AS retail_year, jos_vehicles_makes.make_name AS retail_make, jos_vehicles_models.model_name AS retail_model, jos_vehicles.trim, jos_vehicles.mileage, jos_vehicles.cylinder_count AS engine, jos_vehicles.transmission_type AS transmission, jos_vehicles.drive AS driveType, jos_vehicles.vehicle_price AS price, jos_vehicles.discount_price AS discount, jos_vehicles.id AS pictureURL from jos_vehicles left join jos_vehicles_dealers on jos_vehicles.dealer_id = jos_vehicles_dealers.id left join jos_vehicles_makes on jos_vehicles.make_id = jos_vehicles_makes.id left join jos_vehicles_models on jos_vehicles.model_id = jos_vehicles_models.id where jos_vehicles.published = 1');
$result = mysql_query($query, $conn) or die(mysql_error($conn));

$fp = fopen('file.csv', 'w+');
if ($fp && $result) 
{     
       while ($row = mysql_fetch_row($result)) 
       {
          fputcsv($fp, array_values($row)); 
       } 
}
?>

Open in new window

0
Ray PaseurCommented:
You can fetch the first row and use it to get the column names with array_keys().  Then you can use mysql_data_seek() to reset the results set pointer so that you can fetch all of the rows.

This article may save you from a future catastrophe.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.