php csv conversion

What is the macro that you are using?

Your script seem to grab the records from orders table and generate and out put.
However it's not generating a CSV (Comma Separated Values) file exactly, because your field delimiter is a semicolon and a space. CSV files should follow these rules http://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules

Also, the script dumps the output to the default output stream, therefor if you open this in a browser the HTTP headers you have set will cause it to open using a application selected by the end user or be downloaded.
There is no guarantee that this will be opened using excel in the client side.

If you can provide more details on what you are having trouble with, will be able to help more.

What I am trying to do is to output the 'orders' table as a csv file by using the attached file. Eventually I will be passing some variables to the page so it shows specific table data, but for now all I want to do is test it and output the data as a csv file.
Could you tell me what to edit so I can get this to work,
Thanks,

Ok, so I assume you have the database (and server) parameters assigned correctly

$host = 'localhost';

$user = 'root';

$pass = 'billadmin2006';

$db = '1callorders';

$table = 'orders';

Select allOpen in new window

And just running the script as it is exporting data from your "orders" table.

To export other tables by passing a parameter to the script (i.e http://yourdomain.com/example-csv.php?table=customers assuming you have a table named "customers"), Change the code (line 6) as below:

<?php

$host = 'localhost';

$user = 'root';

$pass = 'billadmin2006';

$db = '1callorders';

$table = $_GET['table'];

$file = 'export';

Select allOpen in new window

The problem is that the data is NOT being exported from the page. All I get when I navigate to the php page is the contents of the php  page as an excel spreadsheet. I do not get the contents of the 'orders' table as a spreadsheet

Correction:
It is exported in firefox but when I try to open the file in excel it gives a serious error message and fails to open.
Looks like a format issue ?

I changed the delimiter from ; to , and now it works. Sorry - I did not fully read your response above re. formating

So you got it to work? all ok now?

One last thing:
If I want to be selective in the data and pass a variable called ""sendID" which selects data filtered by ID:

ie http://localhost/example-csv.php?sendID=1

what do I need to add to the example-csv.php page to get it to export this selected data ?

Hai,

Just follow the steps

1)       header("Content-type: application/csv");
      header("Content-Disposition: attachment; filename=".$ca_model.".csv");
        header("Pragma: no-cache");
      header("Expires: 0");

place those line on the top the page

2) Query execution,fetch the records

3) echo the records with comma separator,(i.e) is each filed should be in comma separated vlaues

4) So automatically the comma separated values are taken as field and it is written on the CSV file

<?php

$host = 'localhost';

$user = 'root';

$pass = 'billadmin2006';

$db = '1callorders';

$table = $_GET['table'];

$file = 'export';

$sendID=$_GET['sendID'];



$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());

mysql_select_db($db) or die("Can not connect.");



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

}

}

$csv_output .= "\n";


$sql="SELECT * FROM ".$table;
if(is_numeric($sendID)) $sql.=" where sendID=".mysql_escape_string($sendID);


$values = mysql_query($sql);

while ($rowr = mysql_fetch_row($values)) {

for ($j=0;$j<$i;$j++) {

$csv_output .= $rowr[$j].", ";

}

$csv_output .= "\n";

}



$filename = $file."_".date("Y-m-d_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;

Select allOpen in new window

If I send the following to the file above (renamed to example_filter_csv.php):
http://localhost/development/example_filter_csv.php?sendID=3

I get the following error:
PHP Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Websites\Dreamweaver\Development\example_filter_csv.php on line 29
PHP Warning:  mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\Websites\Dreamweaver\Development\example_filter_csv.php on line 50

It could be because you are not passing the table parameter? Or did you hardcode that?

even if I open the page with no variable passed to it I get this error message

doctorbill

I think what sudarka is asking is if you are passing the table name to the script as well as the sendID.

If you aren't then the SQL statement might be failing because, basically, it doesn't have a table to in the FROM clause.

If that was to happen then mysql_result will return FALSE, which as the error message is saying isn't a valid argument for mysql_num_rows.

By the way, have you considered using SQL to output the results of the query to a csv file?

Something like this perhaps.

SELECT * INTO OUTFILE 'C:\results.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM yourtable;

All I need to pass is the sendID - could you please help to edit the file

When I define the table as orders, I now get the following error message:
The table columns names are being exported in the csv file but no data

PHP Warning:  mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\Websites\Dreamweaver\Development\example_filter_csv2.php on line 50

How do you 'define' the table?

If you want to use a set name for the table just remove the variable stuff and change the SQL.

$sql="SELECT * FROM order";  

This does not work:
When I pass the following:

http://localhost/development/example_filter_csv2.php?sendID=89
I get the following error:
PHP Warning:  mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\Websites\Dreamweaver\Development\example_filter_csv2.php on line 50

I do get the table headings but NO DATA
Please advise

Could you please post the code that isn't working?

The error message isn't really enough information do go on.

Attached below
example-filter-csv2.php

You don't seem to have changed what I suggested.

Oops, didn't notice what you'd done.

Anyway when I change the database and table names the code works fine for me.

A CSV file is created and I can open it in Excel.

There are some errors but they are because I don't pass an argument for SendID and because the variable output_csv isn't initialised.

Oh, and something with the timezone.

Anyway they are all fixable and I'm only seeing them because I've got php set up to display all errors.

I have change the $table='orders';
If I change the line to $sql="SELECT * FROM orders";  
I get the same problem - no data is returned except the table columns
(the table is called orders)

Can you post the working file please

Hi, Sorry for not replying earlier, I was away.

Like imnorie said, the last code you posted is working for me also (given that I change the DB parameters).

try echoing your SQL statement and see if that was constructed correctly.

$sql="SELECT * FROM ".$table;

if(is_numeric($sendID)) $sql.=" where sendID=".mysql_escape_string($sendID);



//Show SQL here and stop the script
echo $sql; exit;



$values = mysql_query($sql);



while ($rowr = mysql_fetch_row($values)) {



for ($j=0;$j<$i;$j++) {



$csv_output .= $rowr[$j].", ";



}



$csv_output .= "\n";



}

Select allOpen in new window

This is the response:
SELECT * FROM orders where sendID=88

how does the script know that the sendID=88 refers to a record ID and that it needs to show that ths information for that record?

The script doesn't know that.

That's an SQL SELECT statement that you are trying to run against the database.

Can you manually run that query in MySQL?

I don't understand. What do I need to add to the script to get it  to understand that when th sendID is passed, it needs to select All Orders that have that ID?

You are passing the ID to the script - the most likely problem is that the SQL statement is failing, and that could be nothing to do with the script really.

Please test the SQL in the database to see if you get any errors.

You should also check the data types of the sendID, if it's a number (eg int) then the SQL you've posted should work, if it's a string/text/character field then you might need to alter the SQL slightly.

@doctorbill, from you comments before I assumed the field in your DB table is also names sendID. If it is not, you'll need to change it to what ever your field name is.

i.e.
SELECT * FROM orders where recordID=88
or
SELECT * FROM orders where orderID=88

also not that when/if you parametrize the table name as you wanted before, the field name will also have to change accordingly.

Still does not make sense. I do not want to hard code the database ID. I want that to come from the URL:

https;//localhost/development/example_filter_csv2.php?sendID=88
The sendID number will be a variable so it will not always be 88

No. sendID is the name of the variable sent to the page. The name of the field in the database is ID

Right, so your SQL construction code should be like below

$sql="SELECT * FROM ".$table;

if(is_numeric($sendID)) $sql.=" where ID=".mysql_escape_string($sendID);

Select allOpen in new window

Now it's working - using the variable name of ID (same as field name in database table)  works.

Good to hear that. Watch out for that field name when you change the table in query.

As usual - experts solved my problem

Glad to help. Thanks for the points.

No problem - thanks to everyone on this