MySqL Table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nightmatrix
Forum Newbie
Posts: 21
Joined: Sun Jun 08, 2003 11:03 am

MySqL Table

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

why do you think you would need sevral tables?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
nightmatrix
Forum Newbie
Posts: 21
Joined: Sun Jun 08, 2003 11:03 am

Thank You

Post by nightmatrix »

Hey Thanks I can probably figure it out from here
cyril11
Forum Newbie
Posts: 4
Joined: Tue Jul 01, 2003 4:59 pm

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
Post Reply