Код:
| 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 |
| ) |
