I found this great PHP input validation class called GUMP. It is only ~32Kb, one file. Looks perfect to integrate with Fat Free Framework.

Test status update from my phone.

title one

I've been wearing my sysadmin hat all morning and I like it. Love spinning up and customizing new instances.

Crazy amounts of new knowledge

A couple days ago I passed my 5 month mark at my new job as a Senior Developer at the Canary Collective. It's been really great working with some super talented people like Ben, Blake and Todd. But also, holy crap the new stuff I've been learning!

• Fat-Free PHP Framework
• Ion Auth CI authentication library
• Tank Auth CI authentication library
• PayPal API, payments and subscriptions
• Authorize.net payment system
• INK filepicker API
• Cycle2 jQuery slideshow plugin
• Insane levels of front-end js magic

There are more I just can't think of them at the moment. Point is, getting to learn a lot which is awesome. Also, dealing with a lot more front-end work that I was used to. It is pushing me out of my comfort-bubble which is a good thing.

Moving options between select boxes

Just a quick post. I needed to be able to display a multi-select box showing all the options available to a user, and allow the user to select one or several, and 'move' them to their list of chosen options. Then, when the form is submitted, make sure only the items in the chosen list of options is submitted.

Fairly easy right? It was, but took long enough that I wanted to share and tag appropriately, in case people come looking for it. :)

Image of what I wanted:

Link to my working JSFiddle.

HTML:

 
   
        One
        Two
        Three
        Four
        Five
    
    in
    out
    
        Seven
    
    

Javascript:

$('.go_in').click(function() {
    return !$('.all_options option:selected').remove().appendTo('.chosen_options');
});
$('.go_out').click(function() {
   return !$('.chosen_options option:selected').remove().appendTo('.all_options'); 
});

$('form').submit(function() {
    $('.all_options option').prop('selected','');
    $('.chosen_options option').prop('selected','selected');
    alert($(this).serialize());
});

Victory over a big wall

Progress on Lemon-filling has been going well, though I hit a big wall a couple of days ago. I was having problems getting the exact results I wanted with my query.

The problem was, when requesting a pages terms, I wanted all the terms and their definitions, like this if asking for locale_id=1:

+---------------+------------------------------------------+
| terms.value   | rosetta.value                            |
+---------------+------------------------------------------+
| lname         | Username:                                |
| lpass         | Password:                                |
| welcome_blurb | Welcome to appland! Please log in below. |
+---------------+------------------------------------------+

If I requested a locale that existed, but didn't have some or any of the terms defined yet, I wanted this:

+---------------+---------------+
| terms.value   | rosetta.value |
+---------------+---------------+
| lname         |               |
| lpass         |               |
| welcome_blurb |               |
+---------------+---------------+

But I was just getting an empty result set. Spent a while agonizing over it, eventually took the time to write up my problem and posted it on dba.stackexchange.com. Within minutes help arrived!

It pointed me in the right direction, but the solution offered:

SET @given_locale_id = 1;
SELECT terms_value,MAX(rosetta_value) rosetta_value
FROM
(
    SELECT T.value terms_value,R.value rosetta_value,R.locale_id
    FROM terms T LEFT JOIN rosetta R
    ON T.terms_id=R.terms_id
    UNION
    SELECT T.value,'',@given_locale_id FROM terms T
) A
WHERE
    locale_id = @given_locale_id
GROUP By terms_value;

Only worked when asking for all terms. I also need a query where I give it the page key, and it only gives be back the terms for that page. Tinkered a lot, and eventually came up with a solution:

SET @given_locale_id = 1;
SELECT terms_value, MAX( rosetta_value ) rosetta_value
FROM (
 
    SELECT T.value terms_value, T.terms_id, R.value rosetta_value, R.locale_id
    FROM terms T
    LEFT JOIN rosetta R ON T.terms_id = R.terms_id
    UNION
    SELECT T.value, T.terms_id, '', @given_locale_id
    FROM terms T        
 
)A
WHERE locale_id =@given_locale_id AND terms_id IN (SELECT terms_id FROM page_group INNER JOIN page ON page.page_id=page_group.page_id WHERE page.value='sign_in')
GROUP BY terms_value;

It is one of the most complex queries I've had to write. I will probably need to optimize some too, but I'm waiting until I know for sure that my schema design will work before putting in foreign key constraints, which should help.

Lemon-Filling schema works!

I created my schema for lemon-filling, added some data and tried my query. It worked on the first attempt. I was overjoyed. : )

Here we have all the data in their tables. Our demo data is just 3 terms: the 'username ', 'password ', and intro text. It is in English and German. There are two pages: 'sign_in' and 'overview'.

mysql> SELECT * FROM locale;
+-----------+---------+
| locale_id | value   |
+-----------+---------+
|         1 | English |
|         2 | Deutch  |
+-----------+---------+

mysql> SELECT * FROM terms;
+----------+---------------+
| terms_id | value         |
+----------+---------------+
|        1 | lname         |
|        2 | lpass         |
|        3 | welcome_blurb |
+----------+---------------+

mysql> SELECT * FROM rosetta;
+------------+-----------+----------+-------------------------------------------------+
| rosetta_id | locale_id | terms_id | value                                           |
+------------+-----------+----------+-------------------------------------------------+
|          1 |         1 |        1 | Username:                                       |
|          2 |         2 |        1 | Benutzername:                                   |
|          3 |         1 |        2 | Password:                                       |
|          4 |         2 |        2 | Passwort:                                       |
|          5 |         1 |        3 | Welcome to appland! Please log in below.        |
|          6 |         2 |        3 | Welcome to appland Bitte melden Sie sich unten. |
+------------+-----------+----------+-------------------------------------------------+

mysql> SELECT * FROM page;
+---------+----------+
| page_id | value    |
+---------+----------+
|       1 | sign_in  |
|       2 | overview |
+---------+----------+

mysql> SELECT * FROM page_group;
+---------------+---------+----------+
| page_group_id | page_id | terms_id |
+---------------+---------+----------+
|             1 |       1 |        1 |
|             2 |       1 |        2 |
|             3 |       1 |        3 |
+---------------+---------+----------+

Here are the queries. Note all we give it in the where clause is the page we want, and the locale id.

mysql> SELECT terms.value,
    ->    rosetta.value       
    -> FROM locale
    -> RIGHT JOIN rosetta ON locale.locale_id=rosetta.locale_id
    -> INNER JOIN terms ON terms.terms_id=rosetta.terms_id
    -> INNER JOIN page_group ON page_group.terms_id=rosetta.terms_id
    -> INNER JOIN page ON page.page_id=page_group.page_id
    -> WHERE page.value='sign_in' AND locale.locale_id=1
    -> ORDER BY terms.terms_id;
+---------------+------------------------------------------+
| value         | value                                    |
+---------------+------------------------------------------+
| lname         | Username:                                |
| lpass         | Password:                                |
| welcome_blurb | Welcome to appland! Please log in below. |
+---------------+------------------------------------------+

mysql> SELECT terms.value,
    ->    rosetta.value       
    -> FROM locale
    -> RIGHT JOIN rosetta ON locale.locale_id=rosetta.locale_id
    -> INNER JOIN terms ON terms.terms_id=rosetta.terms_id
    -> INNER JOIN page_group ON page_group.terms_id=rosetta.terms_id
    -> INNER JOIN page ON page.page_id=page_group.page_id
    -> WHERE page.value='sign_in' AND locale.locale_id=2
    -> ORDER BY terms.terms_id;
+---------------+-------------------------------------------------+
| value         | value                                           |
+---------------+-------------------------------------------------+
| lname         | Benutzername:                                   |
| lpass         | Passwort:                                       |
| welcome_blurb | Welcome to appland Bitte melden Sie sich unten. |
+---------------+-------------------------------------------------+

I love it; data is totally normalized, yet bundled together in an efficient manner.

Next course of action is to build the UI to input and manage the lemon filling content.

For the curious, here is the SQL to generate the necessary tables.

CREATE TABLE IF NOT EXISTS locale (
    locale_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    value VARCHAR(90) NOT NULL,
    PRIMARY KEY(locale_id)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS terms (
    terms_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    value VARCHAR(90) NOT NULL,
    PRIMARY KEY(terms_id)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS rosetta (
    rosetta_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    locale_id SMALLINT UNSIGNED NOT NULL,
    terms_id INT UNSIGNED NOT NULL,
    value VARCHAR(19999) NOT NULL,
    PRIMARY KEY(rosetta_id),
    INDEX rlocale_index (locale_id ASC),
    INDEX rterms_index (terms_id ASC)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS page (
    page_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    value VARCHAR(90) NOT NULL,
    PRIMARY KEY(page_id)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;
 
CREATE TABLE IF NOT EXISTS page_group (
    page_group_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    page_id INT UNSIGNED NOT NULL,
    terms_id INT UNSIGNED NOT NULL,
    PRIMARY KEY(page_group_id),
    INDEX pgpage_index (page_id ASC),
    INDEX pgterms_index (terms_id ASC)
) 
ENGINE=InnoDB
CHARACTER SET utf8 
COLLATE utf8_general_ci;

Reflections on 2012, and on to 2013

It's a new year. 2012 was not at all how I expected it to be, in the best of ways. Having a child continues to be a source of more joy that I thought was possible in a family. I am spending lots of time with the family, because really, that is the most important thing. I won't care one fig in 40 years whether or not I was super productive in December 2012, for example. Right now the little guy always wants to hang out and play with me, and I take him up on it.

2013 is going to be a busy one. Essentially the first half of the year is going to be focused entirely on the family, on our little girl being born, and her newborn months. We're in the process of looking for a new home, and that plus packing/moving will take up a large portion as well. I'm hopeful by around July we'll be more settled in our new (as yet unfound) home.

I am hoping to have Nickelpinch open for business in 2013. Also I want to make another, more involved Python app. I feel like Nickelpinch is my last hurrah in the PHP world. After that, it'll be time to move on. The reasons for using PHP for Nickelpinch are still valid; it is supported universally on shared hosting, which is what the majority of self-hosted sites will use.

A couple other things; I do want to watch less movies/shows this year. They are a great escape, and definitely will occur when we have a newborn in the house. (Doing anything other than total crashing when the baby crashes isn't really an option, heh.) But after, I am going to prune back watching time, and count on at least 4 hrs a week on projects (in the evening). I can always find excuses, but no one is stopping me from hitting my goals but myself.

I hope everyone else had a great 2012. Onward to 2013!

Announcing Lemon-filling!

This is kind of a follow up to my last post. I've realized the UI localization schema I came up with is pretty nice. It will need some sort of UI to add the terms, pages, etc. I'm building that out first. I'm calling it 'Lemon-filling'. On Github.

I'm hoping to have it done before our little girl arrives, because after that I will be in babyland for a while. The best land!

Designing the UI localization of Nickelpinch

(Cross-posted on Nickelpinch.org)

One feature of Nickelpinch that is critical is the multi-language support. Not just for the descriptions entered in for purchases, etc, but the actual UI itself. In coming up with the MySQL schema to handle this, it ended up breaking into two areas:

Term Information

When accessing a specific term; I wanted it to be based on term, not some ID. When building the view, I want to have lines like: $term['amount']. I ended up with three tables to support this.

• locale
  • locale_id
  • value - English, Deutch, etc
• terms
  • terms_id
  • value - In English; 'amount', 'description'
• rosetta
  • rosetta_id
  • locale_id
  • terms_id
  • value - Deutch for amount, description, etc

The locale table stores all the possible language options. The terms table stores all the terms used in the UI, and the value is in English. These will eventually make up the 'key' that all terms will be referenced as in the views. The rosetta table is the pairing of them; with that we can ask "Get me the term in a specific language".

Page Information

When querying for all the terms for a specific page, I needed terms to be grouped by the pages they're on. I could have built queries for each page, but it made more sense to build in the idea of page and page_groups.

• page
  • page_id
  • value - The page name; think 'add_entry', 'overview'
• page_group - Group all terms to a page, for easy pulling
  • page_group_id
  • page_id
  • terms_id

The page table is just the shortish list of all the pages/views in Nickelpinch. The page_group table is what stores the grouping of terms to a specific page.

What I ended up building is a great relational model that can handle any number of pages, terms and languages, all the while having strong normalization. I can make just one query for all the pages UI component, and just need to give it the page I'm looking for (page.value) and the language I need the terms in (locale.locale_id). With a couple JOINs in there of course. :)