MySQL Sort Order with NULL

As a MySQL programmer, I often need to sort lists in alphabetical or numeric order, but many times the columns I use have null values. When ORDER BY col ASC is used, null vales appear at the top of the list; with DESC they appear at the bottom. Often, I would like a list to sort in alphabetical or numeric order, but with NULL at the bottom.
Example: You have a table that stores user information, including nicknames. You users can search this table so that they can find friends on your site. When the sort by nickname, you would like for people without nicknames to be on the bottom of the list.

Try:

   SELECT * FROM people ORDER BY nickname ASC

Doesn’t work, does it? It returns something like:

All the NULL values are on top.

What to do? Try this:

 SELECT * , nickname IS NULL AS isnull FROM people ORDER BY isnull ASC, nickname ASC

What’s Happening?

[nickname IS NULL AS isnull] adds a column named “isnull” and fills it with a 0 if there is a value for nickname, and 1 if nickname is NULL. We sort first on this “isnull” column, which puts nicknames first, and null values last. We then sort alphabetically by nickname.

Knowledge is power. Power corrupts. You are now more corrupted.

PS
If you have null values and empty strings you wish to force to the end you will need to use the IF function:

SELECT * , IF(nickname IS NULL or nickname='', 1, 0) AS isnull FROM people ORDER BY isnull ASC, nickname ASC

htaccess Mod-Rewrite for WWW Prefix

Doing a 301 redirect for domain names with regards to www prefix. If you have a domain name, i.e. domain.com, you may want to use only www.domain.com to display your site instead of domain.com, or vice versa. To do so, it is very simple, as long as your server supports mod_rewrite and allows users to configure the .htaccess file to do a redirection.

First of all, before jumping onto the wagon and following what others do, you have got to ask yourself why do you want to do it? There are several reasons why people do that, and why people don’t. For example, Microsoft redirects microsoft.com to use the www-prefix, Slashdot chooses to use slashdot.org instead of www.slashdot.org whereas Digg allows the use of either digg.com or www.digg.com. Therefore, what is the underlying reason for doing so, or not doing.Generally, webmasters tend to improve site ranking. A domain name, ie. domain.com, which can be accessible via www.domain.com and domain.com is interpreted as two different sites by the search engine. For example, search for Digg in Google, and you will find that digg.com and www.digg.com exist. Although this improve visibility in search engine, it also has its drawback. Having two sites mean, the ranking of that domain is divided (unequally; dependent on inbound and outbound links) into two. It is fine if the site is highly ranked, and users have no problem discovering the site. However, for sites which do not have very high ranking, it is perhaps more ideal to confine to using one particular domain name for accessing the content.While a lot of webmasters are highly interested in improving sites’ Search Engine Optimization (SEO), Page Rank (PR) and Search Engine Result Page (SERP) for business, others are just happy to create sites for personal use. If you belong to the latter, then it is perhaps not so important to worry about www-prefix on your domain names, unless you feel that it looks nicer with (or without) the www-prefix; otherwise, this article might just be useful for you.To perform a redirect from domain.com to www.domain.com, insert the following code into your .htaccess file.

To perform a redirect from www site to non-www site, use the following code in .htaccess file.

The .htaccess file is simply a text file with the filename .htaccess, and it should be stored at the root directory of your site.

Note, while using .htaccess file, it is important to use it carefully. Wrongly inserted command may render your site not workable.

Importing Methods from an unrelated Class in PHP5

OK, here’s a scenario:
You are developing a Content Managment System. You have some information that is available to the public. New information, however, is kept in a sandbox until it is approved by management.You have a class for public information, and a class for private information. You want to use the same method for previewing your sandbox information as you do for formating your public information.
Solutions:
1. Take the easy way out. Copy the method from public_information to sandbox_information. This way has serious disadvatages. You are copying code instead of reusing it. That’s not cool. Also, if you change the public_information::format method, you have to manualy update the sandbox_information::preview method.

’.$this->info.’’;
   }
}
class sandbox_information {
   private $info = ‘Sandbox’;
   function preview() {
      return ‘’.$this->info.’’;
   }
}
   $pub = new public_information;
   echo $pub->format();
   $san = new sandbox_information;
   echo $san->preview();
?>

2. Use a parent/child relationship. Have an information class and extend it. Take careful note to use protected and not private propeties. This is a great solution, but may not be possible.

’.$this->info.’’;
		}
	}

	class public_information extends information {
		protected $info = ‘Public’;
	}

	class sandbox_information extends information {
		protected $info = ‘Sandbox’;
		function preview() {
			return $this->format();
		}
	}
	$pub = new public_information;
	echo $pub->format();
	$san = new sandbox_information;
	echo $san->preview();
?>

3. Let’s say you can’t do number 2 because the classes are already children. PHP5 allows you to make a generic call to a method of a different class. If you do this inside a method, you can even use $this. But wait, you say, the manual says you can’t make a static method call to a method that uses $this. It turns out that that’s only true if you are doing it when not in object context. When you do it from a class method, you are in object context. (NOTE, this may be considered a BUG. PHP6 will send an ERROR_FAILURE on this when it comes out.)

’.$this->info.’’;
		}
	}

	class sandbox_information extends sandbox_classes{
		public $info = ‘Sandbox’;
		function preview() {
			return public_information::format();
		}
	}
	$pub = new public_information;
	echo $pub->format();
	$san = new sandbox_information;
	echo $san->preview();
	// Fatal error: Using $this when not in object context
	//public_information::format();
?>

Introduction to .htaccess

This work in constant progress is some collected wisdom, stuff I’ve learned on the topic of .htaccess hacking, commands I’ve used successfully in the past, on a variety of server setups, and in most cases still do. You may have to tweak the examples some to get the desired result, though, and a reliable test server is a powerful ally, preferably one with a very similar setup to your “live” server. Okay, to begin..

..a win32 Apache mirror of corz.org

.htaccess files are invisible

There’s a good reason why you won’t see .htaccess files on the web; almost every web server in the world is configured to ignore them, by default. Same goes for most operating systems. Mainly it’s the dot “.” at the start, you see?

If you don’t see, you’ll need to disable your operating system’s invisible file functions, or use a text editor that allows you to open hidden files, something like bbedit on the Mac platform. On windows, showing invisibles in explorer should allow any text editor to open them, and most decent editors to save them too**. Linux dudes know how to find them without any help from me.

that same folder, as seen from Mac OS X

In both images, the operating system has been instructed to display invisible files. ugly, but necessary sometimes. You will also need to instruct your ftp client to do the same.

By the way; the windows screencap is more recent than the mac one, moved files are likely being handled by my clever 404 script.

** even notepad can save files beginning with a dot, if you put double-quotes around the name when you save it; i.e.. “.htaccess”. You can also use your ftp client to rename files beginning with a dot, even on your local filesystem; works great in FileZilla.

What are .htaccess files anyway?

Simply put, they are invisible plain text files where one can store server directives. Server directives are anything you might put in an Apache config file (httpd.conf) or even a php.ini**, but unlike those “master” directive files, these .htaccess directives apply only to the folder in which the .htaccess file resides, and all the folders inside.

This ability to plant .htaccess files in any directory of our site allows us to set up a finely-grained tree of server directives, each subfolder inheriting properties from its parent, whilst at the same time adding to, or over-riding certain directives with its own .htaccess file. For instance, you could use .htacces to enable indexes all over your site, and then deny indexing in only certain subdirectories, or deny index listings site-wide, and allow indexing in certain subdirectories. One line in the .htaccess file in your root and your whole site is altered. From here on, I’ll probably refer to the main .htaccess in the root of your website as “the master .htaccess file”, or “main” .htaccess file.

There’s a small performance penalty for all this .htaccess file checking, but not noticeable, and you’ll find most of the time it’s just on and there’s nothing you can do about it anyway, so let’s make the most of it..

Is .htaccess enabled?

It’s unusual, but possible that .htaccess is not enabled on your site. If you are hosting it yourself, it’s easy enough to fix; open your httpd.conf in a text editor, and locate this <Directory> section..

Your DocumentRoot may be different, of course..

# This should be changed to whatever you set DocumentRoot to.
#
<Directory “/var/www/htdocs”>
#

..locate the line that reads..

AllowOverride None

..and change it to..

AllowOverride All

Restart Apache. Now .htaccess will work. You can also make this change inside a virtual host, which would normally be preferable.

If your site is hosted with someone else, check your control panel (Plesk. CPanel, etc.) to see if you can enable it there, and if not, contact your hosting admins. Perhaps they don’t allow this. In which case, switch to a better web host.

What can I do with .htaccess files?

Almost any directive that you can put inside an httpd.conf file will also function perfectly inside an .htaccess file. Unsurprisingly, the most common use of .htaccess is to..

Control and Deny Access

.htaccess is most often used to restrict or deny access to individual files and folders. A typical example would be an “includes” folder. Your site’s pages can call these included scripts all they like, but you don’t want users accessing these files directly, over the web. In that case you would drop an .htaccess file in the includes folder with content something like this..

NO ENTRY!

# no one gets in here!
deny from all

which would deny ALL direct access to ANY files in that folder. You can be more specific with your conditions, for instance limiting access to a particular IP range, here’s a handy top-level rule for a local test server..

NO ENTRY outside of the LAN!

# no nasty crackers in here!
order deny,allow
deny from all
allow from 192.168.0.0/24
# this would do the same thing..
#allow from 192.168.0

Generally these sorts of requests would bounce off your firewall anyway, but on a live server (like my dev mirror sometimes is) they become useful for filtering out undesirable IP blocks, known risks, lots of things. By the way, in case you hadn’t spotted; lines beginning with “#” are ignored by Apache; handy for comments.

Sometimes, you will only want to ban one IP, perhaps some persistent robot that doesn’t play by the rules..

post user agent every fifth request only. hmmm. ban IP..

# someone else giving the ruskies a bad name..
order allow,deny
deny from 83.222.23.219
allow from all

The usual rules for IP addresses apply, so you can use partial matches, ranges, and so on. Whatever, the user gets a 403 “access denied” error page in their client software (browser, usually), which certainly gets the message across. This is probably fine for most situations, but in part two I’ll demonstrate some cooler ways to deny access.

Custom Error Documents

I guess I should briefly mention that .htaccess is where most folk configure their error documents. Usually with sommething like this..

the usual method. the “err” folder (with the custom pages) is in the root

# custom error documents
ErrorDocument 401 /err/401.php
ErrorDocument 403 /err/403.php
ErrorDocument 404 /err/404.php
ErrorDocument 500 /err/500.php

You can also specify external URLs, though this can be problematic, and is best avoided. One quick and simple method is to specify the text in the directive itself, you can even use HTML (though there is probably a limit to how much HTML you can squeeze onto one line). Remember, for Apache 1; begin with a “, but DO NOT end with one. For Apache 2, you can put a second quote at the end, as normal.

measure twice, quote once..

# quick custom error “document”..
ErrorDocument 404 “<html><head><title>NO!</title></head><body><h2><tt>There is nothing here.. go away quickly!</tt></h2></body></html>

Using a custom error document is a Very Good Idea, and will give you a second chance at your almost-lost visitors. I recommend you download mine. But then, I would.

Password Protected Directories

The next most obvious use for our .htaccess files is to allow access to only specific users, or user groups, in other words; password protected folders. a simple authorisation mechanism might look something like this..

a simple sample .htaccess file for password protection:

AuthType Basic
AuthName “restricted area”
AuthUserFile /usr/local/var/www/html/.htpasses
require valid-user

You can use this same mechanism to limit only certain kinds of requests, too..

only valid users can POST in here, anyone can GET, PUT, etc:

AuthType Basic
AuthName “restricted area”
AuthUserFile /usr/local/var/www/html/.htpasses
<Limit POST>
require valid-user
</Limit>

You can find loads of online examples of how to setup authorization using .htaccess, and so long as you have a real user (or create one, in this case, ‘jimmy’) with a real password (you will be prompted for this, twice) in a real password file (the -c switch will create it)..

htpasswd -c /usr/local/var/www/html/.htpasses jimmy

..the above will work just fine. htpasswd is a tool that comes free with Apache, specifically for making and updating password files, check it out. The windows version is the same; only the file path needs to be changed; to wherever you want to put the password file.

Note: if the Apache bin/ folder isn’t in your PATH, you will need to cd into that directory before performing the command. Also note: You can use forward and back-slashes interchangeably with Apache/php on Windows, so this would work just fine..

htpasswd -c c:/unix/usr/local/Apache2/conf/.htpasses jimmy

Relative paths are fine too; assuming you were inside the bin/ directory of our fictional Apache install, the following would do exactly the same as the above..

htpasswd -c ../conf/.htpasses jimmy

Naming the password file .htpasses is a habit from when I had to keep that file inside the web site itself, and as web servers are configured to ignore files beginning with .ht, they too, remain hidden. If you keep your password file outside the web root (a better idea), then you can call it whatever you like, but the .ht_something habit is a good one to keep, even inside the web tree, it is secure enough for our basic purpose..

Once they are logged in, you can access the remote_user environmental variable, and do stuff with it..

the remote_user variable is now available..

RewriteEngine on
RewriteCond %{remote_user} !^$ [nc]
RewriteRule ^(.*)$ /users/%{remote_user}/$1

Which is a handy directive, utilizing mod_rewrite; a subject I delve into far more deeply, in part two.

Get better protection..

The authentication examples above assume that your web server supports “Basic” http authorisation, as far as I know they all do (it’s in the Apache core). Trouble is, some browsers aren’t sending password this way any more, personally I’m looking to php to cover my authorization needs. Basic auth works okay though, even if it isn’t actually very secure – your password travels in plain text over the wire, not clever.

If you have php, and are looking for a more secure login facility, check out pajamas. It’s free. If you are looking for a password-protected download facility (and much more, besides), check out my distro machine, also free.

500 Error

If you add something that the server doesn’t understand or support, you will get a 500 error page, aka.. “the server did a boo-boo”. Even directives that work perfectly on your test server at home may fail dramatically at your real site. In fact this is a great way to find out if .htaccess files are enabled on your site; create one, put some gibberish in it, and load a page in that folder, wait for the 500 error. if there isn’t one, probably they are not enabled.

If they are, we need a way to safely do live-testing without bringing the whole site to a 500 standstill.

Fortunately, in much the same way as we used the <Limit> tag above, we can create conditional directives, things which will only come into effect if certain conditions are true. The most useful of these is the “ifModule” condition, which goes something like this..

only if PHP is loaded, will this directive have any effect (switch the 4 for a 5 if using php5)

<ifModule mod_php4.c>
php_value default_charset utf-8
</ifModule>

..which placed in your master .htaccess file, that would set the default character encoding of your entire site to utf-8 (a good idea!), at least, anything output by PHP. If the PHP4^** module isn’t running on the server, the above .htaccess directive will do exactly nothing; Apache just ignores it. As well as proofing us against knocking the server into 500 mode, this also makes our .htaccess directives that wee bit more portable. Of course, if your syntax is messed-up, no amount of if-module-ing is going to prevent a error of some kind, all the more reason to practice this stuff on a local test server.

** note: if you are using php5, you would obviously instead use <ifModule mod_php5.c>.