0

I wrote a search engine

   $select = <<<EOT
                `show_horses`.`sys_id`, `show_horses`.`group_sys_id`, `show_horses`.`horse_number`,
                `horses`.`sys_id` as `horse_sys_id`, `horses`.`name` as `horse_name`, `horses`.`f_name`,
                `show_points`.`total`, `show_points`.`exp`, `show_points`.`sum`,
                GROUP_CONCAT(DISTINCT `breeder`.`full_name` SEPARATOR ' - ') as breeder, GROUP_CONCAT(DISTINCT `breeder`.`f_full_name` SEPARATOR ' - ') as f_breeder,
                GROUP_CONCAT(DISTINCT `owner`.`full_name` SEPARATOR ' - ') as owner, GROUP_CONCAT(DISTINCT `owner`.`f_full_name` SEPARATOR ' - ') as f_owner
                    
                    
EOT;
        $query = <<<EOT
                        SELECT $select
                        FROM `show_horses`
                        LEFT JOIN `horses` ON `show_horses`.`horse_sys_id` = `horses`.`sys_id`
                        LEFT JOIN `show_points` ON `show_horses`.`sys_id` = `show_points`.`sys_id` AND `horses`.`sys_id` = `show_points`.`horse_sys_id`
                
                        LEFT JOIN `pivot_users_horses` as `pbreeder` ON `show_horses`.`horse_sys_id` = `pbreeder`.`horse_sys_id` AND `pbreeder`.`relation` = 'breeders'
                        LEFT JOIN `pivot_users_horses` as `powner` ON `show_horses`.`horse_sys_id` = `powner`.`horse_sys_id` AND `powner`.`relation` = 'owners'
                        LEFT JOIN `users` as `breeder` ON `pbreeder`.`user_sys_id` = `breeder`.`sys_id`
                        LEFT JOIN `users` as `owner` ON `powner`.`user_sys_id` = `owner`.`sys_id`
                        WHERE (`horses`.`f_name` LIKE ? OR `horses`.`name` LIKE ? OR `horses`.`f_name` LIKE ? OR `breeder`.`full_name` LIKE ? OR `breeder`.`f_full_name` LIKE ? OR `owner`.`full_name` LIKE ?
                        OR `owner`.`f_full_name` LIKE ?) AND `show_horses`.`sys_id` = ?;
EOT;

Works fine But it only returns one row

When I delete

GROUP_CONCAT(DISTINCT `breeder`.`full_name` SEPARATOR ' - ') as breeder, GROUP_CONCAT(DISTINCT `breeder`.`f_full_name` SEPARATOR ' - ') as f_breeder,
                GROUP_CONCAT(DISTINCT `owner`.`full_name` SEPARATOR ' - ') as owner, GROUP_CONCAT(DISTINCT `owner`.`f_full_name` SEPARATOR ' - ') as f_owner

The problem is solved But this is not practical for me Why when I use GROUP_CONCAT Only one row is displayed? How should I solve this problem?

reza
  • 1
  • 2
  • The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value. – Ergest Basha Oct 04 '21 at 14:08
  • In other words it needs group by in the end check: https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql – Ergest Basha Oct 04 '21 at 14:09

0 Answers0