PHPBuilder - SQL Theory & How-To



RSS Twitter
Articles Databases

SQL Theory & How-To

by: Joe Stump
|
January 3, 2001

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.
Three Circle Theory
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)
);

1
|
2
|
3
|
4
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Joe Stump

Comment:



Comment:

(Maximum characters: 1200). You have characters left.