CREATE TABLE IF NOT EXISTS `articles` ( |
`id` int(11) NOT NULL, |
`tags` varchar(255) NOT NULL |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
INSERT INTO `articles` (`id`, `tags`) VALUES |
(1, 'xxx'), |
(2, 'yyy'); |
CREATE TABLE IF NOT EXISTS `categories` ( |
`id` int(11) NOT NULL, |
`name` varchar(255) NOT NULL |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
INSERT INTO `categories` (`id`, `name`) VALUES |
(1, 'xxx'), |
(2, 'zzz'); |
span style="color: #0000BB"><?php$db = new PDO('mysql:host=localhost;port=3306;dbname=test', 'root', '');$sql = 'SELECT `t1`.*, COUNT(`t2`.`tags`) AS `count`FROM `categories` AS `t1`INNER JOIN `articles` AS `t2` ON `t2`.`tags` LIKE CONCAT("%", `t1`.`name`, "%")GROUP BY `t1`.`name`';foreach($db->query($sql) as $row){ print_r($row);} |
Array |
( |
[id] => 1 |
[0] => 1 |
[name] => xxx |
[1] => xxx |
[count] => 1 |
[2] => 1 |
) |
span style="color: #0000BB"><?php$q = mysql_query('SELECT `t1`.*, COUNT(`t2`.`tags`) AS `count` FROM `categories` AS `t1` INNER JOIN `articles` AS `t2` ON `t2`.`tags` LIKE CONCAT("%", `t1`.`name`, "%") GROUP BY `t1`.`name`');while($row = mysql_fetch_assoc($q)){ print_r($row);} |