SQL Theory & How-To

I try and wade through as many postings on PHP General as possible throughout the day. Everything from “What does
$$var mean?” to “How to I redirect someone to another page?” pop up. Two common problems pop up on the list
frequently, one is “How do I build complex queries on the fly?”, and the other is the problem of how to fully
utilize all those cool features in your DB.

So here is what I plan to tackle in this article:
1.) Creating complex, powerful, queries to take advantage of your table schemas
2.) Building those queries on the fly according to user input

My main beef with people on the list is that they don’t learn things in the proper order. They want to know how to
redirect people without necessarily understanding what a “header” is much less what “302” means. The same goes for
SQL – there is an underlying theory behind SQL, called “Relational Algebra”. If you took any math classes beyond
Math 101 then you have most likely played with its general theories.

Above is a Ven Diagram that 99.99% of us have all seen, and many grew to love. It represents the basic foundations
of Relational Algebra. If you want to know all the people who own a Toaster or own a TV you would choose area
(green circle + blue circle), or, in SQL terms, “SELECT * FROM people WHERE own=’Toaster’ OR own=’TV'”.

Enough math already – I wanna build complex queries on the fly – NOW! Ok, well first we need some decent table
schemas. I’m going to use my link tracker as an example. Here are the table schemas.

link_categories(
    categoryID tinyint(2) UNSIGNED NOT NULL AUTO_INCREMENT DEFAULT '0',
    categoryName char(50) NOT NULL,
    PRIMARY KEY (categoryID),
    UNIQUE ID (categoryID)
);

link_information(
    linkID int(9) UNSIGNED NOT NULL AUTO_INCREMENT DEFAULT '0',
    categoryID tinyint(2) UNSIGNED NOT NULL,
    linkTitle char(150) NOT NULL,
    linkURL char(255) NOT NULL,
    linkDesc text,
    hits int(9) UNSIGNED DEFAULT '0',
    PRIMARY KEY(linkID),
    UNIQUE ID (linkID),
    KEY (categoryID),
    KEY (hits)
);