GROUP BY not working?

Question

My database

CREATE TABLE `mytable` (
  `id` smallint(4) UNSIGNED NOT NULL,
  `name` char(30) NOT NULL,
  `country` char(30) NOT NULL
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;

My data

id   | name              | country
-----|-------------------|--------
1    | Tom Hanks         | Usa
2    | Monica Bellucci   | Italy
3    | Takeshi Kitano    | Japan
4    | Javier Bardem     | Spain
5    | Jim Carrey        | Canada
6    | Luc Besson        | France
7    | Leonardo DiCaprio | Usa
8    | Pedro Almodovar   | Spain
9    | Ryan Gosling      | Canada
10   | Terence Hill      | Italy
11   | Jean Reno         | France
12   | Roland Emmerich   | Germany
13   | Mamoru Hosoda     | Japan
14   | James Cameron     | Canada
15   | Tom Cruise        | Usa
16   | Penélope Cruz     | Spain
17   | Sergio Leone      | Italy
18   | Claude Lelouch    | France
19   | Max Riemelt       | Germany
20   | Hiroyuki Sanada   | Japan

I have a search input text (for typing country name) with which I display in dropdown list existing countries in database

My query send the a keyword and returns me countries (with id), it works fine but it displays countries with duplicates

<?php
require_once '../config/dbconnect.php';
if(!empty($_POST["keyword"])) {
    $result = $conn->query("SELECT id, country FROM mytable WHERE country LIKE '%".$_POST["keyword"]."%' ORDER BY country LIMIT 10");
    $rowCount = $result->num_rows;
    if($rowCount > 0){
        while($row = $result->fetch_assoc()){
            echo '<option value='.$row["id"].'>'.$row["country"].'</option>';
        }
    }
}
?>

The problem is that I have to display countries without duplicates, only distinct values, and I don't find a solution,

I used the clause GROUP BY but it doesn't work, the dropdown list stays blank, I don't know how could I resolve the problem

<?php
require_once '../config/dbconnect.php';
if(!empty($_POST["keyword"])) {
    $result = $conn->query("SELECT id, country FROM mytable WHERE country LIKE '%".$_POST["keyword"]."%' GROUP BY country");
    $rowCount = $result->num_rows;
    if($rowCount > 0){
        while($row = $result->fetch_assoc()){
            echo '<option value='.$row["id"].'>'.$row["country"].'</option>';
        }
    }
}
?>

Thanks for your help!

You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). — Qirel, Sep 22 '17 at 17:08
The thing is, the id in your table refers to a person, not a country. You probably just want to `SELECT DISTINCT country FROM mytable`, and use that for both the value and the displayed text. — Don't Panic, Sep 22 '17 at 17:11
This seems to be a database design issue. Clearly either `name` or `country` is not an attribute of the primary key. — bassxzero, Sep 22 '17 at 17:11
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Sep 22 '17 at 17:13
try using set sql_mode='' before the query and check if it's working. — Anirudha Gupta, Sep 22 '17 at 17:14
In the absence of any aggregating functions, inclusion of a GROUP BY clause is invariably non-sensical. This is no exception. — Strawberry, Sep 22 '17 at 17:20
Thanks for comments, I can not use DISTINCT country FROM mytable, because I need also the id which is stored in the option value. The id is used in another query (when I click on dropodown list) to display the names corresponding to the country — zanzibar, Sep 22 '17 at 17:26

score 0 · Answer 1 · answered Sep 22 '17 at 17:32

Just a precision

I can not use SELECT DISTINCT country FROM mytable, because I need also the id which is stored in the option value

The id is used in another query (when I click on dropodown list) to display the names corresponding to the country selected

score 0 · Answer 2 · answered Sep 22 '17 at 18:59

You have a problem with database design. You are mixing names and countries in one table. So ID in table is referencing to both of them.

You have two options:

Easy but worse option: use SELECT DISTINCT country FROM mytable and update your another query (when you click on dropdown list). You can display names corresponding to the country by this query: SELECT name FROM mytable where country ='".$country."'.

Harder but better: update your DB schema by Second Normal Form (Create new table for countries and update entries in mytable.)

You can use this script:

CREATE TABLE `countries` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` char(30) NOT NULL
);

INSERT INTO countries (name) SELECT DISTINCT country as name FROM mytable;

ALTER TABLE `mytable`
ADD `country_id` int(10) unsigned NOT NULL,
ADD FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`);

UPDATE mytable SET country_id = (SELECT id FROM countries WHERE name = country);

ALTER TABLE `mytable` DROP `country`;

After that you can display countries:

<?php
require_once '../config/dbconnect.php';
if(!empty($_POST["keyword"])) {
    $result = $conn->query("SELECT id, name FROM countries WHERE name LIKE '%".$_POST["keyword"]."%' ORDER BY name LIMIT 10");
    $rowCount = $result->num_rows;
    if($rowCount > 0){
        while($row = $result->fetch_assoc()){
            echo '<option value='.$row["id"].'>'.$row["name"].'</option>';
        }
    }
}
?>

Don't forget to update your another query to filter by country_id:

SELECT name FROM mytable where country_id ='".$countryId."'

Good evening J. Ancinec ! Yes I updated it, thank you Sir ! – zanzibar Sep 23 '17 at 19:32 — zanzibar, Sep 23 '17 at 19:32

GROUP BY not working?

2 Answers2