Page 1 of 1

MySqL Table

Posted: Fri Aug 29, 2003 10:16 am
by nightmatrix
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

Posted: Fri Aug 29, 2003 10:27 am
by JayBird
why do you think you would need sevral tables?

Posted: Fri Aug 29, 2003 10:31 am
by JAM
Depending on various users preferences, there are no ONE solution. But here are some ideas:

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';
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...

Code: Select all

INSERT INTO `links` ( `id` , `cat` , `subcat` , `url` , `linkname` ) 
VALUES (
'', 'PHP', 'Coding Resources', 'http://forums.devnetwork.net/', 'PHPDN'
);
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

Code: Select all

select * from links where car = 'PHP'
...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.

Thank You

Posted: Fri Aug 29, 2003 12:38 pm
by nightmatrix
Hey Thanks I can probably figure it out from here

Posted: Sat Aug 30, 2003 2:37 am
by cyril11
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:

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`;
Let's insert a few:

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);
Then, just modify JAM's table this way (hope you don't mind JAM :wink:)

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';
and of course the corresponding entry as well:

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 
);
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

Posted: Sat Aug 30, 2003 7:01 am
by JAM
Then, just modify JAM's table this way (hope you don't mind JAM )
Hehe, Not at all!
The board are about discussions. Just having one person telling "do this" wouldn't be any good.