In this three-part tutorial we are going to be creating an open blogging system. We are also going to provide scripts that will make it possible to switch to a closed blogging system. This article, which is the first part, will cover the creation of the login scripts for a closed system.

• An open blogging system is a system that allows anyone to contribute to a blog without having to register. In many cases, it also allows any user to start a new topic that will then have its own replies,  as opposed to just commenting on a topic that the blog owner started. This kind of blog is suitable for people who have just created a website and do not have a lot of web traffic.
  
• A closed blogging system is one that requires registration and allows most users to contribute to a topic instead of creating their own. This kind of blog is suitable for people that have a lot of traffic on their websites.

Let's jump straight in, grab ourselves a copy of these great packages, and get hacking! This isn't simple stuff. There are lots of options available to you for obtaining, compiling, and installing the software. Let's deal with MySQL first, as we'll need it before we get PHP going.

MySQL central is http://www.mysql.com/. As befits a program of its stature, there are a zillion mirrors located all over the globe, so do the Internet a favor and pick the one closest to you.

You've got plenty of choices at this point. If you're a do-it-yourselfer, then grab the source code. If you're not that brave, there are some precompiled binaries for other platforms already available for download.

In this section of the MySQL tutorial we'll look at the format of a SELECT statement we met in the last session in detail. We will learn how to use the select statement using the WHERE clause.

SELECT column_names from table_name [WHERE ...conditions];  

Now, we know that the conditions are optional (we've seen several examples in the last session... and you would have encountered them in the assignments too).

All programs accessing a MySQL database should include mysql.h, and on compilation they should be told to link the library mysqlclient, they should be told where that is, and where the include file is.
Example (Using gcc):

gcc -o mysqldemo mysqldemo.c -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient

(These are for my system and will likely differ on yours. A Solaris system, for example, requires -lm.)
Alternatively, you can (at least in UNIX) do:

mysql -o mysqldemo mysqldemo.c `mysql_config --cflags --libs`

This is probably better, if your MySQL configuration is correct. Try it first.

Optimzing your queries can help them run more efficiently, which can save a significant amount of time. This article covers index optimization and index usage. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

This chapter discusses general principles that are useful for optimizing queries to run more efficiently. It covers the following optimization strategies:

• The primary optimization technique for reducing lookup times is to create good indexes. This is true not just for retrievals (SELECT statements); indexing reduces row lookup time for UPDATE and DELETE statements as well. You should know general principles for creating useful indexes and for avoiding unnecessary ones.

• The EXPLAIN statement provides information about how the MySQL optimizer processes queries. This is of value when you're trying to determine how to make a query run better (for example, if you suspect indexes are not being used as you think they should be).