From the category archives:

General

JSP: Java on Your Server.

by Jeff on December 2, 2005

JSP stands for Java Server Pages. The idea of JSP is to allow Java code to be embedded in HTML this is done uising XML tags. JSP allows you to dynamically add content to your pages, and also offers the facility to send Java to the web browser easily.

The Lack of Functions.

Most of the built-in JSP functions do nothing more than flow control: it barely offers any built-in functions at all. Instead, you have to define your own tags using tag libraries. This makes the language extremely extensible, but not much use if you just want to get started quickly and write a simple database-driven website.

The most significant reason to use JSP is that it gives you all the power of Java when it comes to adding things to your HTML pages. In big companies especially, these Java capabilities are very important. The flexibility of the language is also very useful for larger projects: in some ways, JSP is more like a ‘build your own programming language kit’ than it is a language in itself.

Separating the Application Logic.

The real point of JSP is to separate out your application’s logic (the part that does things) from its presentation (the HTML). JSP is, basically, a very minimal language intended to do little more than help turn the output of a Java application into a web page. This has the advantage of letting you write an entirely new website using your existing application as the backend but it has the disadvantage that it can be difficult to add any new functions to the site unless you know what you’re doing in Java.

Unfortunately, the downside of doing things this way is that, if you’re not strict about it, HTML code will have a tendency to start creeping into the output of the backend Java application. If that happens, it will make it difficult to rewrite your JSPs without ending up with broken web pages.

Is Java a Good Idea?

One of the advantages of JSP is that it can easily take Java applets and throw them onto the web. When it comes down to it, though, Java isn’t usually a good idea. Why? Well, it takes a long time to load, it’s slow when it does load, and it doesn’t tend to do anything that couldn’t have been done better in Flash or even in plain old HTML. On the web, Java has come to be considered harmful.

On the server, though, Java is a fine idea, at least if you’re good enough to program in it. There are simpler languages out there, but Java is cross-platform, standardised and does pretty much everything you could want it to. You will, though, need a pretty powerful server to use Java for any significant number of requests.

JSP or ASP?

JSP is obviously named to point out the fact that it is an alternative to Microsoft’s ASP. So why would you use one instead of the other. Well, while ASP has the simplicity of Visual Basic on its side, JSP has the power of Java, and, more importantly, its cross-platform capabilities. While ASP will tie you down to a Microsoft platform, JSP can be used on any operating system capable of running Java and that’s just about all of them.

JSP or ColdFusion?

What JSP and ColdFusion have in common is that they can both interact with Java but which is better? For most projects, the answer is ColdFusion: if you’re building something relatively small (meaning non-enterprise), ColdFusion’s built-in functions should be sufficient and the code shouldn’t be able to grow so large as to be unmanageable.

Should JSP Stay in the Enterprise?

People with small website usually fall into one of two camps: the vendor-devoted people who are quite happy to run their whole system on Microsoft or Macromedia products because it saves them time, and the open source devotees who use languages like PHP because it saves them money and they support the principles involved. Among these two camps, there isn’t really much room for JSP, and in most cases, there shouldn’t be. Unless you’re building a project that’s larger than the average, or you’ve already got a Java application you want to write a web interface for, you’ll probably be better off giving JSP a miss.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

It’s a World Wide Web: Going International.

by Jeff on November 30, 2005

While many websites seem to assume that everyone lives in America and speaks English, most of the world, oddly enough, doesn’t. If you want your website to be successful internationally, you need to make your content available in languages other than English.

Machine Translation.

A good first step in taking your website international is to offer links to translate it at a free machine translation service, such as Babelfish or Googles Page Translator. These services take your text and attempt to translate it automatically as best as they can. However, translating text is a difficult problem, and even quite complicated grammatical rules tend to produce text that is only just understandable. It’s usable in an emergency, but not exactly pleasant, and not something you want your visitors to be relying on especially considering that it won’t work on interactive pages, and that the number of languages available is quite limited.

Hiring Translators.

The next step up the ladder, then, is to consider hiring professional translators to translate your website’s content and navigation elements into each language. While this is fine for larger companies, in many cases it’s prohibitively expensive, at least if you do it for more than a few languages. If you are thinking of hiring a translator, the best approach is to first get your site translated into English (if its not already in English), and then get it translated into the languages spoken by your largest groups of visitors.

Volunteer Translators.

If you have a popular community website, or one with articles that lots of people find useful, then you might find that people even volunteer to translate your articles for free you can give them a little encouragement by putting a message on the bottom of your pages asking for help in translation. Depending on what kind of website you run, you might be able to offer incentives like free products or free membership.

You have to bear in mind that translations you get from volunteers are unlikely to be professional quality, but theyll at least be readable and approximately correct. Even a very bad human translator tends to do better than machine translation.

To make sure youre not putting up any embarrassingly bad translations, you can give readers an opportunity to rate and give feedback on the translation, and remove it if it seems to be doing more harm than good. You will often find that visitors suggest corrections to the translation, making it get gradually better and better.

Deciding What to Send.

One of the biggest mistakes international websites make is asking users to choose for themselves which country theyre in or which language they want out of a list. Not only is this annoying for the user, but its insulting if their country or language isnt there.

The worst thing about all this nonsense is that theres absolutely no need for it. Web browsers send the computers country and language settings to your website in the HTTP headers, if you can be bothered to take account of them a tiny amount of scripting on your part can save your visitors a lot of trouble.

Not only is this approach easier, but its also seamless the user just goes to your website, and its in the language they wanted. You should still offer a choice, but make it a small option in the corner, not the entire front page.

Physical Products Around the World.

Of course, a web design article is no place to discuss the actual logistics of international shipping, but it is important to design your website to take account of it. If youre planning to deliver physical products worldwide, you need to generalise your forms enough to take account of it.

Offer address lines that arent overly specific in what they ask for, and do little validation no-one wants to be told that their address is invalid. Also, make sure you change shipping costs dynamically to take account of the country where the user is based, as this is more than likely the country where theyll want things to be posted to.

You also need to take account of international payment, and make sure you can accept as many kinds of payment as possible, as preferences vary from country to country. Not every country is as reliant on credit cards as you might expect.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

It’s me!

by Jeff on November 30, 2005

Earlier this year I had the opportunity to go with some friends over to Brooks Ayola’s photography studio and take some photos. He has an amazing studio out in LA filled with awesome equipment and toys. Besides being a fashion/commercial photographer Brooks also likes to Blog and that is how my friend got to know him. Anyway, he ended up taking some shots of me and I was just told that I’m on his home page. The photos rotate so you might have to refresh, but I show up in the middle. Here is a small screenshot.
Me in the middle
Check out Brooks Photography site. Also, I’m under the girl on the top left on this page http://ayola.com/dark/index.html. Thanks Brooks!

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

IIS and ASP: Microsoft’s Server.

by Jeff on November 29, 2005

Despite Microsoft’s dominance of everything to do with computers, their web server software sits on a relatively low 20% market share, thanks to the popularity of Apache. However, 20% of millions of servers is still a pretty substantial number of servers, and so IIS (Internet Information Server) can’t be written off that quickly.

IIS and Security.

Among technical people, though, IIS is mainly known for its terrible security record, most famously when a security hole allowed the Code Red worm (a kind of virus) to spread between IIS servers back in 2001 . Microsoft was forced to issue press releases asking people to secure their servers, which meant that millions of webmasters had to go to Microsofts website and download a patch to fix the problem. This prompted many people to go and download Apache instead, so the same thing wouldn’t happen again.
Most of IIS’ security holes were caused by services that most people don’t use, simply because they were left on by default. Once an attacker was in, the damage they could do was greatly increased by the fact that IIS ran with all the security privileges available on the system essentially, once someone got past IIS’ lacking security, they could do anything to the system.

For the latest version, Microsoft finally turned off unnecessary services and made the server run with fewer privileges, creating a much more secure web server. However, most of the IIS servers on the Internet today are not running the latest version, as the only way to get it is to upgrade to the Windows Server 2003 operating system there are plenty of people still running IIS 5 on Windows 2000.

IIS and Stability.

Another prominent criticism of IIS is that it has a tendency to fail under heavy loads, as it can’t handle very many connections at once. If you’ve ever seen an error that says something like ‘Website Too Busy’, the chances are that IIS was responsible for it.

So Why Would Anyone Use IIS?

The primary reason anyone uses IIS is that they created their website using Microsoft’s software. This usually means that their database is Microsoft SQL, and their pages are written using ASP (Active Server Pages), the latest version being ASP.Net. ASP is easy to use, as most scripts are written in a Visual Basic-like language named VBScript, and comes with a slick environment that makes it easy to rapidly develop dynamic websites.

In the latest .Net version, servers can actually run whole programs using the Visual Basic .Net and C# programming languages. This is a powerful feature, allowing full-fledged programming languages to be used to generate HTML pages, and Microsoft counts on it to differentiate ASP from other solutions.

As recently as 2001, ASP was the leading solution for dynamic web pages (it was beaten by PHP the next year), and it still ha a lot of momentum. Open source languages can seem unreliable to managers, and they were often unwilling to make the change from technology that had the backing of a big company like Microsoft. Companies are now starting to make the change, although quite a few are c to Java instead of PHP.

IIS Alternatives.

Since so many people want to switch away from IIS, a market has opened up in helping them to do so while letting them keep their ASP code after all, it wouldn’t be any good if they had to start over in PHP, would it? The best solution is made by Sun, and you can see it at www.sun.com/software/chilisoft. Unfortunately, that software costs $500, so it’s only really worth it if you have a lot of code tied up in an ASP language.

Really, the best thing to do is to stay away from IIS to begin with yes, it’s easy to write web pages in VBScript, and, yes, IIS does come for free with Windows, but in the long run it really isn’t worth the hassle.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

Starting an Online Directory

by Jeff on November 28, 2005

Have you thought about starting an online directory? Directories can be a fun thing to start if you are passionate about a certain subject. If you regularly research a certain subject on the web and happen to find a lot of sites on that subject a web directory might be the perfect thing to start. So what does it take?

First you should have some decent web hosting to host your new directory. Decent hosting means that your web host has good uptime and offers the right amount of services so that your directory script can run prefectly. Lots of directory scripts today use php and some even use cgi. Read all the requirements before purchasing a directory script online and make sure your current or future host offers all that is required. If you have a dedicated server running a linux OS then you will have many directory scripts to choose from.

So what are the important things to look for in a directory script?

Well, when I look for or research directory scripts I look for 5 main things. One is that the scripts are in PHP and work with MySQL. Two is that they offer mod_rewrite so that the pages of the directory are search engine friendly. All that means is that search engines will be able to spider the pages. Three is that the script has paid submissions built in. If your directory gets popular you can charge for link submissions. If the directory has a featured listing or sponsored listing feature you can always manually charge for this.  Fourth is directory customization. If a directory script is easily customizable then you have the opportunity to customize it in the future. You can then add new features to it as you like by either paying a professional programmer or by doing it yourself. You will probably want to customize the layout with your logo and colors once you get it. The fifth main thing is that the directory script is supported. Email the owners of the site before you purchase any script. If the owners respond within a few hours then you know you have a well supported script.

There are quite a few different directory scripts available and what one you choose depends on your needs.  If you plan to have more then 100,000 listings in your directory then Gossamer Threads offers a very robust directory script which can handle millions of listings. Check out Gossamer Links for more info.  It does cost $450 for a single site license which might seem high if you are not starting something you are serious about.  Other directory scripts are available that cost around $99 if you are on a budget.  Search Google for “directory script” or “php directory script” to find the most popular ones.  Some of the ones I have personally worked with are Indexu, phpmydirectory and PLD or php link directory.  Php Link Directory is free for version 2.0 and $25 for 3.0 and runs very nice.  Check it out at http://phplinkdirectory.com

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

How to Set Up Your Hosting in 5 Minutes Flat.

by Jeff on November 28, 2005

Once you’ve chosen your web hosting, you’ll often find that you’re given a set of passwords and technical details, before being left to more-or-less figure it out on your own. If you haven’t started a website before, that can be a daunting experience.

Point Your Domain at Your Host.

The email you received should have contained the addresses of some nameservers. Nameservers look like this: ns1.yourhost.com. If you can’t find it, take a look at the help section of your host’s website.

Once you know your host’s nameserver, go and log in at your domain name registrar’s website. They all work differently, but somewhere you should see options to configure your domain. Replace the registrar’s default nameservers with your host’s nameservers.

Try going to your domain by typing www.yourdomain.com into your web browser. If it’s working, you should see a page telling you that your configuration was successful. If it doesn’t, then you should take a break for a day or two nameserver changes can still take a while to spread across the whole Internet.

Test Your FTP Account.

The next step is to try uploading a page to your website by FTP. Before you can do that, though, you need an FTP program and a test page.

The easiest way to make a test page is to open Notepad and write “this is a test”. Save it as index.html. When it comes to the FTP program, you have a lot of choice. There’s something for everyone: some good free ones to consider are Cute FTP (cuteftp.com), Smart FTP (smartftp.com) and Bulletproof FTP (bpftp.com).

Once you’ve done that, open the FTP program and ask it to connect to your host’s FTP server. This is usually ftp.yourhost.com, although you might also now be able to access it through your own website by using ftp.yourdomain.com. Once you’re connected, you should browse through the folders looking for any existing index.html file it’ll usually be in a folder called something like ‘public’ or ‘public_html’. Upload your own index.html over this one, and say ‘yes’ when you’re asked if you want to overwrite it.

Now, go to your website in a web browser. If everything’s worked the way it should, then you’ll see what you wrote in that file right there on your website! You can get started straightaway writing real content to replace that little bit of text it’s always exciting when you realise that your site is out there and ready on the web right now. If you don’t see the text, on the other hand, then you might want to refer to your host’s support pages.

Set Up an Email Address.

Almost all web hosts allow you to configure your account using a program called cPanel. The host your email sent you should tell you how to access it: it’ll usually be something like http://www.yourdomain.com:2082/cpanel. If you know the address but you can’t get to the page, you might need to disable any firewall software you have running on your computer.

If you’ve got the cPanel address right, you’ll be asked for your username and password, and then you’ll be presented with a screen full of icons. Which icons you have will depend on which features you got with your web hosting. Look for the icon called ‘email’, and then create any accounts you want there.

To check your email, you need to add an account in your email program. This shouldn’t be too much trouble: look for an option called ‘Accounts’ in your email program’s ‘Tools’ menu, and then tell it you want to add an email account. You’ll be asked for POP3 and SMTP servers (your host can provide these), as well as the email address and password you just configured in cPanel. Try sending an email to your new address from one of your other accounts, to see if it works.

Other Things to Do with cPanel.

It varies from website to website exactly what you might need to do with cPanel. It makes it easy, though, to do whatever you might need to do, whether it’s adding new FTP accounts or creating databases. Don’t worry: cPanel is designed to stop you from messing anything up, so it’s fine to experiment with it a little.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

How to Make Visitors Add You to Their Favorites.

by Jeff on November 27, 2005

If you want your visitors to come back again and again, you’ve got to get them to add your website to their favorites (also known as bookmarks in some browsers). That’s the menu where they can save websites that they want to use again, clicking them easily to get to them. Being added to a user’s favorites is like getting an ad for your website right there in their browser’s menu, for nothing. But how can you do it?

Make it Easy.

The option to add a website to the favorites menu is quite hidden, and people don’t often think about it. You’ll get far more people adding you to their favorites if you offer them a quick and easy ‘Add this Site to my Favorites’ link somewhere towards the top of the page. Even if they don’t actually use the link, it still draws attention to the browser’s favorites function and makes the user think about it, increasing the likelihood of them adding the site to their favorites sooner or later.

Pay Attention to the Title.

Once your site is in people’s favorites, of course, it’s not going to do you much good if they can’t find it again later. You need to make sure that the name and purpose of your site is clearly stated in its title, as that’s all they’ll have to go on when they’re looking through their favorites. Make it long enough to be specific, but not so long that it looks self-important or information gets lost off the end.

Tracking Who’s Done It.

It can be difficult to know who’s coming in to your site using a favorite they saved. Usually, you would look at the referrer to see where they came from but if they type the web address or use a favorite the referrer will be blank. There’s no way of telling which of those two things they did unless you send people who use your bookmark link to a special address for example www.example.com/bookmark. However, this method is not foolproof because not everyone who adds your site to their favorites will use your link to do it.

Promise Updates.

If you want people to add your site to their favorites then you need to give them a reason to come back. The best way to do this is to make it clear exactly how often your website is updated. You might write at the top of your website ‘updated weekly’ or you might simply write the date when you last updated the website. The second way only works if you update your website often.

Could You Be Their Homepage?

Of course, if you want to go even further than just getting people to add your website to their favorites then you should consider trying to get people to make your website their homepage. That is, the website that loads automatically when they first open their web browser and that they get back to when they click on their home button. If you can get your website as someone’s homepage even for a relatively short length of time then they are likely to see your website dozens of times in a day.

So how can you get people to set your website as their homepage? It’s a little more difficult than just getting them to add your site to their favorites as it works differently in different web browsers. In Internet Explorer you can give people a link that makes your website their homepage automatically. For other web browsers, however, you will need to give the visitor a set of instructions. This will require some research on your part but will generally be instructions on how to open the web browsers options or preferences menu and use the section that allows the homepage to be set manually.

Before people will make you their homepage, though, you need to offer them the kinds of things that they might want on a homepage. You can’t just expect your own content to be enough, you need to give them other things too, such as local weather or a box that they can type searches into to search the whole web using a popular search engine.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

How to Install and Configure a Forum.

by Jeff on November 26, 2005

Once you’ve built your website, you might like to offer your visitors an opportunity to interact with it, and to talk to you and each other without having to use email. Forums are absolutely ideal for this purpose.

Choosing a Forum.

Before you can install a forum, you need to decide which forum software you want to use. In most cases, this decision will depend on what your server can run and whether or not you want to pay. Here are some of the options:

vBulletin (www.vbulletin.com). vBulletin is the most popular of the commercial forums. It costs either $85 per year or $160 plus $30 per year of updates. Despite its high cost, vBulletin has a large community who produce add-on functions for it that you can download for free. Many of the web’s largest forums run on vBulletin, including the Something Awful forums. It uses PHP and MySQL.

Invision (www.invisionpower.com). Another commercial forum with a devoted community releasing add-ons, but not as large as vBulletin. Again, PHP and MySQL. Its pricing structure is also broadly similar to vBulletin’s, but slightly more expensive. Really, you might as well use vBulletin instead of this.

Ikonboard (www.ikonboard.com). Ikonboard is a free forum (but not an open source one), written in Perl. It is now being developed by the community after its original developer abandoned it to work on Invision. Although many forums still use Ikonboard, most are moving away from it it wouldn’t be a good idea to start a new forum with it.

phpBB (www.phpbb.com). phpBB is a free and open source PHP forum that supports more or less every database system out there. It has a large community and an easy templating system, and is currently the most popular open source forum out there it comes pre-installed on many hosts.

YaBB (www.yabbforum.com). YaBB is a free, open source forum written in Perl. What makes it unique, though, is that it doesn’t need a database to run: it can create its own flat files to store threads and comments. This is a rare and useful feature if you don’t want to deal with the hassle of database administration.

Installing Your Forum.

The installation process will, of course, vary depending on which forum you use you should read the forum’s ‘readme’ file that will come as part of the download. However, in general, the process will go like this:

1. Open the config file (called something like config.php or config.pl) and add your database details that is, your database’s name, and your username and password.

2. Upload all the forum’s files to your server, preferably in a folder called ‘forum’.

3. Go to the install.php/pl file using your web browser (ie. actually go to it at your website).

4. Follow the install script’s instructions. This should create all the tables in your database automatically and let you create an administrator password for the forum.

5. Delete the install script (this is for security reasons).

Once you’ve done that, you’ve got your forum installed. You should now be able to log in as an administrator and change the forum’s settings, including installing any templates you might want to.

Potential Problems.

There are quite a few problems you could run into while you’re installing your forum.

Can’t find install script. If you have trouble finding the install script to run it, check exactly where you uploaded the forum’s files. You need to include the forum name in the URL, and the file may also be in an install sub-folder.

Install script fails. You need to make sure that you entered your database’s details correctly into the config file, otherwise the forum won’t be able to connect to your database. You should also remember that you often need to create the database itself even though you don’t need to create the tables, as the forum won’t usually do that for you.

Forum installs but then doesn’t work. Make sure that you uploaded all the forum’s files, keeping the subfolder structure intact. Don’t upload the files from each subfolder separately: upload everything together, keeping the files in the correct subfolders.

If you run into any other problems, it may be a known issue with your software check their help and support forums for more information.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

How to Get Your Website Talked About on Blogs.

by Jeff on November 26, 2005

Blogs are a very powerful force on the web today. Have you ever wondered why searching for something seems to turn up so many blog entries as results? That’s because blogs link to each other all the time, creating a strong network of links that does very well in the search engines. Not only that, but they’re not shy about linking to other sites, as long as they like them, and one blogger is likely to take links from the next and re-publish them. In other words, getting talked about on blogs gets you potentially thousands of links from sites from highly-ranked pages that’s enough to get you quite high up in any search engine.

So How Do You Do It?

Well, to get your website talked about on blogs, all you have to do is create some content that would be interesting to bloggers. Luckily for you, bloggers as a group have a relatively consistent set of interests. They care about entertainment (films, books), gadgets (iPods, TiVos, etc.), computers and the web basically, imagine things that a slightly nerdy person with lots of free time would care about, and you’ve pretty much got it. If you need any further inspiration, take a look at the links from the front page of a site like www.slashdot.org or www.kottke.org.

Once you’ve chosen your subject, all you’ve got to do is write something about it that is either new, amusing, or controversial.

For example, if you’ve heard that Apple is releasing a new iPod the size of a fingernail, that’s new. Note that you can do perfectly well guessing at new things, as long as it sounds plausible and you’re good at predicting: you can often write an article announcing the obvious next step for a company with popular products and get linked from all over the place.

When it comes to amusing, you might try some kind of spoof along the lines of ‘popular nerdy film/book in the style of nerdy thing’. For example, you might do a version Lord of the Rings as though it were being acted out in an IRC chat, or recreate the storyline of the Star Wars Trilogy with Lego (warning: both of these have already been done).

Controversy is the most fun thing to create, but it’s not easy. You have to attack one of the bloggers’ ’sacred cows’, the things that they almost all seem to agree on. The best example of this is a guy who wrote an article called ‘Why Your Movable Type Blog Must Die’, criticising the software that most bloggers ran their blogs on at the time. It was linked from literally thousands of blogs, and received an enormous amount of traffic if you want to find it, it’s still ranked amazingly highly if you search for ‘movable type’.

Basically, I Have to Be a Wind-up Merchant?

Well, not necessarily it’s better to put forward controversial views that you genuinely hold and stick to producing amusing things that you genuinely find amusing, otherwise your insincerity will no doubt show in what you produce, and no-one will like it enough to link to it. What I’m saying, rather, is that you have to be in tune with the blogosphere’s likes, dislikes, interests and obsessions, and write about things it cares about.

So I’ve Written It…

Once you’ve written something, the next step is to get it out there. There are several ways to do this: first, try outright submitting it to a blog or two, saying that you found this thing you thought they might like. If you published your content in a blog format, it’s also well worth linking to a few related entries on other blogs, as this will create a ‘trackback’, automatically creating a link from their entry to yours.

Other than that, you might try linking to what you’ve done from a few weblog-style community, where you know bloggers participate. You would be surprised how many people will take that link and put it on their blog if they like it.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }

How Databases Work.

by Jeff on November 25, 2005

Almost all of the most useful sites on the web use databases to organise their content, and they often use them to allow users to register and leave comments too. Any time you do something that a website seems to ‘remember’ the next time, the chances are that a database is involved.

Yet, despite how common databases are, they aren’t very well understood. Every day, new webmasters become database administrators without even understanding the first thing about databases. When you use a database on the web today, you’re not just using any database: you’re using ones that rely on concepts built up over decades of database development and proven effective. Here are some of those concepts.

Relational Databases.

The most common database model in use today is that of the relational database others include hierarchical databases (where data is organised in ‘trees’, like an organisation’s management structure), and flat file databases (where data is stored in ‘records’ in a text document).

In a relational database, data is stored in tables. The columns are called fields and the rows are called records. So, for example, a table might have two fields: firstname and lastname. If you then added a record to this table, it could be ‘Bob’ and ‘Smith’. Instead of just having that data, you have labelled it with what it is, and that lets you refer to it and search through it much more easily.

Where the ‘relational’ part is really significant, though, is when it comes to the way tables in a database relate to the other tables. Each record of each table has an ID number (technically known as the ‘primary key’) for example, the Bob Smith record might be ID number 123. This then lets you refer to his record in a new table.

Let’s say you were storing records of people’s orders. You could have two columns: customer number and date. This lets you simply store 123 and the date in the table each time Bob Smith orders from you the relational nature of the database will tell you later on that customer number 123 is Bob Smith. When it comes to things like, for example, storing posts made by multiple authors, this is powerful.

SQL Databases.

SQL stands for ‘Structured Query Language’. It’s the most popular language for making queries to relational database systems. What’s a query? It’s basically a way of asking the database to find a record for you that matches criteria you specify.

Let’s go back to our example firstname and lastname table let’s say the table was called ‘names’. To get Bob Smith’s name in there to begin with, we would have used SQL that looked like this:

INSERT INTO names VALUES (’Bob’, ‘Smith’);

The ID number would be assigned automatically be the database. Then, later on, if we wanted to find out who customer 123 was, we could run this SQL:

SELECT * FROM names WHERE id = ‘123′;

This would get us customer 123’s record from the database Bob Smith’s record.

SQL might look complicated, and it can be, but that complexity is helped by the fact that there aren’t very many SQL commands you’re likely to ever need. Really, most websites can get by with just these statements:

CREATE. Used to create new database tables. You have to tell the database which fields (columns) you want, and what kind of data (text, dates, etc.) each field is going to contain.

SELECT. This command is used to search tables. You can use operators like = (equals), (greater than) to find the record you’re after. For example, if you wanted to find all your sales this week, you would work out the date a week ago and use SELECT * FROM sales WHERE date > that is, “find all records in the sales table where the date is greater than…”.

INSERT. Lets you add new records to the table.

UPDATE. Once you’ve inserted data, update lets you modify parts of it. Useful if, for example, Bob Smith tells you he’d prefer to be known as Robert Smith. Update lets you change the data without having to delete and re-insert it, which means that records get to keep their existing ID nun

DELETE. Removes existing rows from the table, using the same basic syntax as SELECT.

If you enjoyed this post, make sure you subscribe to my RSS feed!

{ 0 comments }