Page 1 of 2

Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 12:49 am
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 ] :)

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 3:22 am
by aceconcepts
So you need to display categories such as "Cars" and sub cats such as "sedan", "4x4", "estate" etc yeh?

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 4:57 am
by stewartrose
Good Morning Mark,

Yes you got it exactly.. Thank you.

All the best from Alan

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 5:16 am
by aceconcepts
How have you structured your database tables?

Can you layout you schema?

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 12:49 pm
by stewartrose
Hi Mark,

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

All the best from Alan

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 3:15 pm
by josh
I'd use an association table, and one of the many well documented ways of querying / storing hierarchical data in sql

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 3:42 pm
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.

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 3:56 pm
by stewartrose
Hi Mark,

Thank you I will start on it now..

All the best from Alan

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 3:58 pm
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)

Re: Admin Cats and Sub Cats

Posted: Wed Nov 12, 2008 4:18 pm
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`
--

Re: Admin Cats and Sub Cats

Posted: Thu Nov 13, 2008 8:25 am
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:

Re: Admin Cats and Sub Cats

Posted: Thu Nov 13, 2008 8:37 am
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

Re: Admin Cats and Sub Cats

Posted: Thu Nov 13, 2008 12:29 pm
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

Re: Admin Cats and Sub Cats

Posted: Thu Nov 13, 2008 6:24 pm
by aceconcepts
Yeh, my name isn't Mark - I wasn't going to say anything though :D

Re: Admin Cats and Sub Cats

Posted: Thu Nov 13, 2008 7:20 pm
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