How to save .csv file created on live server to local PC

[GBC48]

I use the following code to write a .csv file from my MySQL database and it works just fin when the db is on my local PC. When I upload everything to the live server I do not get a local copy.

How do I modify my code to get the .csv file created on the live server saved on local PC?

Thanking you in advance for your help.

My code:

$contactsList = @mysql_query("SELECT * FROM contact, location
WHERE contact.mailLocID = location.locID
ORDER BY familyName, firstName");

if (!$contactsList) {
exit('<p>Error retrieving contacts from database<br />' . 'Error: ' . mysql_error() . '</p>');
}

// Get first row of output
$contact = mysql_fetch_assoc($contactsList);

// Built an array of field names
foreach ($contact as $fieldname => $value)
{
$fieldnames[] = $fieldname;
}

// Output to the file
fputcsv($file, $fieldnames, ",", "\"");

// Reset the pointer
mysql_data_seek($contactsList, 0);

// Output the data to the file
while ($contact = mysql_fetch_assoc($contactsList))
{
fputcsv($file, $contact, ",", "\"");
}
fclose($file);

...

 

[jpadie]

add this to the end

readfile($fileName);

 

[GBC48]

Thank you I have done that and the script just stops. Is there someting I am missing?

Best regards,

Glenn.

 

[jpadie]

not really. I assume that you have changed the $fileName to the actual name (and path) of the csv file?

 

[alan92rttt]

You need to set the documents type in the header.

I use code similar to this to get the document header correct.

$string = ""; // this can be a variable string or a row from a sql query or something else...
$filename='carshow';
$ext = "csv"; // file extension
$mime_type = (PMA_USR_BROWSER_AGENT == 'IE' || PMA_USR_BROWSER_AGENT == 'OPERA')
    ? 'application/octetstream'
    : 'application/octet-stream';
header('Content-Type: ' . $mime_type);
if (PMA_USR_BROWSER_AGENT == 'IE')
{
	header('Content-Disposition: inline; filename="' . $filename . '.' . $ext . '"');
	header("Content-Transfer-Encoding: binary");
	header('Expires: 0');
	header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
	header('Pragma: public');
} else {
	header('Content-Disposition: attachment; filename="' . $filename . '.' . $ext . '"');
	header("Content-Transfer-Encoding: binary");
	header('Expires: 0');
	header('Pragma: no-cache');
}

Alan

 

[jpadie]

You need to set the documents type in the header

only if you want it to download as a file attachment. better to get things working properly with the output on the screen, imo.

also if you are shoving the output down the attachment route you must (in good practice) expressly call exit() afterwards to avoid the file data becoming corrupt by extraneous server output.

 

[GBC48]

Thanks guys but I clearly have not got this right - the script doesn't run at all now. Can you tell me what I have got wrong?

My code:

<?php
include_once '../../../2010/gotos/OShoppa.inc.php';

$string = "$fieldnames[]";
$filename='contacts';
$ext = "txt"; // file extension
$mime_type = (PMA_USR_BROWSER_AGENT == 'IE' || PMA_USR_BROWSER_AGENT == 'OPERA')
? 'application/octetstream'
: 'application/octet-stream';
header('Content-Type: ' . $mime_type);
if (PMA_USR_BROWSER_AGENT == 'IE')
{
header('Content-Disposition: inline; filename="' . $filename . '.' . $ext . '"');
header("Content-Transfer-Encoding: binary");
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
} else {
header('Content-Disposition: attachment; filename="' . $filename . '.' . $ext . '"');
header("Content-Transfer-Encoding: binary");
header('Expires: 0');
header('Pragma: no-cache');
}


$contactsList = @mysql_query("SELECT * FROM contact, location
WHERE contact.mailLocID = location.locID
ORDER BY familyName, firstName");
if (!$contactsList) {
exit('<p>Error retrieving contacts from database<br />' . 'Error: ' . mysql_error() . '</p>');
}

// Get first row of output
$contact = mysql_fetch_assoc($contactsList);

// Built an array of field names
foreach ($contact as $fieldname => $value)
{
$fieldnames[] = $fieldname;
}

// Output to the file
fputcsv($filename, $fieldnames, ",", "\"");

// Reset the pointer
mysql_data_seek($contactsList, 0);

// Output the data to the file
while ($contact = mysql_fetch_assoc($contactsList))
{
fputcsv($file, $contact, ",", "\"");
}
fclose($filename);

readfile($filename);

echo '<p>Done</p>';

?>

 

[vacunita]

You have an explicit Parser error in your 4th line which is stopping all execution of the script.

PHP Parse error: syntax error, unexpected ']', expecting T_STRING or T_VARIABLE or T_NUM_STRING in gbc48.php on line 5

This should be showing up unless you have all errors suppressed which should not be the case until you know everything is working.

Fix that first.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

$string = "$fieldnames[]";

that doesn't look right at all. and there are a bunch of other errors too

<?php
include_once '../../../2010/gotos/OShoppa.inc.php';

$filename = 'contacts';
$ext = ".txt"; // file extension


$contactsList = @mysql_query("SELECT * FROM contact, location 
                                WHERE contact.mailLocID = location.locID 
                                ORDER BY familyName, firstName");
if ($contactsList === false) {
    exit('<p>Error retrieving contacts from database<br />'.'Error: '.mysql_error().'</p>');
}

$first = true;

// Get first row of output
while ($contact = mysql_fetch_assoc($contactsList)) {

    $fh = fopen($filename.$ext, 'abt') or die('cannot open file for writing');
    if ($first) {
        // Output to the file
        $fieldnames = array_keys($contact); //fieldnames
        fputcsv($fh, $fieldnames, ",", '"');
        $first = false;
    }
    fputcsv($fh, $contact, ",", '"');
}
fclose($fh);
serveFile($filename.$ext)

function serveFile($fileName) {
    $mime_type = (PMA_USR_BROWSER_AGENT == 'IE' || PMA_USR_BROWSER_AGENT == 'OPERA') ? 'application/octetstream' : 'application/octet-stream';
    header('Content-Type: '.$mime_type);
    if (PMA_USR_BROWSER_AGENT == 'IE') {
        header('Content-Disposition: inline; filename="'.$filename.'"');
        header("Content-Transfer-Encoding: binary");
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
    } else {
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        header("Content-Transfer-Encoding: binary");
        header('Expires: 0');
        header('Pragma: no-cache');
    }
    readfile($fileName);
    exit;
}

 

[GBC48]

Thank you jpadie, I used your code as is but added ?> at the end but it doesn't run so I am obviously way out of my depth here! What am I doing/not doing?

 

[jpadie]

when you say 'doesn't seem to run' you mean that nothing is downloaded? that nothing is created on the file system? that an error message is displayed? that an error is logged? that no error is logged, no message is displayed and no files are either created or downloaded?

 

[GBC48]

I just get a blank screen in the browser.

 

[jpadie]

add this to the top of the script

ini_set("display_errors", true);
error_reporting(E_ALL);

add the following

function debugServeFile($fileName){
  echo htmlspecialchars(file_get_contents($fileName));
}

and change the reference to serveFile reference to debugServeFile

this should output to the screen in lieu of a file.

 

[GBC48]

Thank you jpadie, I have added the code but still get a completely blank page in the browser. The full code is currently:

<?php
ini_set("display_errors", true);
error_reporting(E_ALL);

include_once '../../../2010/gotos/OShoppa.inc.php';

$filename = 'contacts';
$ext = ".txt"; // file extension


$contactsList = @mysql_query("SELECT * FROM contact, location
WHERE contact.mailLocID = location.locID
ORDER BY familyName, firstName");
if ($contactsList === false) {
exit('<p>Error retrieving contacts from database<br />'.'Error: '.mysql_error().'</p>');
}

$first = true;

// Get first row of output
while ($contact = mysql_fetch_assoc($contactsList)) {

$fh = fopen($filename.$ext, 'abt') or die('cannot open file for writing');
if ($first) {
// Output to the file
$fieldnames = array_keys($contact); //fieldnames
fputcsv($fh, $fieldnames, ",", '"');
$first = false;
}
fputcsv($fh, $contact, ",", '"');
}
fclose($fh);
serveFile($filename.$ext)

function debugServeFile($fileName) {
$mime_type = (PMA_USR_BROWSER_AGENT == 'IE' || PMA_USR_BROWSER_AGENT == 'OPERA') ? 'application/octetstream' : 'application/octet-stream';
header('Content-Type: '.$mime_type);
if (PMA_USR_BROWSER_AGENT == 'IE') {
header('Content-Disposition: inline; filename="'.$filename.'"');
header("Content-Transfer-Encoding: binary");
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
} else {
header('Content-Disposition: attachment; filename="'.$filename.'"');
header("Content-Transfer-Encoding: binary");
header('Expires: 0');
header('Pragma: no-cache');
}
readfile($fileName);
exit;
}

function debugServeFile($fileName){
echo htmlspecialchars(file_get_contents($fileName));
}

?>

I am clearly doing something wrong and would appreciate any further help and guidance you can give me.

 

[jpadie]

it is not credible that you get no output. If you are sure that this is the case then likely as not you have not properly configured your webserver. the code you posted was so full of errors that php will have thrown at least a fatal error on execution

<?php
ini_set("display_errors", true);
error_reporting(E_ALL);

include_once '../../../2010/gotos/OShoppa.inc.php';

$filename = 'contacts';
$ext = ".txt"; // file extension


$contactsList = @mysql_query("SELECT * FROM contact, location
                                WHERE contact.mailLocID = location.locID
                                ORDER BY familyName, firstName");
if ($contactsList === false) {
    exit('<p>Error retrieving contacts from database<br />'.'Error: '.mysql_error().'</p>');
}

$first = true;

// Get first row of output
$fh = fopen($filename.$ext, 'abt') or die('cannot open file for writing');
while ($contact = mysql_fetch_assoc($contactsList)) {

    if ($first) {
        // Output to the file
        $fieldnames = array_keys($contact); //fieldnames
        fputcsv($fh, $fieldnames, ",", '"');
        $first = false;
    }
    fputcsv($fh, $contact, ",", '"');
}
fclose($fh);
debugserveFile($filename.$ext)

function serveFile($fileName) {
    $mime_type = (PMA_USR_BROWSER_AGENT == 'IE' || PMA_USR_BROWSER_AGENT == 'OPERA') ? 'application/octetstream' : 'application/octet-stream';
    header('Content-Type: '.$mime_type);
    if (PMA_USR_BROWSER_AGENT == 'IE') {
        header('Content-Disposition: inline; filename="'.$filename.'"');
        header("Content-Transfer-Encoding: binary");
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
    } else {
        header('Content-Disposition: attachment; filename="'.$filename.'"');
        header("Content-Transfer-Encoding: binary");
        header('Expires: 0');
        header('Pragma: no-cache');
    }
    readfile($fileName);
    exit;
}

function debugServeFile($fileName){
  echo htmlspecialchars(file_get_contents($fileName));
}

?>

 

[vacunita]

the code you posted was so full of errors that php will have thrown at least a fatal error on execution

I agree, however if the error is a fatal one, then the calls to ini_set and error_reporting do not get run, and so no errors are shown. So a blank page is not unexpected.

There are still many errors there that need fixing though.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

quite right. oops.