I want to be free

Funny, but indexOf method doesn’t work in an empty array in Internet Explorer, the method is just not defined. Eh!

Forget about that and always use jQuery wrapper –

$.inArray(42, [31, 42, 53]);

You might be surprised, but a right choise of the project text encoding can affect the project file size and amount of bugs.

To avoid bugs of wrong presentation of text on your page, make sure that all database entities and the application server (PHP) use the same encoding. That helps to forget about issues connected with text presentation.

On database side, make sure you set the correct encoding to:

• database,
• tables,
• columns,
• import-export tools parameters (a big source of wrong encoding bugs),
• corresponding SQL server variables

On application server it’s usually just one query –

SET NAMES utf8

Pay attention, that utf8 might be not the best choise for your project: every non-English character needs 2-6 bytes of memory, so if you built a one-language (local) project with lots of database data, consider using a 1 byte encoding like windows-1251 and save about half of the space on server file system.

All of web applications I’ve built have an admin panel. Usually, that panel is a system to manage items: add a product, disable a user, delete a message.

All these create-update-delete functions are common, and interface for them can be standard.

That’s a way to kill some code – instead of creating a custom admin panel, you can build extremely fast a standard one.

One way to do that is to use admin generator tool of Symfony framework. As you can see in the screencast, it builds the admin interface automatically based on entities models.

Every application operates in a knowledge domain.

File browser works with files, directories and drives. Library software deals with books and editions. Geo tool must be aware of coordinates.

Idea is, developers can save time and lines of code if they agree the terms of the knowledge domain and be strict with it.

Of course, all of us use some terms — this rule is to use a well defined set of terms and to avoid synonyms.

Examples of these rule violations:

• “folder” vs. “directory”;
• “date” or “timestamp”;
• “partner” a.k.a. “affiliate”;
• short for “longitude” is “lon” or “lng”?
• does “website address” differ from “URL”?
• “region”, “area” and “subarea” — in contrast to ADM1, ADM2 and ADM3;
• the application I deal with at my job has: “brand”, “model”, “market” and “platform”, which are also often called “manufacturer”, “vehicle”, “locale” and “client” respectively.

If you apply this rule, you will not waste time creating the multiple mapper classes. More reusage, more OOP inheritance, more rapport. More motivation to work on a solid, well defined project with people, you talk the same language with. Isn’t this nice?

We at SunnyRentals are working a lot with geo data.

The last task I was solving was inclusion of all cities in a touristic regions bounds automatically.

Yes, there are MySQL spatial data types, so region border and cities coordinates can be stored in native data types, but the processing functions are not implemented right: they only operate on MBRs (minimum bounding rectangles) to make things simpler…

So, let’s create our own MySQL function for that.

There are different approaches to distinguish if a point is within a given polygon. I prefered the way to iterate the edges of the polygon and check where the point relatively to the edge is – at right or at left; if the point is always at the same side, it lies within the border.

So we came to a fact that we need 2 functions:

1. Distinguish the side at which the point lies from the line
2. Check that for every edge in the polygon.

Here is the first function:

DELIMITER //

DROP FUNCTION `GetPointPositionOfLine`//
CREATE FUNCTION `GetPointPositionOfLine`( a POINT, b POINT, p POINT) RETURNS tinyint NO SQL
  COMMENT 'Distinguish the side at which the point lies from the line'
BEGIN
  DECLARE s DOUBLE;
  SET s = (X(b) - X(a))*(Y(p) - Y(a)) - (Y(b) - Y(a))*(X(p) - X(a));
  IF s > 0 THEN
    RETURN 1;
  END IF;
  IF s < 0 THEN
    RETURN -1;
  END IF;
  RETURN 0;
END //

All three params are of type POINT. Points a and b are the beginning and end of the line. Point p is the point we check.

Here is the main function:

DELIMITER //

DROP FUNCTION `IsPointWithin`//
CREATE FUNCTION `IsPointWithin`( ls LINESTRING, p POINT) RETURNS tinyint(1) NO SQL
BEGIN
  DECLARE i INT;
  DECLARE a, b POINT;
  DECLARE LineStringNumPoints INT;
  DECLARE Position, PrevPosition TINYINT;

  SET i = 1;
  SET PrevPosition = 0;
  SET LineStringNumPoints = NumPoints(ls);

  WHILE i <= LineStringNumPoints DO
    SET a = PointN( ls, i);
    IF i = LineStringNumPoints THEN
      SET b = StartPoint( ls);
    ELSE
      SET b = PointN( ls, i+1);
    END IF;

    SET Position = GetPointPositionOfLine( a, b, p);
    IF Position <> 0 THEN
      IF Position <> PrevPosition AND PrevPosition <> 0 THEN
        RETURN FALSE;
      END IF;
      SET PrevPosition = Position;
    END IF;
    SET i = i + 1;
  END WHILE;
  RETURN TRUE;
END //

It checks if a point p lies within a polygon of type LINESTRING.

Usage example. Given you have coordinates of cities in the separate float columns (to keep them readable) lat and lng of city table, and the polygon is given as a string too — but of course you can use a column of type LINESTRING:

SELECT *
FROM `city` c
WHERE IsPointWithin(GeomFromText('LINESTRING(4 8, 15 16, 23 42)'), POINT(c.`lat`, c.`lng`));

The solution works pretty fast, especially if you first limit the cities by the rectangle that the polygon lies in.

At my previous job a team leader was a firm believer that <script> tag must be always written in a full form like:

<script type="text/javascript">

When you have lots of javascript pieces, it becomes exhausting.

Good news, there is a way to tell to browser, that default script language is JavaScript.

To do that, inlcude a special META-tag in the HEAD of your HTML document. So if you have an inline JavaScript code for a onclick attribute of a tag, browser has no doubts it’s JavaScript.

Anyway, I include these lines in my projects HTML:

<meta http-equiv="Content-Script-Type" content="text/javascript" />

After that you can have just this:

<script>

By the way, since it’s http-equiv stuff, you can set it at server side.

P.S. There is the same trick for CSS, but it’s seems there are no alternatives to write styles, so it is useless.

Hey! I gonna describe tricks that I use to have less code. Why it is important to have less code, you ask? Less code means less bugs, less support, less developer brains waste.

Today’s trick is extremely simple — when you have a long set of public, protected or private class properties, remove the visibility keyword set to each declaration but define it once as comma separated declaration.

Same applies to class constants as well.

Example:

// Before:
class Cat {
    const KINGDOM = 'Animalia';
    const PHYLUM  = 'Chordata';
    const FAMILY  = 'Felidae';

    public $tail;
    public $whisker = '\/';
    public $head;
    public $legs = array(1,2,3,4);
}

// After:
class Cat {
    const
        KINGDOM = 'Animalia',
        PHYLUM  = 'Chordata',
        FAMILY  = 'Felidae';

    public
        $tail,
        $whisker  = '\/',
        $head,
        $legs = array(1,2,3,4);
}

One benefit is that the code looks clear and it’s much easier to scan rather then to read.

Second benefit is when you need to change the visibility for a property, you don’t have to edit the visibility keyword near the property name (which might be an error prone process when you are tired) — you just move the line up or down, which usually has a shortcut in IDE.

Known disadvantage is that most documenting engines don’t support this ferature.

Once upon a time I was asked at my job to work on company website. There was a choice among existing CMS for the website to be driven by, plus I could think about implemention of something of my own — but you know what? I didn’t feel comfortable to start a CMS from scratch. Now I know why, exactly.

I found this nice article called Homebrew CMS (by Seth Gottlieb), which lists six points of a modern content management system which you as architect should consider before a start:

1. Versioning — it makes data model much more sophisticated;
2. Localization — should images be translated, what is your default language, etc;
3. Preview — the content being previewed must be connected with all the website and not visible to others;
4. Deployment — dependency management like images which are pasted in the content;
5. Usability — is probably the most common reason why companies abandon their home grown CMS;
6. Access control — your system must control not functions only, but also the data.

All in all, an open source free CMS might be the best choice for you too. Spare your energy for something else.

Things you would likely to check in your application. First story is about geo and localization features of your application.

1. Detect browser languages, find the one your application supports and set it as your application’s current languages. If it’s not set, first auto-detect country (see below) and then use the country’s main language.
2. Auto-detect country: first by IP (free database of countries IP addresses can be downloaded here), then by browser locale (en-US means it’s a USA user), then by auto-detected language (ru language gonna mean Russia. Yes, the user can be located in Ukraine or Belarus, not in Russia – but let’s face it, it’s much closer than nothing).
3. On the grounds you’ve detected the country, you can preset the currency for the user.
4. Save this data in user’s profile when he/she registers in your application. If your application cares about other languages the user can speak, you can parse the rest of browser languages and save them too.
5. If entities of your application can be located on map, set geo meta tags to tell the search engines about place your entity is at. (success story). Find out more on geotagging at Wikipedia.

List of countries with a set of supported languages and default currency code for each one can be got from GeoNames (countries info dump).

I think it’s a nice idea to contribute to open source project at least to just highlight this fact in your CV/resume when you are looking for a job.

Benefits:

• you show your level of commitment to something
• money is not the only motivator for you
• the code written by you is publically visible — you don’t need to explain what major design patterns you know, which technologies you are familiar with and how good you usually polish your code, that all can be seen

I think it can be compared with marriage. Remember that Alec Baldwin quote  from “The Departed” movie?

Marriage is an important part of getting ahead: lets people know you’re not a homo; married guy seems more stable; people see the ring, they think at least somebody can stand the son of a bitch; ladies see the ring, they know immediately you must have some cash or your cock must work.

Although Assembla is one of the best ticket systems, I hate it at times. Main reason is that their team plays with fonts and colors and thus changes the site appearence, and at the same time ignores the major issues.

Here is the list that irritates me the most:

1. When you create a ticket, the editor doesn’t work properly. For example, if you hightlight a URL with intention to mark it as a link, they still ask you for the URL in a prompt (a month ago they would even just replaced it with a default markup text like “[[http://server.com | URL TEXT]]“)
2. The same is WIKI based on tinyMCE — it fails so often, that 50% of times I have to open the content HTML source and fix things manually
3. Affiliate system — it pays you back only if you refered 3+ new users. I have just 2 leads, and I loose $80 every month…
4. Some time ago they had an offer to blog about them and get $5 reward — I wrote a post, and their marketing guy Ryan told me that they gonna pay me only after a referal would pay a bill — which was not mentioned in the offer.
5. If you want to export tickets data (for example, to count some stats regarding development performance), you will have to deal with their internal semi-JSON format with no documentation (only not-so-self-explanatory feilds names)
6. Previously they had a project name in project space URL, now they use a unreadable hash — if you work with more then 1 project, you’d feel how unhandy it is
7. There are 3 (three!) different SVN repositories types (SVN+Trac, External SVN, Source/SVN) in Tools which is confusing. Every one has its own set of possibilities — you have to choose what is more vital for you, you cannot have it all.
8. Current filter choise is lost if you leave Assembla page for 5-10 minutes, and you have to switch to it over again. The choise is saved in a HTTP-secure cookie (it means you cannot change it by JavaScript). Damn, why?
9. They don’t show hours total for tickets  no matter how you group them. It’s one of the most important things for developers, guys.
10. If you want just to get content of the project, you cannot.  I mean not SVN Checkout, but kind of SVN Export — download all folders/files without SVN client. They had a button that allowed to download the project as ZIP archieve, but now it’s not there. I have a few PHP projects which many people download and use, and they complain often about this missing feature.

All in all, Assembla still has a big way to go to maturity.

At times it’s necessary to know which queries the server runs right now.

Of course, you can try this statement:

SHOW PROCESSLIST

but if you need a more poweful tool, try MySQL Proxy.

Although it’s still an alpha version, it’s a handy tool to monitor, filter and manipulate your queries since there is Lua scripting language interpreter supported which is quite obvious in usage.

The MySQL Proxy (as can be understood from its title) can help you to see communication between a few MySQL servers and a few clients.

My usage pattern is that the Proxy listens to 4040 port on my local machine, and if I change the port setting in connection string of any application (my job project or even PhpMyAdmin), I can see the queries logged in a console window.

You can track lots of characteristics of queries — just take a look at example Lua scripts in the package that you’ve downloaded.

If you want to load a list of SQL files into your database on Windows, you can create a .cmd file with such content and run it.
Place it in the same folder where your .sql files are.

@echo off
SET DATABASE=my_database
SET USER=root
SET PASS=1
SET FORMAT=*.sql

FOR /F "usebackq" %%i IN (`dir /on /b %FORMAT%`) DO echo %%i && mysql --user=%USER% --password=%PASS% --default-character-set=utf8 %DATABASE% < %%i

Make sure, mysql command can be run. If no, either set the full path to mysql command tool, or add the path to PATH environment variable.

Reverse geocoding is a process of getting toponym name (city name) by its coordinates.

To make this, you can use a remote service like I have already described, or implement it yourself.

To do so you need two things:

1. A database of cities with coordinates (having a population value at least for major cities is a big plus)
2. A method to find a closest city

#1 is left for you, let’s talk about #2.

In the previous post I introduced the DISTANCE function (SQL) to count a distance between 2 points by coordinates. Let’s use it to find the biggest city closest to a given point:

DELIMITER //
DROP FUNCTION IF EXISTS REVERSE_GEOCODE;//
CREATE FUNCTION REVERSE_GEOCODE( latitude DOUBLE, longitude DOUBLE, radius DOUBLE )
    RETURNS DOUBLE READS SQL DATA DETERMINISTIC
    COMMENT 'Finds a city by coordinates given. Returns City ID.'
BEGIN
    DECLARE cityID INT(10) DEFAULT 0;
    DECLARE d DOUBLE DEFAULT 0; /* useless var */

    SELECT id, DISTANCE(`lat`, `lng`, latitude, longitude) AS dist
    INTO cityID, d
    FROM `geo_cities`
    WHERE lat BETWEEN FLOOR(latitude - 0.5)
                  AND CEIL(latitude  + 0.5)
      AND lng BETWEEN FLOOR(longitude- 0.5)
                  AND CEIL(longitude + 0.5)
    HAVING dist < radius
    ORDER BY IF(population > 0, population, 1) * (1/IF(dist > 0, dist, 1)) DESC
    LIMIT 1;

    RETURN cityID;
END; //
DELIMITER ;

Well, this function orders cities by (population and distance) function — so that the biggest closest city is returned.

WHERE clause is supposed to shorten the number of cities which are looked through; otherwise we would have to search through whole world’s cities. In my solution the search is limited by a reasonably small rectangle.

In the next post I’ll tell you how to find a biggest city depending on current Google Maps zoom, so that it won’t give you a small city near Paris if you click on France at a world-level map view.

Useful function.

DELIMITER //

DROP FUNCTION IF EXISTS DISTANCE; //

CREATE FUNCTION DISTANCE( lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE )
    RETURNS DOUBLE NO SQL DETERMINISTIC
    COMMENT 'counts distance (km) between 2 points on Earth surface'
BEGIN
    DECLARE dtor DOUBLE DEFAULT 57.295800;

    RETURN (6371 * acos(sin( lat1/dtor) * sin(lat2/dtor) +
        cos(lat1/dtor) * cos(lat2/dtor) *
        cos(lon2/dtor - lon1/dtor)));
END; //

DELIMITER ;

In next post I will explain how I used this function to implement a reverse geocoding — it’s useful when you need to find a closest city by given coordinates.

I attempted to setup Sphinx on Windows, but was not trivial — problem was that I couldn’t run the SearchD as a Windows service since I got 1067 error (“Process Terminated unexpectedly“).

It’s not obvious but helpful to know how you can get the error message the service crashes with. To do so, go to Control panel → Administration → Event Viewer. Here you can get all notices and error messages that the service produces:

If you get your service running and search tool gives you the correct results, but your PHP application gets FALSE as query result, try to see what SphinxClient’s getLastError() method returns:

$s = new SphinxClient;
$s->setServer("localhost", 3312);
...
$result = $s->query('test');
echo $s->getLastError();

In my case there was a following message:

searchd error:
  client version is higher than daemon version
  (client is v.1.22, daemon is v.1.19)

At times, some records in a recordset are more important than others — e.g. a featured product in a products list. In such a case you can make 2 requests to get the featured item first, then to load other N-1 items, and then display them.

If you want to make it by one move, you can use a feature of preferrable sorting — you can define what value and in which field goes first if such value is present.

For instance, you want to show an item with item_id=15 first:

SELECT *
FROM items
ORDER BY item_id IN (15) DESC

Which returns something like (featured item goes first):

+----------+------------+
| item_id  | name       |
+----------+------------+
| 15       | Product 15 |
| 32       | Product 32 |
| 31       | Product 31 |
| 30       | Product 30 |
| 29       | Product 29 |
| ...      | ...        |
+----------+------------+

If you import unicode text into latin1 database column, the symbols would be screwed up — russian symbols become a shit like “залоговый депозит”.

To convert such quickly (as a test) you can use Lebedev’s convertor.

To convert the whole table do the following (thanks to dull.ru):

1. The most important step: dump such data in a file with mysqldump

   mysqldump -u user -p --default-character-set=latin1 --skip-set-charset --no-create-info --extended-insert --complete-insert dbname table > dbname.sql

   If not the whole table data is bad, create another table (CREATE TABLE t2 LIKE t1) and copy wrong rows to the new table.

2. Replace latin1 by utf8 in the file
   This can be done manually in your editor or by this command:

   sed -r 's/latin1/utf8/g' dbname.sql > dbname_utf.sql

3. Convert your latin1-table to utf8 (and maybe truncate it):

   ALTER TABLE `table` CONVERT TO CHARACTER SET 'utf8';

4. Import the utf8 data back to the table:

   mysql -u user -p --default-character-set=utf8 dbname < dbname_utf.sql

That’s it.

It’s handy to use View Helpers in Zend FW driven project. For example, you want to make an in-place tracker (e.g. Google Analytics) — you create a helper class My_View_Helper_Tracker inherited from Zend_View_Helper, Zend finds its automatically and then you are free to use your helper method:

echo $this->tracker( $trackerID );

The question is what you gonna do if you want a base class for a family of trackers?

It’s not so obvoius due to naming conventions.

Let’s say you want 2 kinds of trackers: Google Analytics and Euroads.

1. You create such files structure:

library
 - My
   - View
    - Helper
      - Tracker
        Abstract.php
        - Google
          Page.php
        - Euroads
          Owner.php
          Guest.php

2. You name your classes as:

• My_View_Helper_Tracker_Euroads_Guest (extends My_View_Helper_Tracker_Abstract)
• … so on …

3. Class methods are:

public function tracker_euroads_guest(...) ...

4. And the trickiest part: the helper call:

echo $this->tracker_Euroads_Guest

During my work on sunnyrentals.com I’ve got a task to add 2 closest airports to every property an owner creates.

I implemented it quite fast since we have a database of airports with coordinates.

While playing with this feature, we found out that it is not good enough — the real task must be to add 2 closest and biggest airports. The problem is that we don’t have any data in airports DB to guess how big or famous a particular airport is.

So we need to rate every airport somehow…

The solution we found was simple — we need to google for the airport name and get the search results count. The count can be considered as rating value — London Heathrow airport has 2.33 million results while Kiev Zhulyany airport has only 0.77 mln which looks fair.

Several things to pay attention to:

• the query we formed was [city name] + [airport name] + ‘ airport’
• if this query gives zero result, I omit the city name — at times it hepls
• we put the query into quotes to google for the exact phrase, otherwise the London City airport gets the highest rating due to the fact that “city” is a general term
• if the airport name includes the city name (Melbourne Intl), we omit the city name — ”Melbourne Intl airport” is better then “Melbourne Melbourne Intl airport“
• in addition to previous idea — if the airport name sounds like the city name, we omit the city name as well. Example: Narsarsuaq airport in Narssarssuaq city. I used soundex function for this comparison — it’s present in PHP and MySQL.

To get the google results you can use the Google Search API:

$queryTemplate = 'http://ajax.googleapis.com/ajax/services/search/web?v=1.0&q=%s';
$airportQuery = '"London Heathrow airport"';
$query = sprintf( $queryTemplate, urlencode( $airportQuery ) );
$json = json_decode( file_get_contents( $query ), 1 );
$rating = (int)$json['responseData']['cursor']['estimatedResultCount'];