I just realised how much time I spend answering questions on PHPBuilder forums. Now it started out as a good way for me to learn stuff and some days I go there to get my brain working before getting stuck into my own coding, but lately it has become a way of avoiding my own ‘work’ - I say ‘work’ like that cos I ain’t getting paid for anything at the moment. What I am, or should be doing is getting into newest versions of php and mysql; that is why I opened this hosting account.

Only I end up sucked into long dialogues with people who have old version code and problems, which means I learn very little. What has got me on this rant is the realisation that a lot of them are getting paid for the stuff they are doing and they can’t code worth a damn. Yet I can’t get any paid work or even an interview right now Something wrong here. I knock out some example code in 10 minutes then spend the next 2 days trying to get them to implement and debug it.

So I’m going to have to stop, which is a shame cos I like helping people and I like solving problems just for their own sake. But it aint paying my mortgage

It turned out to be a lot easier than I expected to convert sorttable.js to use the tag class to define sort type. So now by applying a class to the first row one can control what type of sorting is used.

Now this modification was not strictly necessary and in some situations it would not even be desirable, but it does allow much tighter control when the column contents are known beforehand. Since this is also the case when applying CSS through class for right-align numeric data etc, the same class works for both.

I’ve uploaded the file incase anyone wants it - lets see if wp shows the link

sorttable_ajax.js

Just had my first comment, from Stuart Langridge no less. Timely as well because I was just here to post on my progress.

The problem with formatted numbers was that a thousands separator in the first row caused the column to be interpretted as ’string’ data and hence sorted lexically instead of numerically. This problem arose in my testing after the column was sorted descending, but would have been there for any formatted numbers.
I have fixed it by extending the regex to recognise both (,) and (%) as valid numeric symbols. In the process I merged the ts_sort_currency and ts_sort_numeric merged functions - and the associated type tests - as currency is really numeric data anyway.

The regex for the sort type test now reads:

if (itm.match(/^[\d\.,%£€\$]+$/)) sortfn = ts_sort_numeric;

allowing for Euros and percentages as well.

My next step will be to extend it for all currencies as that is the way of the web. This will be achieved when I move to column class for data and sort typing instead of internal regex, as there is no way I’m gonna find an authorative list of currency symbols - which will probably change over time anyway.

{I wonder how you get spell checking working in wp? save me having to re-edit all the time}

And of course the solution to the numeric miss-sort was obvious in the end. Once sorted descending a thousands seperator (,) will cause the column data type to be identified as ’string’ and trigger a string sort.

Simple fix would be to just re-order the type tests so that numeric is applied first.

Another fix would be to modify the regex pattern to include comma so that numeric data is not mis-typed.
However, my inclination is an all together different approach. It already relies on class ’sortable’ to identify tables to be made sortable. I see no reason not to extend this to column classes that define the sort type - bypassing internal type evaluation all together. Since most times a column class already exists to control presentation like text alignment, this will just enforce some standardisation over class names. The biggest problem is going to be allowing multiple classes. Have to think about that.
Of course, to keep this as a general purpose script type-evaluation should remain in the absense of a specified column class.

Just got myself a new theme - had to have it when I saw the picture of Che, Cuba being one of my favourite places and all. Which remineds me I have to add a new category: Philosophy and politics (note the small p - plotics being the bastard child).
How do you like it world?

I’ve been occupied with Stuart’s sorttable tables script for a couple of days now on-and-off. A very fine script that I must congratualte him on, except that it does some things I don’t like and want to change.
The first problem was with the way it treats numeric columns. It works fine with fully formatted currency columns but falls over with straight numeric columns that contain thousands seperators. Not his fault really, js parseInteger() and parseFloat() don’t like thousands seperators. Written by geeks who don’t realise most of us have to work with pretty displays I suppose.

So I dealt with that by using Staurt’s own regular expression from his currency sort function to strip the formatting from numeric data. Works fine the first time you sort the column up and down, but then it jams up again. So now I’m working on the logic path to find out why it it will sort desscending once but not again.
It seems to be linked to another thing I want to change in sortable.js anyway: it seems to be remembering the direction a specific column was last sorted in. Now that at first sounds fine: sorted ascending so next time you obviously want to sort descending, right? Well no, not if the user has been sorting by other columns in between. It only leads to confusion.

So once I have fully sussed out the proccessing logic I will be modifying the script so that the sort order processing is simpler more consistent: and in the process elliminate this numeric bug whereby it bypasses the regex on numeric columns and so allow the display of ‘pretty’ data.

The third thing that needs modifying is the way it will deal with mixed numeric-character data like 3Com or 102XYZ. Because the script just looks at the first char in the first row of a column to determine data type, if that happens to be an integer then the script will fail entirely. So it looks like I’m gonne be adding some new regex, which is great since regukar expressions are not my stong point - infact I’ve hardly ever used them. Looks like a good reason to learn.

In case anyone thinks I’m criticising Stuart’s work too much then just let me say again that I congratulate him on this utility, I certainly could not have done it from scratch myself, not in js I couldn’t. This is all just part of what makes open source great, cos Stuart built some of his code using other peoples’ work as well.

{get me, writting this for all the world as if other people are going to read it!!}

Just posted this as a solution to someone’s problem on PHPBuilder and would like to remember it:

How to insert a value into one table only if it is not present in a second table

“INSERT INTO application(username)
SELECT n
FROM (SELECT ‘myUsername’ AS n) AS t
LEFT JOIN users u ON ( t.n = u.username )
WHERE u.username IS NULL”

See, I knew this blogging would come in handy - now I can archive all the rare SQL solutions I come up with and forget because I don’t design DBs that need them, or rather I design my application architecture so that this sort of things is not needed in the db.

Still this was an interesting little problem and I quite enjoyed solving it. Nice to know I havn’t lost my touch; even though I’m sure there are untold tutorials around out there where this is already a standard solution.

My very first post in my very first blog, so of course I have to start with:

 Hello World

Don’t know if I’ll be posting much here so we’ll see what happens.