I have tow tables
Each POSTING have Any SUB OF SUB OF SUB OF SUB OF CHILD CATEGORY MEANS 5 LAVEL OF CHILD CATEGORY WILL ASSIGN TO ANY POST BY FOLLOWING JOIN
posting as p , categories as c WHERE p.cat_id = c.id
MY TABLES
CREATE TABLE IF NOT EXISTS categories
(
id
int(11) unsigned NOT NULL AUTO_INCREMENT,
name
varchar(255) DEFAULT NULL,
descc
text,
cat_type
int(11) DEFAULT ‘0’,
cat_parent
int(11) DEFAULT ‘0’,
seo_keywords
varchar(255) DEFAULT NULL,
seo_desc
varchar(255) DEFAULT NULL,
title
varchar(255) DEFAULT NULL,
marque
text,
image
varchar(255) DEFAULT NULL,
seqno
int(11) NOT NULL,
mode
int(11) DEFAULT ‘0’,
PRIMARY KEY (id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=106 ;
–
– Table structure for table posting
CREATE TABLE IF NOT EXISTS posting
(
id
int(11) unsigned NOT NULL AUTO_INCREMENT,
user_id
int(2) unsigned NOT NULL,
cat_type
int(11) NOT NULL,
city_id
int(2) unsigned NOT NULL,
country_id
int(11) unsigned NOT NULL,
location
varchar(255) NOT NULL,
type
int(2) NOT NULL,
cat_id
int(11) NOT NULL,
title
varchar(255) NOT NULL,
amount
int(11) NOT NULL,
phone_number
int(11) NOT NULL,
desc1
text NOT NULL,
listner_type
int(2) NOT NULL,
featured
int(2) unsigned NOT NULL,
publish
int(2) NOT NULL,
map
varchar(255) NOT NULL,
publish_date
date NOT NULL,
mode
int(2) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;
I WANT OUT PUT LIKE
SHOP (10) "Title (Total of 5 lavel of child )
i did following MySQL query
SELECT c.name , c.id, (SELECT COUNT(p.id) as total FROM posting as p WHERE p.cat_id = c.id AND p.mode = 1) as total1 FROM categories
as c WHERE c.cat_type = ‘1’ AND c.mode = 1 AND c.cat_parent = 0 ORDER BY name
But it collect 2 laved of child category while simple We want N lavel of Child Category TOtal…
HELP … PLEASE!