Sr. Pega Architect
The Computer Merchant, Ltd
US-CA-Simi Valley

Justtechjobs.com Post A Job | Post A Resume

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.
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)
);
[ Next Page ]


Comments:
converting an db2 schema into a sql schemascott chittock12/05/05 14:09
ven diagramshea01/10/05 20:47
RE: Select queryDennis DeOcampo07/28/03 12:03
No JoinRAG03/25/03 10:37
RE: Primary keyTbull01/02/03 16:23
RE: Basic SqlJason12/14/02 00:50
SQ7 Problems and annswersAlbert Salima12/05/02 05:26
RE: SQL getting the last recordKamalakar Desai12/03/02 22:27
MySQLsubhash10/19/02 05:51
How do I convert a IBM DB2 into a MYSQLHaresh Moradia10/10/02 21:47
PHP Session Variables?CJ Sperber10/04/02 16:07
RE: SQL getting the last recordqwerty uiop08/26/02 18:47
RE: PRIMARY filegroup fullBrian Kalunga08/23/02 09:21
SQL getting the last recordDrew08/08/02 11:20
Copy table in MSSQL7 or 2000Shelley08/01/02 02:20
RE: Primary key solution (or not?)Kodiak07/31/02 05:50
RE: how can i retrieve access database to SQL DBRUPENDRA KIRAN KOTA07/26/02 03:51
Primary keyARE MOSHOOD A07/19/02 12:26
RE: mySQL to MS-SQL in PHPDecibel07/02/02 10:36
Poor MySQL CodingMattR07/02/02 08:49
RE: Primary keyfabien07/02/02 05:56
Select queryEmer Cronin05/29/02 07:18
RE: Primary keyThor Sune Jensen05/03/02 03:58
how can i retrieve access database to SQL DBJoe05/02/02 23:57
? Inner Joins ?Steve04/25/02 08:51
RE: How can I retrieve data from MS Access?Greg Davey04/21/02 15:37
RE: SQL Sourceszein04/19/02 07:47
How can I retrieve data from MS Access?Daniels P03/20/02 14:40
Primary keyEduardo Pinheiro03/20/02 13:48
Connecting string for MS SQL server 2000Mahesh 03/19/02 04:12
RE: how to database filesMike Siekkinen03/06/02 21:29
RE: Theory?pedro02/01/02 23:48
RE: Basic SqlYohanes Eko01/22/02 01:47
RE: Theory?Joe Stump12/14/01 13:36
RE: Basic SqlBruce Pierson12/06/01 00:07
mySQL to MS-SQL in PHPalisa12/04/01 02:56
Basic Sqltracy Bradshaw11/30/01 12:15
"Venn diagram"; "Ven diagram"Michael Levy11/29/01 10:00
RE: how to access database files created in fDr.K.Giridhar09/09/01 23:51
typo error in articleMike C08/21/01 19:48
ODBC prepare/ selectJabro08/05/01 08:23
Combining text and dataAntony Carr08/01/01 06:05
how to database filesalfin07/19/01 07:52
How to fetch a Group By QueryFred07/09/01 08:10
RE: Theory?IndyMan05/09/01 08:19
access MSSQL !Hoan03/18/01 02:28
UNIQUEDave J.02/20/01 08:15
RE: learning in correct orderarmin02/13/01 04:35
SQL SourcesLogan01/15/01 15:50
RE: (1)$where[] and (2)"f[category]" Ryan Holmes01/10/01 18:00
RE: (1)$where[] and (2)"f[category]" Terry Dahms01/10/01 16:19
Error in dynamic WHERERyan Holmes01/09/01 23:05
Easy there Mr. MathBrian Busche01/09/01 16:39
RE: Theory?Kirby L. Wallace01/08/01 15:38
RE: learning in correct orderjoe stump01/08/01 12:48
RE: learning in correct orderRoel01/08/01 11:45
learning in correct orderClaire Morris01/07/01 16:54
RE: TheoryKernel Panic 01/07/01 08:03
Re: JoinsSeth01/07/01 03:46
RE: TheorySteve Yelvington01/06/01 14:28
RE: External ReferenceClarence Liau01/06/01 01:58
there's NO 'I.linkCategory'Rodrigo01/05/01 14:56
RE: TheoryPaul K Egell-Johnsen01/05/01 07:29
RE: TheoryStephen VanDyke01/05/01 07:02
External ReferenceAnderson Fortaleza01/05/01 05:16
RE: Error in queryFrank Seesink01/04/01 18:54
TheoryDerek C01/04/01 15:40
Error in queryAndreas Bernhardsen01/04/01 07:04
 

If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly.

Add A Comment:

Name:

Email:

Subject:

Message:

To reduce spam posts, messages are now manually approved

You are not [logged in]. That means your account will not get credit for this post.