Development

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

Admintasia: awesome design template for web applications

by

PS. The post was written so long time ago. Now I’m happy we can simply use Twitter Bootstrap and get working application/site design in seconds.

Unlike desktop applications which rarely use “skins”, web applications look ugly and unprofessional w/o a design template. However, building unique design for each project sometimes is just not affordable, especially for back-end, admin and other data analysis and management applications. At the other hand, if you look for free or commercial design templates available, you’ll find nothing but website templates which doesn’t have elements like cascading menus, dialog, grid, form elements and others usually required for an application user interface.

Sad but true, except this little thing: Admintasia 2.1. Powered with jQuery and jQuery IU it has all the elements you need to build rich professional user interfaces.

Read more

Cascade update fails on MySQL for foreign keys referring to the same table

by

Recently I noticed that ON UPDATE CASCADE rule falis in MySQL on InnoDB tables if a foreign key references the same table, which is usual for tree-like data structured. At the same time ON DELETE CASCADE works fine.

This  is not corresponding to SQL standard but it’s already stated in MySQL manual:

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT

It was also reported as a bug but rejected years ago.

Read more

Enabling/Disabling HTML elements with jQuery 1.6+

by

After upgrading to jQuery 1.6+ you’ll find that code you used to enable/disable elements doesn’t work anymore:

//This is supposed to enable element(s) before jQuery 1.6:
$("some-selector").attr("disabled", "");
//This is supposed to disable element(s) before jQuery 1.6:
$("some-selector").attr("disabled", "disabled");

But now the code above doesn’t change anything. So what’s the problem?

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.

Flot: Amazing JavaScript/jQuery/AJAX charting library

by

When I developed Windows desktop applications I was tied to ProEssentials library which was powerful yet expensive and had weird and very limiting API. Don’t want to say anything bad about ProEssentials developers but, OMG, their API contains hundreds of properties and functions you have to go through just to find few ones you really need. Not only do many of them have strange names, but they also do very strange things))).
How do you like “ForceVerticalPoints” property? How in this World could points be vertical or horizontal? Well, it’s actually about point labels… Enough about that horror.

Thank to Flot library, charting for the Web made extremely easy yet powerful and extensible. Flot is jQuery plug-in which means you can use all power of jQuery for setting it up, passing data to it and reacting to its events. Moreover, chart plotting happens on the browser side which means your server isn’t slowed down by producing chart pictures. And this also means you can add interactive features to your chart like point tooltips, zooming, live AJAX updates or display options applied w/o page reload.

Read more