Tips & Tricks

I Just Export from PHP to CSV and Import It to MS Excel, What Could Go Wrong?

by

For many years I assumed generating a CSV file is the best option if you asked to implement an “export to Excel” in your PHP web application. Writing CSV files with PHP is plain simple and does not involve using complicated or proprietary libraries, the format could be also read by many applications other than Excel and after all the output is a plain-text file, which you can review with any text editor if something goes wrong. Basically, all you need is fputcsv PHP function, that takes a provided data array and writes it as well-formatted CSV row to the output file.

That all worked fine for me, until one day a client told me that CSV files we produce does not really import to Microsoft Excel. It turns out that Excel has two ways to import CSV file:

  • Text Import Wizard, which is invoked by clicking From Text on the Data tab of modern Excel versions. In this case you’re prompted to specify file encoding, delimiters and other settings.
  • Regular file import, which is performed if you double click a file in Explorer or used File -> Open menu. In this case Excel tries to auto-recognize file format details.

Our problem was that the regular file import didn’t work for files we generated with PHP. Text import wizard worked with correct settings but users didn’t want to go into file format details and after all I found it also doesn’t work right for some files.

After a lot of research, trials and errors, I found that was caused by few limitations MS Excel applies to CSV format, which are not supported by original PHP fputcsv function:

  • Excel expects all rows ended by CR and LF characters, while fputcsv on Linux etc places LF character only.
  • Excel expects multiline values to use CR character as a line break.
  • Excel has a limit of 32767 characters per single cell.

First two points I learned from this answer at Stack Overflow, which for some reason is not marked as correct one.

Still, the issues above is not a reason to switch from CSV to a native MS Excel format, writable via some library or PHP extension. With a little work, you can get your CSV files compatible with MS Excel:

  • Replace all LF characters or CR LF combinations with CR characters in the array items you’re passing to fputcsv function.
  • Ensure all array items you’re passing to fputcsv function do not exceed 32767 characters or trim them in any way possible.
  • Replace LF characters fputcsv put at line ends with CRLF.

Unfortunately, fputcsv function still does not support any way of altering line break characters so you only can rewrite them as following:

fputcsv($fp, $line); 
fseek($fp, -1, SEEK_CUR); 
fwrite($fp, "\r\n");

Or, you can use a function from this comment. However, it does not incorporate first two tips, so you may need to alter it or find another alternative function. There are some available.

After fixing the issues your CSV files will open in MS Excel w/o any problem.

How jQuery event handlers can survive AJAX refresh using event delegation

by

How many time you were working hard to add smart AJAX (or similar) refresh function to your complex user interface just to find out that your jQuery event handlers are not working anymore after the refresh? That is, as you your original DOM elements are gone, event handlers assigned to them are gone too. That’s so much frustrating, when you almost get it working, you realize that you have to rewrite most of your shiny JS code in order to stay working after AJAX refresh.

I used to implement clumsy workarounds by supplying event handlers initialization code with HTML returned by AJAX. But that code is run by jQuery using eval() function making it impossible to debug. So I switched to encapsulating event handlers into a function that could be called after each refresh. But once again, I need to rewrite my code so that event handlers are assigned to the elements of refreshed container, otherwise I end up assigning event handlers to elements that already have them.

That was much pain resulting in dirty code until I found out jQuery (and JS) already have a solution to this problem via delegation of events. In easy words: you can assign event handlers to static elements that are not refreshed but contain refreshed elements. You just have to add a selector specifying child elements you want the event to be handled for. If you have no idea, you can assign event handlers to body element as it will always stay there.

So if you have a code like this:


$('.my-button').click(function() { 
    alert('My button is clicked'); 
});

…and you find out that the button is replaced by AJAX and stops working, you can simply rewrite it to the following syntax:


$('body').on('click', '.my-button', function() { 
    alert('My button is clicked'); 
});

And this event handler will still work after AJAX refresh!!!

Despite the handler is assigned to “body” element, the “this” variable passed to the event handler will still contain “.my-button” element, so no change to the event handler body is usually required.

Notice the code change is quite minimal, isn’t it? You just need to change the way event handler is assigned a bit, you can even automate that code rewrite with regular expressions. No more moving code to HTML returned by AJAX, no more “init” functions that restore event handlers, no tears: it just works.

At the moment I’m not aware of any drawbacks of this method. Maybe performance?

As I’m not really a big JS expert, I was afraid it could work for simple events like “click” but not work for more specific ones like “change”. So I wrote this simple example to verify if it works for “change” event: https://jsfiddle.net/ab49Lprq/. Yes, it works! I also tested focus event and it works toothanks to bubbling of JS events. I think only custom event handler that stops bubbling may prevent it form working as expected but still you can handle the even in an element inside the one that stops bubbling.

So have fun writing nice jQuery user interface automation and make sure it still works like a charm (but easy to debug just in case;) even if you heavily refresh parts of your screen with AJAX.

Never develop for Facebook canvas

by

Some time ago we started a project (like a social game) mainly targeting Facebook audience. We decided to base it on Facebook Canvas technology, not to be confused with canvas.facebook.com, which is business presentation layout. Basically it loads your web application into iframe inside the familiar Facebook user interface page on an URL like https://apps.facebook.com/{your-application-machine-name}.

At first, it seemed to be a cakewalk and the technology matching our expectations just right:

  1. The users are presented with a Facebook interface they are already familiar too.
  2. The users doesn’t have to login if they are logged in to Facebook. They just have to sign up to the application the very first time they access it.
  3. We can use Facebook’s Invitation Dialog that sends game invitations to selected Facebook friends of the user.
  4. We can use Canvas Payment service instead of implementing some payment gateway support in our application.
  5. We can automatically create Facebook groups for user teams in our application.

But when we started to dive more and more deeper into Facebook Canvas development, the disadvantages started to show up one by one:

  1. It required us to host our application (what is loaded into the iframe) on SSL URL so we had to buy an SSL certificate. While not a big deal and good for security but why SSL is also required for a test version of the application?
    Facebook allows registering a test version(s) of your canvas application with different URL(s), which can point to local host the hustle for code testing. But it’s all ruined by this SSL requirement so you end up telling your browser https://127.0.0.1/ is safe even with self-created certificate. Honestly speaking, I failed to solve this issue permanently for Google Chrome browser on Windows even after adding my certificate to white list following some guidelines I found on the web.
  2. The whole Facebook Canvas system seems to be not developed for web applications with link transitions and POST forms. I think they targeted mainly Flash or JS applications that always stay on the same URL.
    If you refresh the iframe or follow some link, it will lost all the Facebook authentication data. So we had to save the login data in user session on our side and restore on page load if original Facebook data is not available. AFAIK, this may not work for some old IE version that do not save cookies for iframes.
  3. Another thing is that the URL in your browser stays the same when you surf between application pages in the iframe so if you refresh the Facebook page, you will go back to the application’s page you started with. At the same time, Facebook allows coding application local paths into Facebook Canvas URL like: https://apps.facebook.com/{your-application-machine-name}/{path-inside-your-application}. So, if you open an URL like https://apps.facebook.com/{your-application-machine-name}/my/test/page it will load https://{your-application-base-URL}/my/test/page into the iframe.
    I ended up implementing a JS trick that intercepted any link lick event and reloaded the main frame of the browser with corresponding Facebook URL. This is what I didn’t like the most: a JS failure or slow page load may cause the user to click an original link.
    Still I had to save login data to user session in order to restore it after POST request sent by any form in my application. This approach did not allow having form targets reside on URLs different to the current page but that was fine to me.
    Those could be considered a root issues of using the iframe tag to load the Canvas application but why aren’t they all documented?
    All in all it seems that you should better use pjax or any similar JS technology to process requests and update the application page w/o doing a n iframe reload actually.
  4. While Facebook have a mobile version m.facebook.com, there is nothing like that for apps.facebook.com. It doesn’t matter you made a responsive layout in your application, the whole page still uses a desktop layout making your page inside iframe use it too. This makes mobile user see a conventional desktop Facebook page with sidebars and menus, zoomed out to fit inside tiny mobile device screen. This is just unacceptable.
  5. Facebook Canvas for Games have a nice feature called invitations. The user could pick some of his or her Facebook friends and send them invitation messages, which will bring them to the game if clicked. The invitation messages will appear below the Notifications (globe) icon on the Facebook menu when you click it. This is just nice and you know what? The invitation messages are completely not shown in mobile version of Facebook (m.facebook.com) and in mobile Facebook application. Everything else is shown but not the game invitations. And as Facebook support answered, this is not a bug, it’s a “feature”. They don’t want to annoy mobile users with Canvas game invitations assuming Facebook Canvas is not for mobile devices at all.
    Taking into account that most of our users accessed Facebook via mobile devices, we had to fallback to old-style email invitations for recipient addresses manually entered by the user.
  6. The invitation dialog itself is a complete disaster. It shows “Send” button right to each friend on your list but it doesn’t actually send anything until you click the OK button in the bottom of the dialog which is very hard to notice. So many times our users fall into this mistake thinking they invited their friends while no invitation was on the way. And as the dialog is provided by Facebook, we could not alter it much. The worst problem there is not other way your application may message the users who didn’t yet signed up to the application.
  7. Another issue with the Invite Dialog is that it doesn’t indicate the users you already invited resulting in duplicate invitations sent to the same user.
  8. Facebook Canvas have also a nice feature called Canvas Payments. With a few lines of JS code you can accept payments from wide variety of services: PayPal, credit cards, mobile payments, etc. But it all goes for a price of draconian 30% commission, which we didn’t notice when we made the decision to use the service. It turned out our application could not commercially success with such a high rate for processing payments.
  9. Another problem with Canvas Payments is that it only works in one direction: from the user to the Facebook (and eventually to your bank account). If you want the users to monetize their achievements in your game, you should pick another service like PayPal at least for payouts.
  10. And finally the application groups feature went deprecated this year without any replacement provided. So you cannot generate Facebook groups and invite members from your application anymore. This is quite sad as we just loved the feature for hosting internal conversations or team members within our game.

Conclusion

Facebook Canvas seems to be a good way to promote your application if it is already successful as a web or mobile application. At any way it shouldn’t be considered as the primary publication platform unless for a simple one-page application. You should consider Facebook Canvas for providing as a limited version of your application or just as a gateway to it. The whole Facebook Canvas platform seems to be ageing and it looks like Facebook may abandon it later either completely or partially.

You shouldn’t use Facebook Canvas Payments if small commission rate is critical for your commercial success. If you’re going to allow users monetize their achievements or get paid for anything else, you should consider a different service like PayPal.

You should have a mobile (Android/iOS) application if you’re targeting mobile users. Facebook allows some integration with the mobile application(s) you specify in your Canvas application profile.

All in all, it turned out that for us it would better if we developed a stand-alone web application with Facebook Login function and PayPal payments instead of relying on Facebook Canvas and Canvas Payments. It was a huge failure but I hope somebody will learn from our mistake.

Changing array from within the foreach loop

by

A very basic PHP syntax thing usually ignored by developers is that you can directly update current element from within the foreach loop.

Just precede value variable with & character and it will be assigned by reference:

$arr = array(1, 2, 3, 4);
foreach ($arr as &$value) {
    $value = $value * 2;
}

Read more

Easy way to automatically update commercial WordPress plug-ins

by
Update notification for a commercial plug-in

WordPress CMS has a nice auto update feature that displays update notifications for outdated plug-ins and themes in admin panel. Once you got a notification you can go to Dashboard->Updates and install all updates with few clicks while WordPress will automatically download and extract archives, remove old versions and reactivate plug-ins for you.

The only problem is  that it works for plug-ins published on wordpress.org which are open-source so it’s not an option for commercial plug-ins.

Luckily there is a very good solution to this problem: Upgrademe plug-in.

Read more

PDFmyURL: export web pages with JavaScript elements to PDF on shared hosting

by

Customers and users sometimes ask for an ability to save some reports from web applications to PDF files. It could be very tricky to write PDF directly but as far as you already have those reports on web pages you can add printable views of them and then ability to convert HTML/CSS of the pages to PDF, say with dompdf library.
This way you can hit two targets: add printing of reports and PDF export. Dompdf is still in beta but at least it could work on shared hosting AFAIK.

But what if you need to export web pages with JavaScript canvas elements like Flot charts that are plotted on browser side only? All available solutions require installing executables which is usually problematic on shared hosting.

The solution is PDFmyURL.com/ which you can easily use as a web-service: send GET requests with URL of the page you want to export and get PDF file as a response. The service is based wkhtmltopdf. As you can see, it’s good enough to convert pages with Flot charts:

http://pdfmyurl.com/?url=http://people.iola.dk/olau/flot/examples/basic.html.

You can call the service from JavaScript on your pages or from server-side scripts via any HTTP client library like cURL. All wkhtmltopdf options are supported and could be passed as GET request parameters.

Their free service inserts very small watermark to each page which is usually not a problem. But of course you can upgrade to paid service to remove it.

How to set InnoDB as a default storage engine for MySQL tables

by

I use InnoDB storage engine because of support for transactions and referral integrity rules. However, MySQL still creates new tables as MyISAM by default. It was so annoying  to always define storage engine when creating new tables and double check that I didn’t forget it until I found how to set InnoDB by default.

Read more

Why MySQL timestamp is 24 seconds different from PHP

by

You may find the timestamp value returned by MySQL UNIX_TIMESTAMP() function 24 seconds grater than that returned by PHP functions and methods like strtotime(), mktime(), DateTime::getTimestamp(), Zend_Date::getTimestamp().

Read more

PHP regular expressions and UTF-8

by

Perl-compatible regular expression functions in PHP can properly work with Unicode strings. Just add /u modifier to turn on UTF-8 support in preg_replace, preg_match, preg_match_all, preg_split and other PCRE (preg) functions. This way you can parse strings with national characters. For example:

$clean = preg_replace('/\s\s+/u', ' ', $dirty);

If used without /u modifier this code damages UTF-8 encoded strings by replacing national character bytes improperly interpreted as whitespace characters. This and many other problems are caused by improper interpretation of every byte as ASCII character which is not always true for UTF-8.

The modifier is available from PHP 4.1.0 or greater on Unix and from PHP 4.2.3 on win32. UTF-8 validity of the pattern is checked since PHP 4.3.5.
I found this tip as well as many other useful info on regular-expressions.info. It’s not easy to find it in the PHP documentation but it’s actually hidden here.

SEO-friendly URLs and relative links

by

The Web community is going crazy about SEO-friendly URLs like http://somesite.com/products/network/router/. Well, it looks much better than a script URL http://somesite.com/products.php?c=network&p=router which may actually serve the page behind the scenes. There are a lot of good articles on how to implement SEO-friendly URLs, for example this one or my own post. But they do not warn the reader about one usual problem: once you have updated your site to handle virtual paths you will probably get a bad surprise:

CSS, image and internal page links are totally broken!

Why? Because those links are usually relative to the page location. The browser has no idea about virtual folders and tries to get files from locations relative to the page URL context. For example, if there is a usual CSS link in the page header:

<link rel="stylesheet" href="style.css" type="text/css" media="screen" />

Then the browser will try to download non-existing file http://somesite.com/products/network/router/style.css and fail silently. No CSS style will be applied.

It’s incredible how many words were spoken about SEO-friendly URLs with almost no word about this relative link problem.
So, what you have to do? Don’t worry, there are multiple solutions available and I’ll try to explain them all.

Read more