 |
 |
 |
 |
|
|
|
SQL Theory & How-To
Joe Stump
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
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)
);
| Comments: | ||
| converting an db2 schema into a sql schema | scott chittock | 12/05/05 14:09 |
| ven diagram | shea | 01/10/05 20:47 |
| RE: Select query | Dennis DeOcampo | 07/28/03 12:03 |
| No Join | RAG | 03/25/03 10:37 |
| RE: Primary key | Tbull | 01/02/03 16:23 |
| RE: Basic Sql | Jason | 12/14/02 00:50 |
| SQ7 Problems and annswers | Albert Salima | 12/05/02 05:26 |
| RE: SQL getting the last record | Kamalakar Desai | 12/03/02 22:27 |
| MySQL | subhash | 10/19/02 05:51 |
| How do I convert a IBM DB2 into a MYSQL | Haresh Moradia | 10/10/02 21:47 |
| PHP Session Variables? | CJ Sperber | 10/04/02 16:07 |
| RE: SQL getting the last record | qwerty uiop | 08/26/02 18:47 |
| RE: PRIMARY filegroup full | Brian Kalunga | 08/23/02 09:21 |
| SQL getting the last record | Drew | 08/08/02 11:20 |
| Copy table in MSSQL7 or 2000 | Shelley | 08/01/02 02:20 |
| RE: Primary key solution (or not?) | Kodiak | 07/31/02 05:50 |
| RE: how can i retrieve access database to SQL DB | RUPENDRA KIRAN KOTA | 07/26/02 03:51 |
| Primary key | ARE MOSHOOD A | 07/19/02 12:26 |
| RE: mySQL to MS-SQL in PHP | Decibel | 07/02/02 10:36 |
| Poor MySQL Coding | MattR | 07/02/02 08:49 |
| RE: Primary key | fabien | 07/02/02 05:56 |
| Select query | Emer Cronin | 05/29/02 07:18 |
| RE: Primary key | Thor Sune Jensen | 05/03/02 03:58 |
| how can i retrieve access database to SQL DB | Joe | 05/02/02 23:57 |
| ? Inner Joins ? | Steve | 04/25/02 08:51 |
| RE: How can I retrieve data from MS Access? | Greg Davey | 04/21/02 15:37 |
| RE: SQL Sources | zein | 04/19/02 07:47 |
| How can I retrieve data from MS Access? | Daniels P | 03/20/02 14:40 |
| Primary key | Eduardo Pinheiro | 03/20/02 13:48 |
| Connecting string for MS SQL server 2000 | Mahesh | 03/19/02 04:12 |
| RE: how to database files | Mike Siekkinen | 03/06/02 21:29 |
| RE: Theory? | pedro | 02/01/02 23:48 |
| RE: Basic Sql | Yohanes Eko | 01/22/02 01:47 |
| RE: Theory? | Joe Stump | 12/14/01 13:36 |
| RE: Basic Sql | Bruce Pierson | 12/06/01 00:07 |
| mySQL to MS-SQL in PHP | alisa | 12/04/01 02:56 |
| Basic Sql | tracy Bradshaw | 11/30/01 12:15 |
| "Venn diagram"; "Ven diagram" | Michael Levy | 11/29/01 10:00 |
| RE: how to access database files created in f | Dr.K.Giridhar | 09/09/01 23:51 |
| typo error in article | Mike C | 08/21/01 19:48 |
| ODBC prepare/ select | Jabro | 08/05/01 08:23 |
| Combining text and data | Antony Carr | 08/01/01 06:05 |
| how to database files | alfin | 07/19/01 07:52 |
| How to fetch a Group By Query | Fred | 07/09/01 08:10 |
| RE: Theory? | IndyMan | 05/09/01 08:19 |
| access MSSQL ! | Hoan | 03/18/01 02:28 |
| UNIQUE | Dave J. | 02/20/01 08:15 |
| RE: learning in correct order | armin | 02/13/01 04:35 |
| SQL Sources | Logan | 01/15/01 15:50 |
| RE: (1)$where[] and (2)"f[category]" | Ryan Holmes | 01/10/01 18:00 |
| RE: (1)$where[] and (2)"f[category]" | Terry Dahms | 01/10/01 16:19 |
| Error in dynamic WHERE | Ryan Holmes | 01/09/01 23:05 |
| Easy there Mr. Math | Brian Busche | 01/09/01 16:39 |
| RE: Theory? | Kirby L. Wallace | 01/08/01 15:38 |
| RE: learning in correct order | joe stump | 01/08/01 12:48 |
| RE: learning in correct order | Roel | 01/08/01 11:45 |
| learning in correct order | Claire Morris | 01/07/01 16:54 |
| RE: Theory | Kernel Panic | 01/07/01 08:03 |
| Re: Joins | Seth | 01/07/01 03:46 |
| RE: Theory | Steve Yelvington | 01/06/01 14:28 |
| RE: External Reference | Clarence Liau | 01/06/01 01:58 |
| there's NO 'I.linkCategory' | Rodrigo | 01/05/01 14:56 |
| RE: Theory | Paul K Egell-Johnsen | 01/05/01 07:29 |
| RE: Theory | Stephen VanDyke | 01/05/01 07:02 |
| External Reference | Anderson Fortaleza | 01/05/01 05:16 |
| RE: Error in query | Frank Seesink | 01/04/01 18:54 |
| Theory | Derek C | 01/04/01 15:40 |
| Error in query | Andreas Bernhardsen | 01/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. | ||
