Blog Posts

PHP Error Handling and Debugging – Part 1

This article describes the process of testing PHP code.  Using the tips that I will explain can help to decrease the code/test cycle time.

The first thing that you must know in order to plan your code/test process is the environment in which your code will run.

If you have full control of the system, less configuration is required. In this case you can rely on the default settings, and simply need to know where the logs are kept by default. On a typical LAMP (apache) system you can find the log files in /var/log/httpd. Check the documentation for the operating system that you use as some operating systems use a different directory (i.e. one version of Ubuntu uses /var/log/apache2). By default error messages from php will be kept in this directory.

If you are developing on a server where you don’t have access to the default logs, you can configure where your log messages are sent by putting a php.ini file containing the directive

error_log: path_to_log

in the root of the domain.

With this information in mind, we can begin to find code errors.

There are two error types to look for:

  1. parse
  2. runtime

A parse error is the first thing to look for when testing new or modified code. This can be something like a missing semicolon or other grammatical mistake. If a parse error occurs, it will be sent to PHP’s error_log. A simple way to find this kind of error is to load the file directly in a browser (i.e. an AJAX script that would not normally run in a browser could be tested this way for parse errors). With a default PHP installation the parse error will shown on the screen.

Most errors that are encountered are runtime errors. There are two kinds of runtime errors:

  1. exception
  2. functional

The first kind of runtime error happens when a statement or function call that is grammatically correct encounters a unexpected circumstance such as an invalid parameter (i.e. fopen(file_that_doesnt_exist,’r’)). This kind of error can only be seen during an actual run of the code with valid inputs. Opening the file in the browser directly usually will not find it as the inputs will not be those that would typically be encountered. For example opening an AJAX script that relys on the _POST variable for its input will typically not run many of the branches because of the missing _POST variables. To find this error, run the script as it would typically be run and check the error log for errors.

A functional runtime error is when the code runs, doesn’t generate an error, but doesn’t produce the expected outputs. To find this error use one or more of the following techniques:

  • echo/printf
  • error_log
  • try/catch

The simplest way to find errors is by adding echo statements to the code. This method can be somewhat tedious and slower to use than others, but a few well placed echo statements that use print_r to show the value of key return data structures can sometimes quickly illuminate the source of the malfunctioning code. The problem with this method is that because it outputs directly to stdout (the web browser) it is only available if the script can be run in the web browser directly with typical inputs. Many times this is not possible (i.e. for AJAX or cron code).

A more general way of debugging is to use the error_log function instead of echo. With the error_log function you can direct the messages to a file of your choosing with

error_log($message,3,"path/filename")

or to the configured error_log mentioned earlier via

error_log($message)

A bonus when using the error_log() function is that you also get a timestamp for each error logged.

If a runtime error is expected, a try/catch statement should be placed to ignore it or otherwise handle it in a way that doesn’t cause the script to stop abruptly.  This way the script will continue to run and an error will be logged.  This is better because you will know at what section of code the error occurred.  If the blocking error had gone uncaught (in the case of AJAX responder script errors), the calling application might have received a malformed response (parse error).  A try/catch statement is only helpful when a blocking exception occurs, and will not help to debug functional runtime errors.  The structure of this type of code testing is as follows:

try {
 //your new code
 } catch(Exception $E) {
 error_log(E.getMessage());
 }

In this article we have discussed simple code/test cycle techniques for PHP.  Tune in next time for part 2 where we will review using a debugger such as XDebug.

Javascript Compression — Tools and Process

This article is a review of the tools and processes that I have tested and gives plusses and minuses of each.

Software Uncompressed Compressed Percent Comment
Closure Compiler 39K 16K 59% with ADVANCED_OPTIMIZATIONS
YUI Compressor 39K 22K 44%
perl-Javascript-Minifier 39K 25K 36%

 

perl-Javascript-Minifier
Since CPAN library’s Javascript-Minifier and CSS-Minifier are immediately available linux tools they are a good starting point. The Javascript-Minifier is simple to use. Here is a script that you can try to see how it works:

#!/usr/bin/perl
 use JavaScript::Minifier qw(minify);
 my($iFile)=$ARGV[0];
 my($oFile)=$iFile;
 $oFile=~ s/[.]js//;
 $oFile="${oFile}_perl_min.js";
 open(INFILE, "$iFile") or die;
 open(OUTFILE, ">$oFile") or die;
 minify(input => *INFILE, outfile => *OUTFILE);
 close(INFILE);
 close(OUTFILE);

In my tests, it didn’t break my code, but did generate errors because of incorrectnesses in my code. I used the google chrome jslint plugin to find the errors. jslint only works on pure javascript, but strings are not parsed. Thus you can use php to initialize variables by putting the php code inside of quotes, and still check it with jslint.

 

YUI Compressor
The YUI Compressor is Yahoo’s library, and works better than perl-Javascript-Minifier. Here is an example command for using YUI Compressor:

java -jar yuicompressor-2.4.7.jar --type js -o filename_yui_min.js filename.js

A nice feature of the yuicompressor is that it can accept javascript strings from the command line. This makes it simple to script. It’s goal is to not break code, and in my tests this was observed to be true.

 

Closure Compiler
The google closure compiler is the most advanced of the ones that I tested. It has a simple mode that doesn’t break code and an option for ADVANCED_OPTIMIZATIONS that produces very compressed code. Here is an example command for using the closure compiler in simple mode:

java -jar compiler.jar --js filename.js --js_output_file filename_closure_min.js --externs externs.js

And similarly for advanced mode:

java -jar compiler.jar --compilation_level ADVANCED_OPTIMIZATIONS --js filename.js --js_output_file filename_closure_min.js --externs exterms.js

Similar to perl-Javascript-Minifier, closure compiler only works on pure javascript files. Because of the effectiveness of the optimizations that it does, it can break code. To effectively use it, you need to design your javascript with minification in mind. Typically you want to use your javascript as a library (i.e. as handers for events such as mouse clicks) to do this, you need to add a small amount of code that preserves the function names that will be available to external scripts. Similarly if you want to use external libraries in your library, you need to add extern declarations that will preserve the external symbols. There are less modifications required if you use the simple mode than for the advanced mode. I wanted to use advanced mode for some script that contains jQuery calls (including jQuery Mobile), but wasn’t able to find a way to preserve the jQuery and $ symbols. I tried using –externs with the externs file available as an addon from google svn, but this didn’t solve the problem. Therefore I recommend using simple mode for files containing jQuery and advanced mode for files that do not.

 

In summary of the tools reviewed google closure compiler is the most effective, perl-Javascript-Minifier is the least likely to break code, and yuicompressor is a compromise between these extremes. Additionally each of these tools can be run locally on your machine.

Open Document Format for Dynamic Spreadsheet Compilation

This article gives an overview the process of using Open Document format (ODF) to create spreadsheets. I will describe in general the ODF format, pros and cons, and process of developing such a library.

Open Document Version 1.2 was used for this project (the latest Version of ODF available at the time of this writing is 1.3).

During the course of developing spreadsheet data export capability for a recent project, I discovered Open Document Format. Open Document Format is a xml approach to document creation. It is used to great effect by OpenOffice.org (http://www.openoffice.org).

ODF Overview:

A minimal .odf format spreadsheet file contains the following files/directories:

  • content.xml
  • styles.xml
  • meta.xml
  • mimetype
  • META-INF/manifest.xml

The most important files of this set are content.xml (which contains all of the data and some style markup) and styles.xml (which contains additional style markup not contained in content.xml).

ODF Pros and Cons:

Pros:

  • Writing ODF format is computationally fast since it merely adds xml markup to the plain text data.
  • ODF is readable and writeable by Microsoft Excel and OpenOffice.org as well as many other applications ().
  • Small file size (the xml fileset is stored in a zip archive).
  • Many of the features of Microsoft Excel are supported including:
    • Header/Footer
    • Paper Size and Orientation
    • Cell/Row/Column Style (color,font size, border, merged cells)
    • Page Scale During Print

Cons:

  • Microsoft Excel 2007’s implementation although viable is less robust than that offered by OpenOffice.org.
  • Missing support for Excel macros means that computations must be done on the server side and results in a static spreadsheet document
  • Differences in formulas between Excel and OpenOffice.org mean that the document must effectively be written for Excel or the other applications or that computations must be done on the server side (i.e.: openoffice SUM(1; 2; 3), excel SUM(1,2,3))

Library Design and Implementation:

My data export library was based on OpenOffice.org’s approach, and follows this http://develop.opendocumentfellowship.com/ tutorial. It was designed using a cookie-cutter type approach that involves creating a template file using OpenOffice.org, extracting the strings that contain the overall file format, extracting the strings that contain the style, and repetatively substituting the composite string for each row of the excel. When new formats or features were needed, the feature was selected in OpenOffice.org, the output file examined, and the relevant strings extracted and templated as necessary. This approach requires some time, because once the feature has been selected and added to the source the output must then be tested with Microsoft Excel to ensure compatibility. A better approach would be to select the feature in Microsoft Excel and then test with OpenOffice.org. This is because OpenOffice.org accepts the Excel format most of the time whereas the converse is not true.

Another more flexible and long-term approach to constructing this library would be to create a grammer based on the xml tags, and write the xml directly. I have also read about others creating ODF libraries using .odt (open document template).

Guidelines for writing great articles

Part of my efforts with the Mississippi Magic Magazine involve setting up guidelines for writing great articles.  If you are wondering why a technologies consultant is posting guidelines on writing great articles then let me explain.  We are in the business of creating success in our constituents that we partner with through consulting and development.  That being said… Mississippi Magic Magazine provides an outlet of expression for people and businesses in Mississippi but it does not employ writers to produce magazine content.  Therefore, all the writing done on the Mississippi Magic Magazine is done by volunteer writers that have a vested interest in the community.  From business owners to pastors of churches, people active in leadership in the community provide all of the content found in magazine.  To make this a profitable venture for each of them, we’ve found it necessary to publish a guidelines for writing great articles.

Benefits:

  • People like to do business with those they are familiar with.   Through connecting with our community through your articles, you’ll lower the barriers that people have to traditional broadcast style advertising communications.  .
  • Articles will be broadcasted to the RSS subscription readership.
  • Articles will be indexed by search engines so that anyone looking the information you provided will find it and you on the internet.
  • Articles will spread in a viral method through through social media sites such as twitter, facebook, and linked in.
  • Unlike traditional media, articles written will be available online well into the indefinite future.
  • Articles will link your thoughts, expertise, and methods to your perceived value as a solutions provider and allow people to reach you.
  • Articles always make the front page when they are published.
Good practices:
  • Offering needed information to make an informed decisions.
  • Offering solutions by showing how to solve a problem.
  • Providing lists of methods or resources.
  • Expounding upon a subject.
  • Offering information about events by featuring information, pictures, and video covering people in the community.
  • Praising and pointing out the good about others.
  • Simplifying complex information, instructions, or events.
Avoid:
  • Slandering others.
  • Defaming others.
  • Advertising your products or services.
  • Soliciting business.
Using these methods, community leaders can provide invaluable content that leads people in the community to them as a provider of services and goods.  Through the relationship formed between the reader and author in the magazine articles these leaders have an inroad to the reader when the reader seeks to find a provider for a need or want that the author can fulfill.
This style of marketing defined in these guidelines for writing great articles are known today as inbound marketing.  By using them you’ll get found on the internet and connect with your customer.
– Joel

Dissonance in business communication

Overcoming dissonance in business communication

Dissonance in business communication occurs when your message and behavior do not say the same things to your audience.  According to dictionary.com dissonance can mean disagreement or incongruity.  This often to happen when a spoken message is delivered to someone who exhibits body language or behavior that indicates another message entirely.  For instance the message to a co-worker: “Great news!! Bob got promoted!” is partially interpreted by what is said, but if the speaker said it in an annoyed, rude, or pensive fashion then the message really doesn’t seem to be great news at all.

Illustration of Dissonance in Business Communication
Bad Body Language

The message is interpreted by the listener and both the message and the messenger are judged by the speakers delivery.

Now you may be thinking to yourself that you’re fairly successful at business communication when speaking to people so this doesn’t apply to you.  Maybe it doesn’t but it when looking at this type of situation I can say that I know I’ve made these types of mistakes at times and it certainly applies to me.  In my efforts to obtain better human relations skills I came across a work of Dr. Mark Goulston titled Just Listen.  In it Mark talks about this type of dissonance and lists (among many other things) a survey which you can get your close friends and business constituents help you with to identify areas that might be keeping you from getting through to people.

Dissonant Behavior Survey:  Survey several people who are close to you about how you might rub people the wrong way.  Let them know that you are trying to improve your business communication skills and ask them to identify the top three ways from the list below that you might annoy someone.  Be sure to accept their judgements without criticism and thank them for their help.

Dissonance creating behaviors:

  • arrogant
  • hyper
  • needy
  • overly opinionated
  • impulsive
  • rigid
  • nit picky
  • passive
  • indecisive
  • demanding
  • hostile
  • stuffy
  • over sensitive
  • sly
  • untrustworthy
  • mellow dramatic
  • rude
  • shy
  • pessimistic
  • abrupt
  • excessively perky
  • close minded

Once you have received the results of your survey compare them and look for areas that they all agree on. If you really want to be effective with this survey ask your sources to elaborate on the behavior they identified.  They will probably be reluctant to be directly critical of you and tell you that others “may or could” view you with the traits they mentioned because they won’t want to offend you.  Ask them what it is that could make you seem that way and what they think you could do about it.  You will then have the information needed to directly analyse your own behavior and improve your method of face to face business communication.

In the weeks after completing the survey study your own behavior as you interact with people and look for manifestations of the behaviors you identified.   Once you’ve reached a higher level of self awareness about your targeted behaviors you can go to work on them.  For instance you could talk to the people you work with or do business with and tell them that you are trying to improve your performance in the targeted area because you’d like to become more effective in the role that you deal with them in and then ask them how you can do better in that particular area in the future.  If the trait is so ingrained in your personality that you cannot change it then you can pre-disclose the matter in future face to face business communication to keep it from being a distraction from your message.

I hope this helps someone as much as it has helped me. If you’d like more information about this method read or listen to Dr. Goulston’s book Just Listen,

– Joel
Catontech.com
Meridian, MS

Flattening the org chart with mobile web technology – part one the pyramid.

Part one – The traditional pyramid organizational structure

When I was in college (10 years ago!) studying information technology and business one of the hot topics was how information technologies would flatten the pyramid organizational chart of many businesses. Ten years later after graduating and participating in management in several organizations as a corporate citizen of the of the business world I can say that I’ve seen little progress. Many organizations seemingly have invested in new technologies only to primarily use email systems to communicate with most of the information moving from top down with the majority of the decision support technology being leveraged at the top of organizations and not the bottom.

Traditional businesses are built upon a pyramid structure of management that works on a one to many relationship between the highest level leader  / manager or the organization and and his subordinate managers that handle employees and customers.  Here’s a brief story showing how this works.

An illustration:

Our entrepreneur  Joe starts out  with a vision to sell widgets to the world (or at least his home town).  He begins selling and becomes so successful that he must hire employees to help.  At this point Joe has to step back from dealing with customers directly and begin dealing with his employees if he wants to be successful.  Joe must devote some of his time to managing his people.  If he is successful at instilling his vision, methods, and purpose into the people he has hired then his business will continue to grow.

Now lets say that Joe is very successful and must hire or contract 30 people to handle the day in day out operations of his business.  Obviously Joe cannot deal directly with customers like he used to and probably spends most of his time coaching, training, and counseling his people on to success.  He has created a small pyramid structure where each employee or contractor reports to him.  Joe’s business expands and he opens a branch in another town.  He can’t be in both places at the same time so he has to hire another person to help manage.  The new person starts out with several employees and Joe’s plan, vision, and methods for selling widgets.

Joe now has two pyramids to handle,  the one he is directly involved in and the remote location.  This is the small business model.  As Joe continues down the road to success he continually adds more pyramids to his organization eventually creating units that handle marketing, legal issues, human resources, procurement, and financials.  By now Joe’s reached the point where there is no way he can run his original unit and properly motivate, inspire, and lead all the others so he hires another manager to handle his unit and spend all of his time now overseeing all the units.

Sounds complicated?  Well it really isn’t if Joe’s done some smart hiring but Joe does have quite an infrastructure of of people in place and a weekly obligation to do a set amount of sales in order to pay them all.  Joe’s done a great job of scaling up his business but the way he’s done it doesn’t leave him any room for scaling down.  Also, Joe is out of touch with his consumers now because he doesn’t get to deal with them directly so he can’t read market demands like he used to so he has to rely on the people he’s hired or contracted to get this information for him.  Joe’s business model probably looks like the picture below.

The traditional pyramid organizational structure
Joe's business organizational chart

As you can see Joe’s continuing success is placing him further and further way from his customers while his ability to make decisions that affect the outcome of the business has grown in proportion to its scale.  Joe’s answer to this problem would typically be to saturate his decision making process with inputs from the managers he’s hired and empower them to make some of these decisions.  He might occasionally drop in on departments or operations to see what is going on and attempt to get a better perspective of the needs of the business by talking directly to employees that deal with suppliers and customers, but for the most part he’ll be plugged into the network he’s built and be removed from the front lines of the business.  These are the effects of the traditional pyramid structure of business.

Strong points

  • Inherently allows for growth in the scaling of business.
  • Allows for intuitive growth of business
  • Authority and areas of responsibility clearly defined.
  • Clearly defined path for promotions and reward within the organization.
  • Internal units can create group cohesiveness, camaraderie and loyalties.
Weaknesses
  • Leadership is removed from front-line supplier, customer, and prospect interaction and may lack responsiveness.
  • Leadership must rely on subordinate sub leaders to supply information and analysis.
  • Top level decision making can be skewed by group thinking, the overriding desire for consensus, and group politics which do not reflect the businesses actual needs.
  • Front-line employee’s may have limited decision making ability and lack the ability to properly engage the customer or prospect.
  • Innovation may be stifled by top down communication.

In our next article we’ll discuss an alternative structure that could be used to improve responsiveness and innovation through networking.

Joel

 

TCPDF php package for pdf writing

I recently had opportunity to implement the TCPDF package for a midsized project. This article attempts to document my experiences with the API, its strengths, weaknesses, and ease of use.

The package is quite simple to implement at a high level, and following the included examples I was able to create a writer for my project in a matter of days. I appreciated the flexibility of being able to use HTML for layout. Also appreciated was the ability to override the TCPDF class to create custom headers and footers. I utilized this to place a reference to the company logo in the website’s image directory rather than in the tcpdf package’s image directory. I also was able to create a more detailed header layout than the default using this method. Once the pdf document is constructed, TCPDF provides some helpful output options including posting the document directly to the browser. This is a nice option because it allows previewing in an iframe, and doesn’t take up space on the server.

Initially I constructed a string containing inline style and the data in one large HTML table, and wrote the pdf document using one writeHTML call. An example of this follows:


$style="<style type=\"text/css\">\n";
$style=" table {\n";
$style=" color:red;";
$style=" }\n";
$style=" td {\n";
$style=" border:none;";
$style=" }\n";
$style="</style>\n";
$table="<table>\n";
$table=" <tr>\n";
$table=" <td>example</td>\n";
$table=" </tr>\n";
$table="</table>\n";
$html=$style.$table;
$tcpdfObj=new TCPDF('L','pt',true,'letter',false);
$tcpdfObj->SetHeaderData("logo.png", 100, 'pdf title', 'header text');
$tcpdfObj->setMargin(72,72,72,true);
$tcpdfObj->setHeaderMargin(72);
$tcpdfObj->setAutoPageBreak(TRUE,72);
$tcpdfObj->SetFillColor(0,0,0);
$tcpdfObj->setCellPaddings(0,0,0,0);
$tcpdfObj->setFont('sans','',10,'',true);
$tcpdfObj->AddPage();
$tcpdfObj->writeHTML($html,true,true,false,false,'');
$tcpdfObj->Output("test.pdf",'I');

This first implementation worked for a small test database, but failed when I tested it for larger ones producing out of memory errors. Raising php’s mem_limit didn’t solve the problem. I was able to work around this by dividing the writeHTML call into several smaller calls each with a copy of the inline style and a HTML table containing several rows of the original HTML table, but this added to the running time. writeHTML seemed to work with about 2500 cells at a time. Having overcome the memory limitation, I found that running time for large datasets was unnacceptable. It was in the range of 10 minutes or more for a 50000 cell document. Fortunately tcpdf has faster Cell, and MultiCell functions, however when using them layout becomes much more restrictive. Using these faster calls reduced the running time by 50%, but this was still too slow for my project.

To summarize, the tcpdf package works, offers some flexibility of layout and output, is quickly implemented, but doesn’t scale well.

PHP quick data export to spreadsheet

In our last post we talked about the limitations of creating large spreadsheets with PHP library PHPExcel.  Today we’ll discuss a quick workaround that will allow you to create large unformatted spreadsheets for quick and easy data export from your applications.  Here’s how we do a php quick data export to spreadsheet:

  • Properly escape your data.
  • Write data with fcsvput($filehandle, $yourdataarray, “t”);
  • Include table headings and other data.
  • Use a xls file extension.
Properly escape your data... You’ll be using a tab delimited format for output so you’ll need to replace the tabs with an escaped tab or five spaces so that your data doesn’t corrupt your output format.  For our purposes we’ll be using five spaces in the following function.
function TSV_Escape($str)
{
    if (!strlen($str) == 0)
    {
        $str=str_replace('t','     ',$str);
        if (!strstr($str,"n") === false)
        {
            $str=str_replace('n','%n',$str);
        }
        return str_replace('"','""', $str);
    }
    return '';
}

Write you data with fputcsv:  First you’ll need to open a file handle like this…

// Set file path and initialize handle.
$TSVFile = $_SERVER['DOCUMENT_ROOT'] . "/MyDataFile.xls";
$TSVFileHandle = fopen($TSVFile, 'w');
// Write your headers.. See next section :)
// Write your data.
// Write query data to csv.
if ($objdbconn->real_query($sql))
{
    if ($result = $objdbconn->store_result())
    {
        echo date('H:i:s') . "Processing " . $result->num_rows . " records.<br>\n";
        while ($row = $result->fetch_assoc())
        {
            $writeArr = array();
            foreach($row as $value)
            {
                $writeArr[] = TSV_Escape($value);
            }
            fputcsv($TSVFileHandle, $writeArr, "\t");
            unset($writeArr);
        }
    }
}
// Close your file handle.
fclose($TSVFileHandle);

Write your headers and other data:  You may want to include some information about your spreadsheet and you’ll certainly want to include the column header row.  Here’s a brief example of how this could be done:

$headings = str_replace('`', '', "Heading One,`Database Field 1`,`Database Field 2`,`Database Field 3`");
$headArr = explode(',', $headings);

$RepDat[]="Created: " . date('H:i:s d-m-Y');
$RepDat[]="Created by: " . $UserName;
// Write county name to csv file.
fputcsv($TSVFileHandle,$RepDat,"\t");
fputcsv($TSVFileHandle,$headArr,"\t");

Use a xls file extension.  Sure you could use a tsv file extension, but if you want Excel or Open Office to open the file by default with minimal headache the xls extension will do the trick.  You’ll get an message when you open the report stating that the file was not in the same format as the extension, but you won’t have to worry about the tsv file extension being registered to the right application.

This method will kick out a tab delimited spreadsheet in a matter of seconds and can safely handle large record sets.  We used it for a while, as a temporary fix for reporting, until we came up with a better method and we still use this method to create database load files when parsing complex legacy reports or backing up database records to file.

I hope this php quick data export method is helpful.

Joel

Excel spreadsheets with PHP – a PHPExcel overview

Writing Excel spreadsheets with PHP using PHPExcel:  The pros and cons

PHPExcel is a php library for reading and writing excel spreadsheets.  It is very well documented and has good community support.  When searching the web for an open source solution for writing spreadsheets in PHP I reviewed everything from PHPClasses to Pear and finally I came across this package.  It’s the most up to date looking project out there for excel spreadsheets with PHP.  I didn’t want to use a project that was in the twilight of its development cycle so I opted to try PHPExcel.  Here’s a a brief overview of what I found:

Pros – The good points:

  • Very well documented.
  • Good examples
  • Support forum with active participation.
  • Supports many of the builtin excel functions.
  • Easy to style
  • Supports modern Excel formats plus csv, html, and pdf
  • Write spreadsheets
  • Read spreadsheets
Cons – what I couldn’t stand:
  • SLOW….  This library takes considerable time to produce a spreadsheet when memory conserving disk caching is used.  Consider running your process in the background.
  • Memory Intensive  PHPExcel offers disk caching to reduce its in memory size, but it basically is an in memory spreadsheet and must load completely into memory while being before being written to file.  This can considerably hinder your performance as the spreadsheet object and writer object can take over 25 mg of ram while the program is running with an additional 1 kb of ram per cell loaded in memory.  For an example of how this can impact your server or hosting service consider the load of a 50 column 10000 row spreadsheet which would take 525 mb of ram to load when writing to file.  While its possible to set the scripts memory high enough to handle this you’ll find that if several of these scripts try to run at the same time you’ll have a mess that could crash your server.
  • Write to file is batched processed at the end of the script and happens all at one time.  Regardless of your disk caching settings the entire spreadsheet is loaded into memory and then written to the file.  This creates a serious bottleneck if you have a large file and will oftentimes crash your script.  The only exception to this is when PHPExcel writes a csv file in which it allows for a more granular approach to memory to file writing.
Workarounds that will reduce memory usage and improve speed.
  • Set a cells value explicitly.  Setting the cells value and data type explicitly is much faster than writing cell values for PHPExcel to determine the data type of.
  • Disable formula precalculation by setting it to false.
  • Use disk caching.
  • Don’t try to use it to create reports from large data sets.
In summary:  If you are trying to read and write to small spreadsheets with less than two hundred and fifty thousand fields or cells then this library will be a good fit for your project.  It’s great for summary reporting!!  However if you’ve got extremely large record sets that you need to send to a spreadsheet you’ll need a different approach.  I’ll be writing about how we did that in the next blog post.
Stay tuned and may the source be with you!!
– Joel

My findings on large complex sql statements with mysqli

My findings when developing using large complex sql statements with mysqli in php

The granular approach is more efficient

Sometimes its tempting to execute a large complex sql statements with mysqli to “do it all” in one update but I’ve found this approach to be inefficient in terms of speed and functionality.  My advise it to use smaller and precise sql statements instead of large cumbersome queries. The small statements execute quickly and will get the job done faster then the large complex query. For instance in one case I had a complex dynamically generated query with over 230 update statements that updated a single table and concatenated (CONCAT) text to a text field.  This query typically hung up and took so long to execute that it caused problems in my ajax environment locking tables and creating conflicts with other operations that were trying to read and write using update and select statements.  Once I broke it up into 230 small queries the execution time went down from 30 seconds and failing to 3 to 5 seconds and never failing.  In another case when translating csv files to a database I had an issue with a multi query that hung up on large files but once broke it down into running smaller specific queries it was able to execute up to 10000 queries in a few seconds (no I’m not exaggerating it).

In addition to these tidbits here’s some other findings… Mysql lets you set the priority level of queries and by default update queries have a higher priority than select queries so in theory it isn’t possible to run a select on a table that is in the middle of an update transaction if autocommit is being used (it is always on by default). Mysqli locks the table when performing the update and the select has to wait until it is unlocked. This can become cumbersome and unweildy when performing large complex queries and so far I haven’t had very much success with them.

When it comes to complex sql statements with mysqli achieving granularity through smaller interrelated specific sql statements seems to get far greater results for speed and efficiency.

May the source be with you

– Joel