Admin Cats and Sub Cats

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

stewartrose
Forum Newbie
Posts: 10
Joined: Wed Nov 12, 2008 12:35 am

Admin Cats and Sub Cats

Post by stewartrose »

Good Morning Team,

As a total nebbie to the php programming I am stuck, what I need is to be able to create master categorys and sub categorys in a admin program , but I cant get my head around it to solve the problem, has anyone got any routines I could have to help please.

All the best from Alan [ Loosing hair fast ] :)
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Admin Cats and Sub Cats

Post by aceconcepts »

So you need to display categories such as "Cars" and sub cats such as "sedan", "4x4", "estate" etc yeh?
stewartrose
Forum Newbie
Posts: 10
Joined: Wed Nov 12, 2008 12:35 am

Re: Admin Cats and Sub Cats

Post by stewartrose »

Good Morning Mark,

Yes you got it exactly.. Thank you.

All the best from Alan
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Admin Cats and Sub Cats

Post by aceconcepts »

How have you structured your database tables?

Can you layout you schema?
stewartrose
Forum Newbie
Posts: 10
Joined: Wed Nov 12, 2008 12:35 am

Re: Admin Cats and Sub Cats

Post by stewartrose »

Hi Mark,

No i have not done that because i was not sure what was needed.

All the best from Alan
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Admin Cats and Sub Cats

Post by josh »

I'd use an association table, and one of the many well documented ways of querying / storing hierarchical data in sql
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Admin Cats and Sub Cats

Post by aceconcepts »

Create three tables:
  • 1. tblCategory (intCatId, strCategory)
    2. tblSubCategory (intSubCatId, strSubCategory)
    3. tblLinkSubCat (intCatId, intSubCatId)
tblLinkSubCat will allow you to link multiple subCats with multiple cats.

Create those tables and then post back when you're done.
stewartrose
Forum Newbie
Posts: 10
Joined: Wed Nov 12, 2008 12:35 am

Re: Admin Cats and Sub Cats

Post by stewartrose »

Hi Mark,

Thank you I will start on it now..

All the best from Alan
TeNDoLLA
Forum Newbie
Posts: 8
Joined: Wed Nov 12, 2008 3:52 pm

Re: Admin Cats and Sub Cats

Post by TeNDoLLA »

Or if you can use InnoDB as db engine you can use two tables and make parent_cat_id to refer main_cat_id field in main_categories table.

table main_categories (main_cat_id, main_cat_name)
table sub_categories (sub_cat_id, sub_cat_name, parent_cat_id)
stewartrose
Forum Newbie
Posts: 10
Joined: Wed Nov 12, 2008 12:35 am

Re: Admin Cats and Sub Cats

Post by stewartrose »

Hi Mark,

All done, and if I hope it is right, but this system will not allow me to upload .sql .txt , so I have to cut and paste it in sorry mark...

Thanks from Alan

-- phpMyAdmin SQL Dump
-- version 2.11.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 12, 2008 at 10:12 PM
-- Server version: 5.0.51
-- PHP Version: 4.4.8

--
-- Database: `domains_vegan`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblCategory`
--

CREATE TABLE `tblCategory` (
`intCatId` int(10) NOT NULL auto_increment,
`strCategory` varchar(100) NOT NULL,
PRIMARY KEY (`intCatId`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

--
-- Dumping data for table `tblCategory`
--


-- --------------------------------------------------------

--
-- Table structure for table `tblLinkSubCat`
--

CREATE TABLE `tblLinkSubCat` (
`intCatId` int(10) NOT NULL auto_increment,
`intSubCatId` varchar(100) NOT NULL,
PRIMARY KEY (`intCatId`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

--
-- Dumping data for table `tblLinkSubCat`
--


-- --------------------------------------------------------

--
-- Table structure for table `tblSubCategory`
--

CREATE TABLE `tblSubCategory` (
`intSubCatId` int(10) NOT NULL auto_increment,
`strSubCategory` varchar(100) NOT NULL,
PRIMARY KEY (`intSubCatId`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

--
-- Dumping data for table `tblSubCategory`
--
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Admin Cats and Sub Cats

Post by aceconcepts »

It all looks good except for:

Code: Select all

 
CREATE TABLE `tblLinkSubCat` (
`intCatId` int(10) NOT NULL auto_increment,
`intSubCatId` varchar(100) NOT NULL,
PRIMARY KEY (`intCatId`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
 
1. intCatId in tblLinkSubCat should not be auto_increment - it should ideally be an indexed field.
2. intSubCatId should not be varchar, it should retain the same data type as it does in it's own table (tblSubCategory).

The main thing you should realise about relational databases is that they work by reference of unique "keys" or "ids" such as intSubCatId and intCatId.

In tblCategory the primary key (unique ID) is intCatId and in tblSubCategory the primary key is intSubCatId. When these same fields are used in tblLinkSubCat they become "foreign keys" but still should reatin the same data type definition otherwise the database will not be able to match the fields via a query.

The reason for using tblLinkSubCat is so that we can "relate" or "link" a sub category with one or many categories. It wouldn't be very useful if you could only "link" one category with one sub category.

If you want to know more about this look up "normalisation".

I hope this makes some sense :wink:
stewartrose
Forum Newbie
Posts: 10
Joined: Wed Nov 12, 2008 12:35 am

Re: Admin Cats and Sub Cats

Post by stewartrose »

Hi Mark,

No I dont understand yet, so I will go do some reading as you say on normalization, I dont have a problem learning in at the age of 62 :)
Thank you whats next..

All the best from Alan
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Admin Cats and Sub Cats

Post by josh »

stewartrose wrote:I dont have a problem learning in at the age of 62 :)
Wow, that's inspiring. I'm going to think about you everytime I start to take my youth in the field for granted.
This link may also be of interest:
http://www.evolt.org/article/Four_ways_ ... index.html

and I don't think that guy's name is Mark, that's a quote from the great Mark Twain in his signature, hah
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Admin Cats and Sub Cats

Post by aceconcepts »

Yeh, my name isn't Mark - I wasn't going to say anything though :D
stewartrose
Forum Newbie
Posts: 10
Joined: Wed Nov 12, 2008 12:35 am

Re: Admin Cats and Sub Cats

Post by stewartrose »

Hi,

Dont worry if you dont want to use your name it is only your business no one else's
You are talking to me so that is all I need..
Keep up the good work..

All the best from Alan
Post Reply