PHP script not exporting as csv showing errors

[Russia]

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:

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

[mac_gyver]

the problem is the OUTPUT on line 5 of config.php. you cannot send any output before sending a header. what is line 5 of your config.php file?

[Russia]

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:

[litebearer]

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

[Russia]

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.

 

 

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++;
    }
}

[litebearer]

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

[Russia]

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

[litebearer]

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.

[Russia]

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

 

[litebearer]

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

[Russia]

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.

[litebearer]

this may help

http://www.geeksengine.com/database/basic-select/column-alias.php

[Russia]

this may help

http://www.geeksengine.com/database/basic-select/column-alias.php

 

I am not doing a SELECT tho, I am doing a SHOW COLUMN

[litebearer]

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)

[Russia]

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

 

[litebearer]

not sure, but try...

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

 

[Russia]

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;
?>

[mac_gyver]

the SHOW COLUMNS query does not do what you are trying.

 

the quickest method of specifying a column order and alias names would be to change your SELECT * query so that it lists the actual columns in the order that you want and the alias names that you want.

[kicken]

<?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).