Paul's Dev Blog –

I can divide by zero. Twice.

Archive for the ‘Database’ Category

Sort out your records – MySQL style

without comments

So I had a predicament. I had to sort out a list of records. Not amazingly difficult, I know, but the client wanted it sorted in a specific order. The Status (which is what I am sorting by) was displayed as text on the front end, but was a number in the database. So my task was to order this list by a non-sequential order that I can define.

As is usual, I played around a bit before I got it working. Then I started looking for a better (and non code based) way to go about it. The answer came in the form of the ORDER BY FIELD() MySQL function.

Usually, your ordered statement looks something like this:

SELECT * FROM where_ever ORDER BY Column1, Column2

Simple, but not helpful in this case. But adding in the ORDER BY FIELD command, our SQL statement looks like this:

SELECT * FROM where_ever ORDER BY FIELD(fieldToOrderBy,4,1,5,6)

So now when you display your records, everything with a “Status” of “4” will be first, “1” will be second, and so on. If there are more status values possible those will be put to the back of the order, as the pre-defined values take precedence over the rest of them.

You can also use text as an ordering value (which is a good thing sometimes), which will look like so:

SELECT * FROM where_ever ORDER BY FIELD(fieldToOrderBy,’Medium’,’Large’,’Small’)

The above will be helpful if you want to show all the Medium items first, Large next, and so on.

To sum up, before you do like I did and spend a lot of time going about things via server side code, re-read the MySQL (or whatever database system is you poison) documentation. You are almost guaranteed to find some useful shortcut to save time, code, and headache.


Written by iGonzo

January 5th, 2011 at 4:35 pm

Posted in Database,Programming and Classic ASP

with 2 comments

You know, a lot of people are moving off to other, cooler programming languages. PHP, ASP.NET, Ruby, all of these are seen as superior to Classic ASP; at one time the greatest language to use for dynamic data driven sites.

More often than I care to admit, I get asked “Why don’t you do it in [insert current language here] to do that? It’s easier”. True, but I have been using ASP for 11 years and am quite good at it if I do say so myself. Besides, as long as it can process XML, I see little reason to change our entire code base. Which brings me to the actual crux of this post: Using Classic ASP with

More specifically, you can use any programming language with the Authorize API as long as you can process XML. But since I use primarily Classic ASP, that is what we have to use.

Authorize, in their wisdom, has seen fit to provide examples of accessing their API. One of these examples includes an ASP version. At first I totally ignored these, assuming that they could not accomplish what the client needed (which is integration with the Authorize CIM system). And so I toiled for many an hour creating functions and testing. Eventually I came across a problem I could not bull my way through, so I humbled myself and looked at their example code.

My first reaction was, of course, chagrin. Their ASP examples were performing functions that I needed. Precisely what I needed. *urg*. So, over the next 20 minutes, I functionalized their examples, added in our data points, and low and behold I got a positive response without any errors. I even tried to create errors and still the data went through.

Begin head banging on desk.

So then, after my headache went away, I grabbed every code example they had and was able to add in a whole mess of features and functions that not only added robustness to the site, but some serious value for the client. The transmission functions I created are able to take any form of XML request and sent it to Authorize and get a response back. After a quick check my reply data is added to the database and its on to the next thing: which for the client is making money (businesses are funny that way).

The lesson here, trolls and girls, is to not scoff at API example code. We can all look at the PDF of how to form this or that request, and what to expect back in response, but seeing a bit of sample code execute without errors is, in my opinion, a very uplifting thing to experience.

Now, if I can just figure out how this FBML crap, I think I’ll be golden.


Written by iGonzo

November 5th, 2010 at 1:34 pm

Search Arrays without server code (sorta)

without comments

So it happens that I have a database field value that is a comma separated string of numbers. Classically, I would grab the field, turn it into an array, and search the array. Somewhat cumbersome and not really worth the effort of another function. That, and I would have to grab every record in the database, search through the applicable fields, and filter out the record(s) I need.

Me, being lazy, want an easier way to do it. Then I found this:

Then I came across this:

All I have to do is construct a statement like this:

SELECT * FROM table_name WHERE FIND_IN_SET(‘thevalue‘,Field_To_Search)

You can also use a variable in place of ‘thevalue‘ if you need to search for, say, a passed parameter.

In the end, I was given a list of one or two records that I can easily do the rest of the work for, instead of grabbing every record in the table and filtering after the search.

For the lazy folks, this is quite a good thing. For the non-lazy folks, this is still a good thing. The above query not only executes faster, the code weight is greatly reduced and also runs faster (tests show about a 40% increase in speed and a 50% reduction in code execution time).

Final thoughts; this now leaves me more time to either work on something else, or go grab some food. I vote food.


Written by iGonzo

October 15th, 2010 at 11:53 am

Posted in Database,Programming

Tagged with , ,