Category Tree As A Database - New MySQL/PHP User

[bobcanada1972]

Hi,

I was wondering if someone could help suggest a good format for a category tree that I want to build.

I want to build something like this:-

Vehicles
|-----Cars
|----Sports Cars
|------- Toyota
|--- some car
|--- another car
|------- Ford
|--- another car
|--- another car
|-----Family
|------- Toyota
|--- another car
|--- another car
|--- another car
|--- another car
|---- Boats

etc...

The thing is... each item could, theoretically, be in 3-4 or more subcategories...

How do people typically deal with something like this? How would you go about dynamically displaying the tree? Is there a way to do it so that the tree can be "manipulated" in multiple ways? (eg. in the above, Toyota could've been chosen as a search parameter before Family...)

I've never done any real MySQL/PHP coding before... I just have 20 years of C++ experience...

Thanks for any advice,
Bob

PS. What I currently have is something like this in a .SQL file which I'm going to import through PHPMyAdmin:-

DROP TABLE IF EXISTS `ListingCategory`;
CREATE TABLE IF NOT EXISTS `ListingCategory` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`category_id` int(11) NOT NULL default '0',
`friendly_url` varchar(255) NOT NULL default '',
`keywords` text NOT NULL,
`active_listing` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `friendly_url` (`friendly_url`),
KEY `category_id` (`category_id`),
KEY `title` (`title`),
KEY `active_listing` (`active_listing`),
FULLTEXT KEY `keywords` (`keywords`,`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Empty Table `ListingCategory`
--

INSERT INTO `ListingCategory` (`id`, `title`, `category_id`, `friendly_url`, `keywords`, `active_listing`) VALUES
(7, 'Test Category 1', 0, 'test_category_1', '', 0),
(8, 'Test Subcategory 1', 7, 'test_subcategory_1', '', 0),
(9, 'Test Category 2', 0, 'test_category_2', '', 0),
(10, 'Test Subcategory 2', 9, 'test_subcategory_2', '', 0);

[treacle0996]

Am far from being an expert, but the way i have done this is to have a table of nodes. Each node is either a menu (in which case it has a description) or an item (which means it has an item id that links to the item table). I have a separate table that links child node to parent node.

This means a menu can contain any number of items and/or menus, and any item can be linked to any number of parent menus.

When building up the tree i start with the first menu node, and put any items with that as a parent into it. I then check if any of these children are also menus, and do the same to them.

As i say, i'm no expert, so I doubt this is the most efficient way, but it certainly is flexible.

[r937]

bob, the category structure you have there is called the adjacency model (its main distinguishing feature is that it has a "parent_id" which refers back to the same table)

see Categories and Subcategories for sample queries for this structure

if an item can belong to more than one (sub)category, you will need a 2rd table between the categories table and the items table --

CREATE TABLE item_categories
( item_id INTEGER NOT NULL
, category_id INTEGER NOT NULL
, PRIMARY KEY ( item_id, category_id )
, FOREIGN KEY ( item_id ) REFERENCES items ( id )
, FOREIGN KEY ( category_id ) REFERENCES categories ( id )
);

there will be one row in this table for each category that an item belongs to

[bobcanada1972]

Thanks for the replies - those both help a lot!

Can I just ask one more small question?

I want to add UK addresses to the database, too... most of these will be within 3 counties (states) of England... how do people typically add addresses to a database? Is it like:-

AddressLine1,
AddressLine2,
City,
County,
Postcode,

etc...? If I'm only using certain cities/counties, should I therefore create different tables where these are added? Or should I just put the strings into the address table?

[r937]

AddressLine1,
AddressLine2,
City,
County,
Postcode,

this is good

you need no other tables

[bobcanada1972]

So... forgive me for being stupid... I'm a C++ coder really... does MySQL compact the database?

Eg. if I have 10,000 database entries with "Newcastle-upon-Tyne" as the city, will it store the string 10,000 times or will it use some internal method for reducing this?

I might move the cities/counties into a separate table anyway because it restricts things better for the future when I enable users to edit the data... I will only (initially) be concentrating on certain parts of the UK...

Plus... it avoids the problem where we can have any one of:-

Newcastle
Newcastle-upon-Tyne
Newcastle upon Tyne
others...

All of which are actually the same place.

[r937]

Eg. if I have 10,000 database entries with "Newcastle-upon-Tyne" as the city, will it store the string 10,000 times or will it use some internal method for reducing this?

it will store the strings eggzackly as entered

your idea of cities and/or counties tables is good, given that you want to control spelling, plus the fact that all the addresses will be from a certain geographic area

[bobcanada1972]

Great :-)

I'll set that up.

I can't access my phpMyAdmin from work for some reason - I think the port to my virtual server is blocked by our Firewall - is there a way that I can test my SQL setup other than installing the software on my work PC?

Thanks for all the help!

[bobcanada1972]

Can I just ask one really small, further question...?

Basically... how do people go about "creating" databases from scratch using PHP?

For testing from home I'm using PHPMyAdmin ... but ... I won't be able to use this from work as my company blocks the port number :-( ... but I'm sure there must be a way to just put some PHP code in which will read the .SQL file and use it to create the database for me..?

Currently my .SQL file has just:-

DROP TABLE IF EXISTS `Location_County`;
CREATE TABLE IF NOT EXISTS `Location_County` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`friendly_url` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `friendly_url` (`friendly_url`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `Location_County` (`id`, `name`, `friendly_url`) VALUES
(1, 'Tyne and Wear', 'tyne_and_wear'),
(2, 'Northumberland', 'northumberland'),
(3, 'County Durham', 'county_durham');

...

Thanks for any advice...

[guelphdad]

See http://www.free-webhosts.com/free-mysql-database.php for a list of free hosts that offer mysql

[bobcanada1972]

Ah... I was thinking more along the lines of a good way of:-

1. completely emtying a database that I specify;
2. refilling it with the info from my last message (and more).

Using PHP...

I'm wondering if I can do it using something like mysql_query( ... ) and 'include("data.sql")' or similar...?

[longneck]

not anything quite that easy. mysql_query() allows only one statement to be executed at a time. if you pass multiple statements, it will fail or only execute the first one.

[bobcanada1972]

I've posted a further question to this same subject in the PHP forum with title "NewbieQ: Arrays" ... I have the database setup in PHP now - makes my life a whole lot easier! (I don't need phpMyAdmin...)