Additional approaches to large file support in PHP/MySQL environments

Saturday, January 12, 2008, 2:32 AM
Source Code by John (Article #168)

In recent weeks a number of folks have come to the blog from search engines looking for approaches to handling large files in a PHP / MySQL environment. In a previous article, I had covered how to use the PHP.ini and my.cnf files to increase upload sizes.

However, once you have accommodated those solution, sometimes the upload issue isn't a matter of file size permissions.

For example, I am currently tinkering with a project to import and expand the FCC database of television transmitters in North America. This project involves a fairly acceptable size file, approximately 10 Mb or so. But, the procedure for going through the Excel file generated from the FCC files and then converting that spreadsheet into a usable MySQL table is pretty extreme in terms of its memory usage.

Advertisements

Sure, I could chunk the Excel file. But, honestly? Screw that. If I wanted to do things without so much quick automation, I never would have learned how to code! So, here's a more novel and wasteful solution.

In the .htaccess file for your directory, add the following items:

php_value memory_limit 192M
php_value max_execution_time 600

This presumes you have enough server permissions to do this.

In fairness, while the PHP.ini file would also be a tenable place to do this, that comes with a few obvious shortcomings. First, if you already have enough permissions to have your htaccess files treated verbatim by the server, a PHP.ini modification isn't going to make any difference in performance. Second, you need to isolate this kind of memory usage on a web server.

If you opt to use this approach, please create a dark web environment for it. This should be completely off the grid from any potential users or search engine bots. Normally, I don't recommend allocating more than 16 Mb of memory to any PHP script. In truth, I rarely operate any above 8 Mb, unless it involves a serious image upload system.

But, it is valuable as an approach to handling large scripts and scripts that eventually eat lots of memory. For example, with my FCC database conversion, you're looking at dumping all the information the FCC currently has about every license, application and construction permit for every transmitter in all of North America and all the US and Canadian territories. If you run through that whole Excel spreadsheet in a single shot, you're going to produce a gigantic memory footprint.

Of course, as a daily user-level thing, I would never do something like this. It risks bogging down or crashing the server, as I'm unwilling to make the necessary investment to maintain this sort of processing power just for web pages.

But, for a couple uses every week, if you isolate this in a protected directory or a distinct subdomain off the grid, this can be very useful.

Mail article to a friend

Tools

Check Google PageRank

Recent articles

Welcome!

Wonder where to start with your web design business?

This blog follows along with my efforts to build and grow a website design business, Pro Content and Design.

The goal of this blog is to fill in blanks that may be empty as you get your business rolling.

This blog, particularly the source code section, is not intended for beginners. If you are not comfortable with databases, Ajax, DOM objects and other advanced methods, I strongly suggest you go take a look over at W3 Schools before even reading -- let alone tinkering with -- any of the code here.

I hope this blog has some value to web designers as they attempt to get their businesses going.

Good luck, and happy reading.

Thank you,
John Crawford
Pro Content and Design