 |
 |
 |
 |
|
|
|
SQL Theory & How-To
The first thing to note is that we have two tables here with a many to one relationship. "Many to one what?" What
this means is that there can be "many" links in "one" category, thus creating a "many to one" relationship. Other
types are "one to one" and "many to many". To keep things simple we won't be going into those. Another thing to note
is that they share a common bond "categoryID" - this "relates" each link to its respective category. Notice that
they have the exact same attributes - "tinyint(2) UNSIGNED NOT NULL" - which will optimize the JOIN we do on that
field later in this article. "Optimize ... who needs to optimize - it's only gonna have 15 links in it!" - well it's
a good practice to get in, I know MySQL needs to have EXACT declarations for an optimized JOIN - other DB's may not.
A good article on normalizing your tables can be found
here.
I'll leave how you enter the data into the tables up to you. I generally make a simple form to enter in cateogories
into link_categories and then use that table in my form for link_information - choosing a category each time I enter
a link and the categoryID being entered into the link_information table. So after we have some data entered into
our tables we have something like this:
mysql> select * from link_categories; +------------+-----------------+ | categoryID | categoryName | +------------+-----------------+ | 1 | miester.org | | 2 | Linux | | 3 | miester's picks | | 4 | PHP | | 5 | My Desktop | | 6 | Mini Howto's | +------------+-----------------+ 6 rows in set (0.00 sec) mysql> select * from link_information; +--------+------------+-----------------+--------------------------+----------+----------------------+ | linkID | categoryID | linkTitle | linkURL | linkHits | linkInfo | +--------+------------+-----------------+--------------------------+----------+----------------------+ | 1 | 2 | Linux.com | http://www.linux.com | 69 | Info about link here | | 2 | 2 | Slackware Linux | http://www.slackware.com | 70 | Info about link here | | 3 | 1 | Home | http://www.miester.org | 377 | Info about link here | +--------+------------+-----------------+--------------------------+----------+----------------------+
"But I only want to show the title, link, and category name on my homepage!" - OK that's where the JOIN comes into
play here.
mysql> SELECT I.linkTitle as title, I.linkURL as href, C.categoryName as category
-> FROM link_information as I, link_categories AS C
-> WHERE I.linkCategory=C.categoryID;
+-----------------+--------------------------+-------------+
| title | href | category |
+-----------------+--------------------------+-------------+
| Linux.com | http://www.linux.com | Linux |
| Slackware Linux | http://www.slackware.com | Linux |
| Home | http://www.miester.org | miester.org |
+-----------------+--------------------------+-------------+
3 rows in set (0.00 sec)
| 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. | ||
