You have the following database table
--
-- The table structure `board`
--
DROP TABLE IF EXISTS `board`;
CREATE TABLE IF NOT EXISTS `board` (
`id` int(11) auto_increment,
`user_id` int(11) NOT NULL,
`cat_id` varchar(250) NOT NULL,
`name` varchar(250) NOT NULL,
`translate` varchar(250) NOT NULL,
`text` varchar(15000) NOT NULL,
`email` varchar(64) NOT NULL,
the `phone` varchar(20) NOT NULL,
`person` varchar(250) NOT NULL,
`region_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
`address` varchar(500) NOT NULL,
`price` varchar(11) NOT NULL,
`time` int(11) NOT NULL,
`timeupdate` int(11) NOT NULL,
`timesrok` int(11) NOT NULL,
`status` enum ('1','2') NOT NULL default '2',
`activation` enum ('1','2') NOT NULL default '1',
`torg` enum ('0','1') NOT NULL default '0',
`free` enum ('0','1') NOT NULL default '0',
`vip` enum ('0','1') NOT NULL default '0',
`viptime` int(11) NOT NULL,
`select` enum('0','1') NOT NULL DEFAULT '0',
`selecttime` int(11) NOT NULL,
`confirm` enum('0','1') NOT NULL DEFAULT '0',
`sendemail` enum('0','1') NOT NULL DEFAULT '0',
`type` enum('1','2') NOT NULL default '1',
`del` enum('0','1') NOT NULL DEFAULT '0',
`file` enum('0','1') NOT NULL DEFAULT '0',
`views` int(11) NOT NULL default '0',
`url` varchar(2000) NOT NULL,
`lat` varchar(11) NOT NULL,
`lng` varchar(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
FULLTEXT `cat_id` (`cat_id`),
KEY `region_id` (`region_id`),
KEY `city_id` (`city_id`),
FULLTEXT `s1` (`name`, `text`, `price`),
KEY `status` (`status`),
KEY `activation` (`activation`),
KEY `del` (`del`),
KEY `file` (`file`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
In cat_id separated by commas are written to category ID (probably not the best approach). Need to count number of ads in each category
So
SELECT `category`.*, (SELECT COUNT(1) FROM `board` WHERE `board`.`cat_id` LIKE CONCAT('%', `category`.`id`, '%') AND `status`='1' AND `activation`='1' AND `del`='0' AND THERE COULD STILL BE DOP. FILTERING) AS `count` FROM `category` WHERE `refid`='1' ORDER BY `realid` ASC
says is not quite true, but it counts ads from category id 5 and id 15.
Also a separate table which contains user id, category id and the ad id.
--
-- The table structure `board_category`
--
DROP TABLE IF EXISTS `board_category`;
CREATE TABLE IF NOT EXISTS `board_category` (
`user_id` int(11) NOT NULL,
`cat_id` int(11) NOT NULL,
`board_id` int(11) NOT NULL,
KEY `user_id` (`user_id`),
KEY `cat_id` (`cat_id`),
KEY `board_id` (`board_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;