Hi everyone I was wondering if someone could help me out with a table I am trying to create I want to create a resource web site that has links to other sites I have seven categories and Subcategories in each link I wanted to be able to use a MYSQL table to hold the links I did not want to create seven different tables as this would be unproductive
Any Suggestions would be good
P.S. If you could explain it as well so that I can learn
Thank You in Advance
MySqL Table
Moderator: General Moderators
Depending on various users preferences, there are no ONE solution. But here are some ideas:
Pretty selfexplainatory:
id - the id of the link. generally a good idea. (Ex: 1)
cat - categories (Ex: PHP)
subcat - subcategories (Ex: Coding Resources)
url - what you put in href (Ex: http://forums.devnetwork.net/)
linkname - name (Ex: PHPDN)
Inserting something...
As the 'id' is set to AUTO_INCREMENT, two single quotes is enough. MySQL will itself add the next number in line.
After a while, you will have a huge selection of links, and by using code like
...you can get all links that has to do with that general area.
Writing an example of the entire script (especially the output to a page part) would take up a whole page with commenting, so I'd suggest you look into prewritten snippets of it on the web (http://www.evilwalrus.com, hotscripts.com perhaps) and go from there.
Hope I was of some help & good luck.
Code: Select all
CREATE TABLE `links` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cat` VARCHAR( 50 ) NOT NULL ,
`subcat` VARCHAR( 50 ) NOT NULL ,
`url` VARCHAR( 200 ) NOT NULL ,
`linkname` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `id` )
) COMMENT = 'Links';id - the id of the link. generally a good idea. (Ex: 1)
cat - categories (Ex: PHP)
subcat - subcategories (Ex: Coding Resources)
url - what you put in href (Ex: http://forums.devnetwork.net/)
linkname - name (Ex: PHPDN)
Inserting something...
Code: Select all
INSERT INTO `links` ( `id` , `cat` , `subcat` , `url` , `linkname` )
VALUES (
'', 'PHP', 'Coding Resources', 'http://forums.devnetwork.net/', 'PHPDN'
);After a while, you will have a huge selection of links, and by using code like
Code: Select all
select * from links where car = 'PHP'Writing an example of the entire script (especially the output to a page part) would take up a whole page with commenting, so I'd suggest you look into prewritten snippets of it on the web (http://www.evilwalrus.com, hotscripts.com perhaps) and go from there.
Hope I was of some help & good luck.
-
nightmatrix
- Forum Newbie
- Posts: 21
- Joined: Sun Jun 08, 2003 11:03 am
Thank You
Hey Thanks I can probably figure it out from here
Hi,
If you plan to add new categories, or do any change on them (rename them or move a subcategory from a category to another), then think about storing your categories in a separate table:
Let's insert a few:
Then, just modify JAM's table this way (hope you don't mind JAM
)
and of course the corresponding entry as well:
The link no longer contains the category itself but just its reference.
By spliting your data into two table, you have made your database easier to read and to manage. The drawback of this approach is that your queries are now a bit more difficult to write.
But it worths the extra work.
Cheers,
Cyril
If you plan to add new categories, or do any change on them (rename them or move a subcategory from a category to another), then think about storing your categories in a separate table:
Code: Select all
CREATE TABLE `categories`
(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`cat_name` VARCHAR(50) NULL,
`parent_cat_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) COMMENT=`categories`;Code: Select all
INSERT INTO `categories` VALUES
#root category id 1
('','root',0),
#php category id 2
('','php',1),
#coding resources category id 3
('','coding resources',2),
#asp category id 4
('','asp',1);Code: Select all
CREATE TABLE `links` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`url` VARCHAR( 200 ) NOT NULL ,
`linkname` VARCHAR( 50 ) NOT NULL ,
`cat_id` INT UNSIGNED NOT NULL,
PRIMARY KEY ( `id` )
) COMMENT = 'Links';Code: Select all
# 'phpdn' is inserted in the category id 3, which name is 'coding resource' # and which parent category is 'PHP'
INSERT INTO `links` VALUES (
'', 'http://forums.devnetwork.net/', 'PHPDN', 3
);By spliting your data into two table, you have made your database easier to read and to manage. The drawback of this approach is that your queries are now a bit more difficult to write.
But it worths the extra work.
Cheers,
Cyril