How to achieve this complex search using jQuery datatable plugin?
<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>