title: How to achieve this complex search using jQuery datatable plugin?
body: <p>I am trying to make a searchable interface and my backend includes three MySQL database tables:</p>
  
  <p><strong>tbl_country</strong></p>
  
  <pre><code>mysql&gt; select * from tbl_country;
  +--------------+---------------+
  | country_code | country       |
  +--------------+---------------+
  | AFG          | AFGHANISTAN   |
  | AUS          | AUSTRALIA     |
  | CAN          | CANADA        |
  | GBR          | GREAT BRITAIN |
  | IND          | INDIA         |
  | USA          | USA           |
  +--------------+---------------+
  6 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>tbl_state</strong></p>
  
  <pre><code>mysql&gt; select * from tbl_state;
  +----------+------------------+--------------+
  | state_id | state            | country_code |
  +----------+------------------+--------------+
  |        1 | Maharashtra      | IND          |
  |        2 | Delhi            | IND          |
  |        3 | West Bengali     | IND          |
  ............
  |       51 | Queensland       | AUS          |
  +----------+------------------+--------------+
  33 rows in set (0.00 sec)
  </code></pre>
  
  <p><strong>tbl_city</strong></p>
  
  <pre><code>mysql&gt; select * from tbl_city;
  +---------+----------+--------------------+
  | city_id | state_id | city               |
  +---------+----------+--------------------+
  |       1 |        1 | Mumbai (Bombay)    |
  |       2 |        1 | Nagpur             |
  .......
  |      84 |       37 | Edinburgh          |
  |     122 |       44 | Cardiff            |
  |     127 |       46 | Melbourne          |
  |     130 |       48 | Perth              |
  |     131 |       49 | Adelaide           |
  |     132 |       50 | Canberra           |
  |     133 |       51 | Brisbane           |
  |     134 |       51 | Gold Coast         |
  +---------+----------+--------------------+
  54 rows in set (0.00 sec)
  </code></pre>
  
  <p>I have my MySQL Database schema and values as shown here in <a href="http://sqlfiddle.com/#!9/5012c/1" rel="nofollow noreferrer">SQL Fiddle</a></p>
  
  <p>My search needs to get a single interface from where I can search all Country based on any given city or state details.</p>
  
  <p>I am trying to use this query which correctly gives the output that I want to fetch:</p>
  
  <pre><code>SELECT 
  -- tbl_country
  c.country_code, c.country,
  
  -- tbl_state
  CAST(GROUP_CONCAT(s.state SEPARATOR ',') AS CHAR) as state,
  -- tbl_city
  CAST(GROUP_CONCAT(ct.city SEPARATOR ',') AS CHAR) as city
  
  -- tbl_papers
  FROM tbl_country c
  LEFT JOIN tbl_state s ON s.country_code = c.country_code
  LEFT JOIN tbl_city ct ON ct.state_id = s.state_id
  
  GROUP BY (c.country_code);
  </code></pre>
  
  <p>I am using <a href="https://www.datatables.net/" rel="nofollow noreferrer">Datatable</a> plugin which is a quite powerful and easy to use but whenever I tried to use search filter it will now work as the datatable's <a href="https://github.com/emran/ssp" rel="nofollow noreferrer">Server Side PHP class</a> tries to create a dynamic query with where condition based on the bind columns. </p>
  
  <p>The query bind the datatable as shown here in first row as no search is done :
  <a href="https://i.stack.imgur.com/6N1gV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6N1gV.png" alt="Correct Datatable Binding"></a></p>
  
  <p>The dynamic query is like this:</p>
  
  <pre><code>SELECT    sql_calc_found_rows `c`.`country_code`,
            `c`.`country`,
            group_concat(`s`.`state` separator "," ) AS state,
            group_concat(`ct`.`city` separator ",")  AS city
  FROM      `tbl_country`                            AS `c`
  LEFT JOIN `tbl_state`                              AS `s`
  ON        (
                      `s`.`country_code` = `c`.`country_code`)
  LEFT JOIN `tbl_city` AS`ct`
  ON        `ct`.`state_id` = `s`.`state_id`
  WHERE     (
                      `c`.`country_code` LIKE :binding_0
            OR        `c`.`country` LIKE :binding_1
            OR        group_concat(`s`.`state` separator ",") LIKE :binding_2
            OR        group_concat(`ct`.`city` separator ",") LIKE :binding_3)
  GROUP BY  `c`.`country_code`
  ORDER BY  `c`.`country_code` ASC limit 0,
            10
  </code></pre>
  
  <p>Which obviously gives error like shown here:
  <a href="https://i.stack.imgur.com/2i2bR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/2i2bR.png" alt="Error due to adding where condition with GROUP_CONCAT keyword "></a></p>
  
  <p>Is there anyway, I can filter/ search based on given city and state and country details on above mentioned scenario using PHP and datatable plugin. May be I need to change my query structure!</p>