SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Category Tree As A Database - New MySQL/PHP User

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

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2008
    Location
    UK
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bobcanada1972 View Post
    AddressLine1,
    AddressLine2,
    City,
    County,
    Postcode,
    this is good

    you need no other tables

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bobcanada1972 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  9. #9
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See http://www.free-webhosts.com/free-mysql-database.php for a list of free hosts that offer mysql

  11. #11
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...?

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  13. #13
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •