betterphp

betterphp does not currently provide support for this item.

16 comments found.

There is a problem with the zip file you have uploaded, it can’t be opened.

This is the error message:

The following invalid filename was encountered in the archive “docs/data_export_helper::set_mode.html”

Ah,

it seems that : are only allowed in file names on Linux systems, I will update this as soon as possible. If you are desperate for a working version send me an email via my profile page and I will email you it.

sorry about that.

no problem, I can wait :)

well its in the update queue.

Thanks, I got the updated zip file. This is a great script, works like a charm :)

Glad you like it, I’m looking into adding the Excel 2007 format too so there may be another update in the next few days. If you can think of any other useful formats let me know, I considered SQL but I’m not sure how useful it would be.

Sorry again about the bug, I guess we know that the review people use Linux or Mac now so somethign came out of it :)

The Excel output worked first time for me with a simple select-where-order by type query.

But unfortunately, like other data export scripts on CC that I’ve tried, it does not seem to handle complex SQL queries.

For example, as far as I can tell, if you have sub-selects in your query it simply doesn’t work. A query with subselects that returns a few hundred rows in my sql query console, simply returns nothing.

I’d be happy to be proved wrong because otherwise this seems to do exactly what it says on the tin and is very simple to use.

The documentation could also do with an example for each export type. I can only see Excel and XML .

I think a sql dump output would be useful as would PDF (offered by other similar scripts on CC). But for $8 I’m not complaining.

I’m not really sure what you mean, this script does not handle SQL at all, you have to format the data into a array before passing it to this script.

I may have misunderstood, can you post the code that didn’t work, if you prefer you can email me via my profile page.

The reason I didn’t include examples of the other export types is that they would be exactly the same code but with a different constant being passed to the set_mode method.

I will add SQL at some point in the next few days, PDF however is not somethign I will add, the main reason being that the developer would want to format it to be specific to their site, which would be a little bit difficult to do if this just output a PDF file with a table on it.

@cfoinfo

After reading your post I decided to run a few more tests:

I have exported data using EXPORT _AS_EXCEL up to the 65535 rows per worksheet limit of MS Excel 2003, without any problems.

After that I tested a CSV export of over 225k rows from a database.

I have also tried all sorts of queries and this script works perfectly… 5 stars :)

Note: If you have seen an error like “Allowed memory size of 134217728 bytes exhausted”, it just means that PHP has exhausted the maximum memory limit it was allowed. This has nothing to do with this script and is easily solved by increasing the memory limit such as using “ini_set(‘memory_limit’, ‘256M’);”.

Glad you like it, unfortunately there is nothing that can be done about the memory really, as you say though its easy to raise the limit if you need to export huge data.

I think you both missed my point.

All I’m saying is that when you populate a php array to export to excel with a sql statement that includes sub selects, in my experience the export doesn’t work. If you take out the sub selects it works fine.

It’s nothing to do with numbers of rows or memory limits. Just an issue with the use of particular sql to populate the array that is passed to the export routine.

e.g. this statement returns correct 174 rows in sql console and in the generated excel export file – great!

$result = mysql_query(‘ select sca_supplier.supplier_id as id, sca_supplier.name as supplier from sca_supplier order by supplier’);

this statement also returns correct 174 rows in sql console but no excel file is generated at all…

$result = mysql_query(‘ select sca_supplier.supplier_id as id, sca_supplier.name as supplier, IF(sca_supplier.supplier_id IN(select supplier_id from sca_resource where audit_id = 1 and doctype = ‘Survey’),’Y’, ‘N’) as received from sca_supplier order by supplier’);

I use a similar, much bigger statement with various sub selects in it to populate the array that supplies a table grid that is also on the same php page and also works fine – displaying 174 rows.

My point is that I just don’t understand why the use of standard sql should cause this issue with the export routine. The same thing happens with 2 other data export scripts I’ve tried on CC, so it’s not just an issue with this one. It makes no sense to me as the export routine shouldn’t care about the sql used to populate the array to export, only that the sql returns something to export.

Otherwise the script works fine and I’ll be using it to export from ‘simpler’ sql as it’s very easy to implement.

You must be supplying an array that is not the expected format, as you said it cant be anything to do with the SQL its self.

If you print out the array using print_r you might spot the problem.

I’ve a problem When i export my DB to excel that it can’t display UTF -8 Could u give me a resolution? Thanks

what do you mean it cant display UTF -8. UTF -8 is a charset so displaying it doesn’t really make much sense.

OK i mean when i export from DB to excel that it can’t display VietNamese language (UTF-8) rightly Could you help fix it???

can you change the charset anywhere in excel ?

I don’t see how this could be caused by the code, but you could try exporting as a CSV file and opening that with excel

I hope you answer my question as soon as

have a little patience, I don’t check this site every day, let alone every hour.

Oh I forgot How could i change field of Mysql to columns of excel of following me when i export excel, and similar with XML Example : Excell : username > User Name when i export to excel XML : Jonh David -> Jonh David

you would have to process the result form the query and form the array in the way you want. This script only deals with creating the excel document from the array you give it. So you just need to give it the right array.,

Can’t get it to work for toffee. I have your example in the site root. It does nothing but return the browser to index.php. How do I go about debugging this?

First make sure you have error_reporting on you can do this by adding

ini_set('display_errors', 'On');
error_reporting(E_ALL);

to the top of the file causing the problem. If that does not show up the problem you can email me and I will take a look for you.

Works as promised on our live server, just not on our local server.

Do you have any examples of this file writing the info from my database into an excel file? It’s simple info: name, email, phone… ??

Yeah, the second example on the live preview page uses a database.

I bought this script. Can you advise on how to export to JSON ? There is no documentation on that.

There is an example on the page for the set_mode method. Essentially you need to do

$export = new data_export_helper($data);
$export->set_mode(data_export_helper::EXPORT_AS_JSON);
$export->export($result);

echo $result;

Thanks, but I saw that one too. It does not seem to be a full example, though, like the one you gave for CSV and Excel. What are the content-type headers that you used? Perhaps you can send me a full working example of exporting a sample array to JSON ? I am having some problems.

The content type should be application/json, but that should noe really matter too much for JSON .

What is the problem you are having ?

Hi,

I do not manage to obtain my array since Mysql.

Do you have an example?

 $data = array(); 
    while ($row = mysql_fetch_array($result)) { 
        array_push($data, $row["colum"]);
    }

Thank you for your assistance.

Mick

Hi,

I have another problem.

The cells cannot be modified.

For example, I cannot modify the format of the dates in the Excel file which is produced.

Do you have an idea?

Thanks again!

Mick

Are you saying the file somewhere and then opening that or just opening it from the site ? It could be that it’s a temporary file and write protected. I don’t have Excel being a Linux user so other than that I don’t really know :S

Hi,

It seems that occurs only with the dates cells.

Is it possible to add a condition in the function to modify the format of dates? As with the format numeric.

if (is_numeric($entry)){
    $xls .= pack("sssss", 0x203, 14, $row, $col, 0x0);
    $xls .= pack("d", $entry);

That functions if I replace the dates like this: 0000-00-00 00.00.00 with 0000-00-00

Thanks again!

Mick

I want to pull the data from a mysql database instead of an array. Can you please show me an example file for this process?

Hi,

Thanks for this nice script. Is it possible to change column width?

The exports works flawless, but de data in de XLS-file is packed together. It would be nice if i could do some formatting…

Thanks!

Anyway to do “in cell” line breaks? Tried common solutions out there and none seem to work with this class.

by
by
by
by
by
by