Jump to content

PHP script not exporting as csv showing errors


Russia

Recommended Posts

working on this script but I am having header already sent troubles on lines that allow you to download the csv.

http://pastebin.com/6bTjBri5

An error shows:
 

Warning: Cannot modify header information - headers already sent by (output started at /home/briancqc/public_html/config.php:5) in /home/briancqc/public_html/export.php on line 29

Warning: Cannot modify header information - headers already sent by (output started at /home/briancqc/public_html/config.php:5) in /home/briancqc/public_html/export.php on line 30

Warning: Cannot modify header information - headers already sent by (output started at /home/briancqc/public_html/config.php:5) in /home/briancqc/public_html/export.php on line 31

 


Where would I add a <br> so after every row a new line starts?

How it looks right now: WJWqywX.png

It all goes in a constant line for the export of the rows.
 

Link to comment
Share on other sites

I managed to fix it, there were a few white blank spaces, so i removed them and compacted the file.

 

That seemed to work, but some things didn't, when I downloaded the file, it all is messed up and isn't separated into separate columns and rows

 

Here is how it looks: soKhOo3.png

Link to comment
Share on other sites

just a guess; but try using commas rather than semi-colons to separate your data

Awesome, that worked perfect! One more thing, how would I renamethe row name titles so they look cleaner in the csv output.

 

RacGzog.png

 

Basically the columns, I would like to rename and set in order before downloading:

 

This part:

$result = mysql_query("SHOW COLUMNS FROM " . $table . "");
$i      = 0;

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $csv_output .= $row['Field'] . ",";
        $i++;
    }
}
Link to comment
Share on other sites

Perhaps if the names have a pattern ie "pat_ " you could use

... regex,

or

... substr

or

explode using the underscore, remove the first element from that array, then implode using the underscore

Sounds good, but what if I want to fully rename it? not just remove the first parts of the column name pat_ins= Insurance Company

Link to comment
Share on other sites

Not too difficult; however the compexity would increase dependng upon how may different database tables you are accessing and their column names. ie have an array where the keys are the database field names and the values are the names you want to use.

 

Also if it didn't require too many changes to existing scripts you have in place, simply renaming the database columns would be the easiest way to go.

Link to comment
Share on other sites

The database tables would then have spaces in them and capital and lowercase words. I tried doing
 

$result = mysql_query("SHOW COLUMNS pat_id AS user_id, pat_fname AS first name, pat_lname AS last name FROM " . $table . "");
$i      = 0;

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $csv_output .= $row['Field'] . ";";
        $i++;
    }
}

But that didnt work and instead shows an error:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/briancqc/public_html/export.php on line 12

 

Link to comment
Share on other sites

I believe you need to wrap the alias' with BACK TICS

 

Tried that too:

$result = mysql_query("SHOW COLUMNS `pat_id` AS `user_id`, `pat_fname` AS `first name`, `pat_lname` AS `last name` FROM " . $table . "");

Didnt seem to work.

Link to comment
Share on other sites

I, personally, would rename the columns - using pat_ in the patient table is 'rendundant' as we already know that is the patient table, so any data coming from there doesn't need pat_ in the name, that would allow you to use up to 64 characters for the column names. (use underscore rather than spaces in the names)

Link to comment
Share on other sites

I, personally, would rename the columns - using pat_ in the patient table is 'rendundant' as we already know that is the patient table, so any data coming from there doesn't need pat_ in the name, that would allow you to use up to 64 characters for the column names. (use underscore rather than spaces in the names)

 

I would then have to edit about 5 other scripts that are part of the system thats why I just want to rename it, that is the only time I need it to be neat and nice, just for the exporting of the csv.

$result = mysql_query("SHOW COLUMNS pat_id AS "user_id", pat_fname AS "first name", pat_lname AS "last name" FROM " . $table . "");

Getting this error:

Parse error: syntax error, unexpected T_STRING in /home/briancqc/public_html/export.php on line 9

 

Link to comment
Share on other sites

That seems to show another error:

 

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/briancqc/public_html/export.php on line 15

Warning: Cannot modify header information - headers already sent by (output started at /home/briancqc/public_html/export.php:15) in /home/briancqc/public_html/export.php on line 33

Warning: Cannot modify header information - headers already sent by (output started at /home/briancqc/public_html/export.php:15) in /home/briancqc/public_html/export.php on line 34

Warning: Cannot modify header information - headers already sent by (output started at /home/briancqc/public_html/export.php:15) in /home/briancqc/public_html/export.php on line 35

 

The current code looks like:

<?php

include_once "config.php";

$table = 'patients'; // table you want to export
$file  = 'export'; // csv name.


$query = 'SHOW COLUMNS pat_id AS "user_id", pat_fname AS "first name", pat_lname AS "last name" FROM ' . $table;

$result = mysql_query($query);

$i      = 0;

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $csv_output .= $row['Field'] . ",";
        $i++;
    }
}
$csv_output .= "\n";
$values = mysql_query("SELECT * FROM " . $table . "");

while ($rowr = mysql_fetch_row($values)) {
    for ($j = 0; $j < $i; $j++) {
        $csv_output .= $rowr[$j] . ", ";
    }
    $csv_output .= "\n";
}

$filename = $file . "_" . date("d-m-Y_H-i", time());

header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header("Content-disposition: filename=" . $filename . ".csv");

print $csv_output;
exit;
?>

Link to comment
Share on other sites

<?php

include_once "config.php";

$table = 'patients'; // table you want to export
$file  = 'export'; // csv name.

$sql = 'SELECT pat_id as user_id, pat_fname as `First Name`, pat_lname as `Last Name` FROM '.$table;
$values = mysql_query($sql);
if (!$values){
	die('Query error: '.mysql_error().'<br>'.$sql);
}

$fp = tmpfile();
$first=true;
$len=0;
while ($row=mysql_fetch_assoc($values)){
	if ($first){
		//Write header
		$len += fputcsv($fp, array_keys($row));
		$first=false;
	}
	
	$len += fputcsv($fp, $row);
}

$filename = $file . "_" . date("d-m-Y_H-i", time());

header("Content-type: text/csv");
header("Content-disposition: attachment; filename=" . $filename . ".csv");
header('Content-length: '.$len);
rewind($fp);
fpassthru($fp);
fclose($fp);

exit;
?>
Changes:

- Added error handling, you had none.

- Uses a SELECT with column aliases to define the header cells. You could do it other ways but this is quick and easy

- Uses fputcsv rather than trying to roll your own CSV code.

- Your Content-disposition header was entirely incorrect.

- Include Content-length header so the browser can show a download progress bar (incase you have a large csv file).

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.