Taylor is a freelance web and desktop application developer living in Suzhou in Eastern China. Started from Borland development tools series (C++Builder, Delphi), published a book on InterBase, certified as Borland Expert in 2003, he shifted to web development with typical LAMP configuration. Later he started working with jQuery, Symfony, Bootstrap, Dart, etc.

Taylor's articles

  1. Generate Excel Files and Charts with PHPExcel

    After my article “How To Make Microsoft Word Documents with PHP” (using Interop capability under Windows), there were quite a few comments urging a pure PHP implementation, i.e., only using a universal PHP library to manipulate Office files.

    In this article, we will see how to use a PHPExcel library to provide an “Export to Excel” function in a web app so that the user can export the data into an Excel 2007/2013 file for further analysis.

    NOTE: There are a few PHP libraries that can provide Excel (and Office) file manipulations. The lib we use here is called PHPExcel, a subset of PHPOffice, which can be cloned here.

    Objectives

    After this tutorial, we will get:

    • A sheet showing the game information (date played, teams, score, win/lose status) of my favorite NBA team – LA Lakers, in its 2013-14 season.
    • A button that will export the data into an Excel 2013 file.
    • That Excel file will be populated with some additional analytic data and a chart also generated by PHP and Excel.

    Let’s get started.

  2. PINQ – Querify Your Datasets – Faceted Search

    This entry is part 2 of 2 in the series PINQ - Querify Your Datasets

    In part 1, we briefly covered the installation and basic syntax of PINQ, a PHP LINQ port. In this article, we will see how to use PINQ to mimic a faceted search feature with MySQL.

    We are not going to cover the full aspect of faceted search in this series. Interested parties can refer to relevant articles published on Sitepoint and other Internet publications.

    A typical faceted search works like this in a website:

    • A user provides a keyword or a few keywords to search for. For example, “router” to search for products which contain “router” in the description, keyword, category, tags, etc.
    • The site will return the products matching the criteria.
    • The site will provide some links to fine tune the search. For example, it may prompt that there are different brands for a router, and there may be different price ranges and different features.
    • The user can further screen the results by clicking the different links provided and eventually gets a more customized result set.

    Faceted search is so popular and powerful and you can experience it in almost every e-Commerce site.

    Unfortunately, faceted search is not a built-in feature provided by MySQL yet. What can we do if we are using MySQL but also want to provide our users with such a feature?

    With PINQ, we’ll see there is an equally powerful and straightforward approach to achieving this as when we are using other DB engines – at least in a way.

    Extending Part 1 Demo

    NOTE: All code in this part and the part 1 demo can be found in the repo.

    In this article, we will extend the demo we have shown in Part 1 and add in some essential faceted search features.

    Let’s start with index.php by adding the following few lines:

    $app->get('demo2', function () use ($app)
    {
        global $demo;
        $test2 = new pinqDemo\Demo($app);
        return $test2->test2($app, $demo->test1($app));
    }
    );
    
    $app->get('demo2/facet/{key}/{value}', function ($key, $value) use ($app)
    {
        global $demo;
        $test3 = new pinqDemo\Demo($app);
        return $test3->test3($app, $demo->test1($app), $key, $value);
    }
    );

    We just created two more routes in our demo application (using Silex).

    The first route is to bring us to the page showing all the records that match our first search behavior, i.e., search by providing a keyword. To keep the demo simple, we select all books from the sample book_book table. It will also display the result set and faceted links for further navigation.

    The second route brings us to another page showing the records matching further facet search criteria in the result set produced in the above step. It will display the faceted search links too.

    In a real world implementation, after a faceted link is clicked, any faceted filtering in the result page will be adjusted to reflect the statistical information of the result data set. By doing this, the user can apply “add-on” screenings, adding “brand” first then “price range”, etc.

    But in this simple demo, we will skip this approach, all faceted search and the links will only reflect the information on the original data set. This is the first restriction and the first area for improvement in our demo.

  3. PINQ – querify your datasets – introduction

    This entry is part 1 of 2 in the series PINQ - Querify Your Datasets

    You may have heard of LINQ (Language-Integrated Query), a “set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic”.

    It provides necessary interfaces and syntax to perform various queries on a given dataset so that operations like filtering, sorting, grouping, aggregating, etc can be performed.

    PINQ (PHP Integrated Query) is “based off the .NET’s Linq, and unifies querying across arrays/iterators and external data sources, in a single readable and concise fluent API”. (Excerpted from PINQ’s official site)

    Why another query language?

    PHPers are very much comfortable with executing queries with either raw SQL statements or via ORM. We select the data from a database, process it and display it in a chosen format (say, a table form). If we need another set of data, we issue another statement, process the returned dataset and display it.

    In normal cirucumstances, this process is both sufficient and efficient.

    But there are cases where this process simply won’t work. Take, for example, a typical e-Commerce website. The user enters one search keyword (say, “router”) and the site displays every matching item. The initial search may only search items with their description, category or tags containing the keyword. Next, the user will start to fine tune the search results by selecting a brand, a price range, etc.

    This fine tuning process is called “faceted” search. Some database engines (like SOLR) have this capability built in (as described in this series: Using Solarium for SOLR Search) but obviously MySQL does not come with this functionality.

    That does not mean, however, that MySQL can’t provide such features. After all, it is all about constructing a new SQL statement and fetching the data again. This has some disadvantages, however:

    1. The criteria of the SQL statement, i.e., the “where” and/or “group by” part, can get very complicated after SQL construction.
    2. As the SQL statement will be very dynamic, it can’t be optimized and will make indexing more difficult.
    3. It will create a huge overhead when communicating the SQL statement back to the db server.

    In these cases, PINQ may come in handy. It is a PHP version of the LINQ library which provides filtering, sorting, grouping, aggregating, and indexing on a given dataset.

    Preparation

    In this series of two parts, we will demonstrate how to use PINQ to mimic a “faceted” search. We will use the sample book collection application’s data (see Data Fixtures in Symfony2 on how to dump the sample data) but with some slight modifications.

    Also, we will use Silex, a very light-weight PHP framework and Bootstrap CSS to simplify the app setup. Please follow the instructions in their respective websites on how to set up a Silex web app and integrate Bootstrap CSS.

    The sample data we used in this demo is slightly modified and I have uploaded to the repo for this demo. The source code can also be found there.

    PINQ Installation

    The recommended PINQ installation is to modify the composer.json file that comes with Silex and add one more line in its require section:

    {
        "require": {
            "silex/silex": "~1.1",
            "twig/twig": ">=1.8,<2.0-dev",
            "doctrine/dbal": "2.2.*",
            "symfony/twig-bridge": "~2.3",
            "timetoogo/pinq": "~2.0"
        }
    } 
  4. How To Make Microsoft Word Documents with PHP

    As I had pointed out in my previous article, PHP and WMI – Dig deep into Windows with PHP, we do live in a world where we PHP devs have to deal with the Windows operating system from time to time. WMI (Windows Management Interface) is one such occasion and Microsoft Office Interop is another – an even more important and more frequently used one.

    In this article, we will see a simple integration between Word and PHP: to generate a Microsoft Word document based on the inputs in an HTML form using PHP (and its Interop extension).

    Preparations

    First, please make sure a typical WAMP environment has been setup in your Windows development machine. As Interop is purely a Windows feature, we will have to host Apache and PHP under Windows. In this instance, I am using EasyPHP 14.1, which is quite easy to install and configure.

    Next, we will have to install Microsoft Office. Its version is not that critical. I am using Office 2013 Pro but any Office version later than 2007 should work.

    We then have to make sure the libraries to develop an Interop application (called PIA, Primary Interop Assemblies) are installed. To ascertain this, we can open the Windows Explorer and navigate to: <Windows Directory>\assembly and we will see a bunch of installed PIAs:

    We see a Microsoft.Office.Interop.Word entry (underlined in the snapshot). This will be the PIA we use in this demo. Please pay special attention to its “Assembly Name”, “Version” and “Public Key Token”. These are to be used in our PHP scripts very soon.

    In this directory, we can also see other PIAs (including the whole Office family) available for programming (not only for PHP, but also for VB.net, C#, etc)

    If the PIAs list does not include the whole package of Microsoft.Office.Interop, we will either re-install our Office and include PIA features; or we have to manually download the package from Microsoft and install it. Please consult this MSDN page for detailed instructions.

    NOTE: Only Microsoft Office 2010 PIA Redistributable is available to download and install. The PIA version in this package is 14.0.0. Version 15 only comes with Office 2013 installation.

    Finally, we have to enable the PHP extension php_com_dotnet.dll in the php.ini file and restart the server.

    Now we can move on to the programming.

  5. Autogenerate CRUD Apps with Phreeze

    Back in the times of Symfony 1.x, the framework had a powerful backend module to help the developers and site administrators create a good enough app and provide necessary CRUD features to manage the database (and save us from using PHPMyAdmin).

    Since Symfony 2, this has been taken out from the core and the developers either have to rely on their own to start from scratch or rely on some other third party Symfony 2 bundle when such a feature is needed – and in many circumstances, it is.

    In this article, we will take a look at Phreeze, a simple and easy to use PHP framework that can help us generate a MySQL CRUD backend app. I will use my book collection test site as the underlying database. Please refer to my Data Fixtures in Symfony2 article for more details on the database structure.

    Installation and bootstrapping

    Phreeze is distributed as standalone. You can clone a copy of its latest version from Github.

    In my environment, I have created a new virtual host test and cloned the repository to its phreeze folder so that I can start the backend generation with Phreeze using: http://test/phreeze/builder.

    To facilitate the generation of a backend, Phreeze introduces two step wizard-like screens to guide us.

    In the first screen, we will provide the necessary database connection information (server, database/schema, user, password):

  6. Symfony 2 Checklist for Development and Deployment

    In my [Building a Personal Web App Head To Toe With Symfony 2](http://www.sitepoint.com/series/building-a-personal-web-app-head-to-toe-with-symfony-2/) series published on Sitepoint, I have covered some basics in bootstrapping, development and finalizing (on some advanced techniques) to use Symfony 2 to develop a web application. However, due to the length limitation of the series, we have not covered much on the “final” step: To deploy a Symfony 2 application to the production environment.

    When we do development, most likely we are in a “root” role; but in a production environment, we may be deprived of this privilege. Also, the settings may be different. To make things worse, sometimes we are not able to change these settings as we do in our own machines.

    Thus, it is of great importance to check the “compatibility” of our production server BEFORE any real development is done in our own server. This will avoid such horrible situations like: a library that Symfony depends on is missing, some global settings which the app relies on are unchangeable, etc.

    Testing the “compatibility” of our production server should really be something we do at the very beginning. There are also some other aspects to be addressed during deployment – such as templates for various error messages, etc.

    In the following sections, we’ll be assuming you don’t have full control over your production server. If you do, most of the compatibility issues probably don’t apply, as you should be able to resolve them yourself quite easily.

    ###An empty Symfony framework on the production server

    Please follow the instructions in [my first article on Symfony 2](http://www.sitepoint.com/building-a-web-app-with-symfony-2-bootstrapping/) to set up an empty Symfony framework on the production server.

    This is also useful to test if the server has cURL enabled, not only installed on the server but also as a PHP extension, making sure we can grab external resources. In my particular case, this is very important – composer.org is blocked in my country and I need to use a proxy to fetch and install the Symfony Framework.

    This empty framework can later be checked into version control.

  7. A Look at Valentina

    Valentina is a set of tools including: Valentina DB (a new SQL database server), Valentina Studio (a database management tool), Valentina Report (a GUI to create reports to be used in an application like PHP) and a related development toolkit (called ADK).

    In this article, we will take a look at:

    • How to use Valentina Studio to manage our MySQL database;
    • How to use Valentina Report to create a presentable report.

    We will not, however, discuss the the Valentina Database as it is impossible to grasp a new database server and cover its underlying mechanisms in such a short article.

    Valentina Studio

    Valentina Studio, the database management tool, has two versions. One is free and can be downloaded here. Another is the Pro version, with more features, priced at $200 per unit. Both versions support Windows, Mac and Linux platforms, making it a cross-platform tool.

    In this article, I will use Valentina Studio Pro. Thanks to Valentina for providing me with a key for my installation and evaluation.

    The startup speed of Valentina Studio is fast, faster than another tool that I am using. Its main interface has 3 panes:

    Fig. 1 The welcoming window

    • Servers: Provides CS based database management. It supports four types of “servers”: MySQL, PostgreSQL, Valentina and ODBC. It supports both local server and remote server connections. In my case, we can see there are two remote MySQL connections and one local connection. A red dot before the connection (or “Bookmark” in Valentina’s term) means the server is currently down. A green dot means it is up and running.
    • Database: Supports files based database management. Currently it supports Valentina DB and SQLite.
    • Projects: This is mainly used in report generation. A “report” generated by Valentina can reside locally and remotely. But it must have Valentina Report Server support (bundled with Valentina Server) to be called from an application. The report project manages the source, query and design of a report. We will cover this later.

    After selecting a server, the databases in that server will be displayed in the following cascading column view:

    This is my favorite view in Valentina Studio. In this view, different levels of entities (database, table, fields, links, indexes, etc) are displayed in columns in a cascading style. Selecting a database in column one, we can choose to display tables, links, views in column two; and for tables, view its fields, indexes, etc in column three. And the final level of details will be displayed in the right most pane. We can also create and modify an entity accordingly.

    Another view, less used in my case, is the tree view:

    When a table is selected, it displays the table data in a grid view; when a field is selected, only the column data is displayed. In the grid, we can right click on a record to export that record into CSV or SQL.

  8. PHP Extension Development with PHP-CPP: Object Oriented Code

    This entry is part 2 of 2 in the series Developing PHP Extensions with PHP-CPP

    PHP-CPP Part 2: The OO side

    In my Part 1 on building a PHP extension with PHP-CPP, we have seen some basics in using PHP-CPP to create our first skeleton PHP extension and covered some important terminologies.

    In this part, we further elaborate its OO features. We will mimic a complex number (in the form of 3+4i) class to demonstrate some more useful and powerful sides of the PHP-CPP library.

  9. Functional Testing in Symfony2

    In functional testing, we don’t look at the “correctness” of a single function, which should be verified by a Unit Test, but look at the bigger picture. The question answered by Functional Testing is: Is our app performing well in the sense that it displays the right content, corresponds to a user’s interaction, etc? This tutorial will focus on Functional Testing in our pre-built Symfony app.

  10. Data Fixtures in Symfony2

    Back when I first started to learn Symfony (1.x) with its Jobeet project, I thought the ability to load a bunch of test data into the database very useful.

    In this article, we will revisit this feature, which has been completely re-modeled and thus has a lot to teach us.

    Preparation

    In this article, we will have two 3rd party libraries to further enhance the power of Symfony.

    The first is the DoctrineFixturesBundle, used to load test data with Doctrine ORM, which is the default ORM in Symfony.

    Please follow the instructions in Symfony official documentation to set up and configure the DoctrineFixturesBundle. If you are familiar with Composer, the process should be easy and straightforward, no hassles.

    Next, we will install PHPUnit, the default test framework used by Symfony. Its official site offers the download of the latest phpunit.phar file. Simply save that file into the Symfony root directory and we are done. Symfony has come with a default and workable PHPUnit configuration file (app/phpunit.xml.dist). In normal circumstances, we shall keep this file unchanged and PHPUnit will work fine. We’ll use PHPUnit in the followup to this article, so just make sure you have it.

  11. Cursors in MySQL Stored Procedures

    After my previous article on Stored Procedures was published on SitePoint, I received quite a number of comments. One of them suggested further elaboration on CURSOR, an important feature in Stored Procedures. As cursors are a part of a Stored Procedure, we will elaborate a bit more on SP in this article as well. In […]

  12. Arbitrary Precision and Big Numbers in PHP

    Mathematical questions and applications always trigger me. Recently I just finished a book by Stephen Hawking “God Created The Integers” and being able to “talk” to those great mathematicians in history thrills me. This is the main reason I decided to write about this topic. In this article, we will review the PHP capability to […]

  13. Integrating Polymer/Dart and Symfony – Part 2

    This entry is part 2 of 2 in the series Integrating Polymer/Dart and Symfony

    This entry is part 2 of 2 in the series Integrating Polymer/Dart and SymfonyIn the previous article, we implemented a Dart widget in our Symfony app. Let's handle the rest now. This might get advanced, so grab a cup of coffee and pay close attention – I recommend you follow along only after completing Part […]

  14. Integrating Polymer/Dart and Symfony – Part 1

    This entry is part 1 of 2 in the series Integrating Polymer/Dart and Symfony

    This entry is part 1 of 2 in the series Integrating Polymer/Dart and SymfonyMy first four articles for SitePoint are devoted to Symfony (my favorite PHP framework) and Dart (my favorite HTML 5 development tool). In this 2-part series, we are going to look at how to integrate these two powerful tools together, i.e. to […]

  15. Stored Procedures in MySQL and PHP

    Put simply, a Stored Procedure ("SP") is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages. In this tutorial, we will see how to create an SP in MySQL and execute it in MySQL server and in PHP. Note: […]

  16. Unique Index and its Rationale

    This article is inspired by Craig's recent post "How to Use Unique Indexes in MySQL and Other Databases". There are a few more tightly related and equally important aspects regarding Primary Key (PK) and Unique Index (UI) which were not covered in that article. So in this article, we will take a further look at […]

  17. PHP and WMI – Dig deep into Windows with PHP

    There are many devices (servers, desktops, laptops, tablets, phones, etc) running a Windows operating system. Many of us who live in the nix based world have to work in this OS, or if we don't, we will, sooner or later. Besides the regular tools we can expect from a *nix system (say Apache, PHP, MySQL, […]

  18. Dart and PHP: A Legacy Animal Guess Game

    Back when I was learning programming on Apple II using BASIC, there was an Animal Guess Game. This game was a very primitive AI game: the computer tries to ask a few YES/NO questions and receives the answer from the user. Based on the answer, it may ask more Y/N questions until it tries to […]