lookupcombobox Delphi - lookupCombobox - values from relatioship

[Isaac Badru 0]

Hello everyone

Sorry about my english, Im from Mozambique and its not my mother Language

I'm developing in Delphi, I created a birthplace table (id, country_Id, country_name) and

city (id, city_Id, city_name) in Xampp, then

INSERT INTO country (id, country_id, country_name) VALUES (NULL, '2', 'Brazil');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '2', 'Rio de janeiro');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '2', 'Sao Paulo');

 

INSERT INTO country (id, country_id, country_name) VALUES (NULL, '4', 'USA');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '4', 'California');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '4', 'New York');

in the country FDQuery I put it: select * from country;

       in the city FDQuery I put it: select * from city inner join country where city.city_id=country.country_id;

and have

city_id city_name country_id country_name

2 Rio de janeiro 2 Brazil

2 Sao Paulo 2 Brazil

4 California 4 USA

4 New York 4 USA

Its Ok, But when I select a country in the Lookupcombobox, (eg USA), when I select the city in the city Lookupcombobox it brings all cities, but, coundnt

 

Please Help

[vfbb 285]

You need 2 tables with this fields

 

city
   city_id
   country_id
   city_name

 

country
   country_id
   country_name

 

Get all countries
SELECT * FROM country ORDER BY country.country_name ASC;

 

Get all cities with their countries
SELECT * FROM city LEFT JOIN country ON city.country_id=country.country_id ORDER BY city.city_name ASC;

 

Get all cities of a specific country (change the XXX to the country number)
SELECT * FROM city WHERE city.country_id=XXX ORDER BY city.city_name ASC;

[Isaac Badru 0]

Correction :

I created a birthplace DB, with two tables, country (id, country_Id, country_name) and city (id, city_Id, city_name) in Xampp

 

Bom dia vfbb, obrigado, o que pretendo é que na minha aplicação após selecionar o país na lookcombobox país, ao selecionar a cidade na lookcombobox cidade apenas apareçam as cidades correspondentes do país anteriormente selecionado, como podes ver na imagem abaixo, está a mostrar todas cidades

 

A outra coisa que gostaria que me dissesses é se teria de implementar o código na FDQuery da cidade ou no procedimento dentro do form ?

 

[vfbb 285]

Suas tabelas estão erradas, veja novamente como teria que ser:

10 hours ago, vfbb said:

city

   city_id
   country_id
   city_name

 

country
   country_id
   country_name

É sempre melhor executar essas queries dentro do código, você terá mais opções fazendo isso.

[Isaac Badru 0]

Sim amigo, mas ainda não consegui como vés

[vfbb 285]

Depois que as suas tabelas estiverem exatamente da forma como te falei, você poderá usar o seguinte código:

 

var
  LCountriesIds: TArray<Integer>;
  LCitiesIds: TArray<Integer>;
  
// Load the cities after country combobox change
procedure TForm1.ComboBox1Change(Sender: TObject);
begin
  ComboBox2.Items.Clear;
  SetLength(LCitiesIds, 0);
  if ComboBox1.ItemIndex = -1 then
    Exit;
  FDQuery1.SQL.Text := 'SELECT city.city_id, city.city_name FROM city WHERE city.country_id=:country_id ORDER BY city.city_name ASC;';
  FDQuery1.ParamByName('country_id').AsInteger := LCountriesIds[ComboBox1.ItemIndex];
  FDQuery1.Open;
  try
    while not FDQuery1.Eof do
    begin
      LCitiesIds := LCitiesIds + [FDQuery1.FieldByName('city_id').AsInteger];
      ComboBox2.Items.Add(FDQuery1.FieldByName('city_name').AsString);
      FDQuery1.Next;
    end;
  finally
    FDQuery1.Close;
  end;
end;

// Load all countries after form show
procedure TForm1.FormShow(Sender: TObject);
begin
  ComboBox1.Items.Clear;
  SetLength(LCountriesIds, 0);

  FDQuery1.SQL.Text := 'SELECT country.country_id, country.country_name FROM country ORDER BY country.country_name ASC;';
  FDQuery1.Open;
  try
    while not FDQuery1.Eof do
    begin
      LCountriesIds := LCountriesIds + [FDQuery1.FieldByName('country_id').AsInteger];
      ComboBox1.Items.Add(FDQuery1.FieldByName('country_name').AsString);
      FDQuery1.Next;
    end;
  finally
    FDQuery1.Close;
  end;
  ComboBox1Change(nil);
end;

Entender este simples código é primordial para fazer qualquer query.

Edited April 3, 2020 by vfbb
Added the variables LCountriesIds and LCitiesIds

[Isaac Badru 0]

Viva a principio eu gostaria de usar lookupCombobox para usar dados da Base de dados e nao combobox, mas enfim, sendo cidades e Paises sempre serao os mesmos dados e porderiam ficar num combobox, mas poder, me ensinas a fazer relaçoes no combobox

 

Por acaso nunca gostei de arrays dai tive dificuldades , nestes trechos por exemplo, nao entendi o que seria

                    LCitiesIds := LCitiesIds + [FieldByName('city_id').AsInteger];
                     ComboboxCity.Items.Add(FieldByName('city_name').AsString);

 

Tentei fazer umas adaptaçoes aqui ao jeito que entendo...

Ja que eu uso uma UDM onde ficam todas as FDQ's tentei fazer abaixo, peço pra melhorares a parte dos arrays, pois nao sou nada bom em arrays

 

Segue o codigo que tentei melhorar, so tens de criar dois combobox : ComboboxCity , ComboboxCountry

 

 

unit UnitTeste;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.DBCtrls;

type
    TForm1 = class(TForm)
    ComboboxCountry: TComboBox;
    ComboboxCity: TComboBox;
      procedure FormShow(Sender: TObject);
    procedure ComboboxCountryChange(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses UDM;
procedure TForm1.ComboboxCountryChange(Sender: TObject);

  var
LCountriesIds : array of integer;
LCitiesIds    : array of integer;

   begin
      with DM.FDQuery1 do
      begin
        ComboboxCity.Items.Clear;
          SetLength(LCitiesIds, 0);   // Imagino ser o tamanho do array , é isso ?
        if ComboboxCity.ItemIndex = -1 then
        Exit;
      // SQL.aDD ('SELECT city.city_id, city.city_name FROM city  inner join Country WHERE city.country_id=country_id ORDER BY city.city_name ASC;');
      //   ... BY city.city_name ASC;'   da erro com o ASC
                                                             // ...WHERE city.country_id=country_id
                                                                       //   country_id= :country_id
                                                                       //        essses (:) foi erro ?

         SQL.aDD ('SELECT City.city_id, City.city_name FROM City inner join Country WHERE City.city_id =Country.country_id;');
           ParamByName('country_id').AsInteger := LCountriesIds[ComboboxCity.ItemIndex];
            Open;
         try
            while not DM.FDQuery1.Eof do
                begin
                    LCitiesIds := LCitiesIds + [FieldByName('city_id').AsInteger];
                      ComboboxCity.Items.Add(FieldByName('city_name').AsString);
                      Next;
                end;
                  finally
            Close;
         end;
      end;
   end;

procedure TForm1.FormShow(Sender: TObject);

var
LCountriesIds : array of array of integer;
   begin

      with DM.FDQCountry do
      begin
        ComboboxCountry.Items.Clear;
        SetLength(LCountriesIds, 0);

        SQL.Text := 'SELECT Country.country_id, Country.country_name FROM Country ORDER BY Country.country_name ASC;';
        Open;
          try
               while not DM.FDQCountrys.Eof do
                   begin
            //     LCountriesIds := LCountriesIds + [FieldByName('codigoP').AsInteger];    //  Incompatible types: Dynamic array an integer
                          ComboboxCountry.Items.Add(FieldByName('country_id').AsString);
                          Next;
                   end;
                finally
              Close;
          end;
       //  ComboBox1Change(nil);     // FireDAC.Stan.Param uis not specified in USES list
      end;
   end;
end.

 

 

 

Assim ficou a imagem em combobox

 

[Isaac Badru 0]

vfbb

Vamos a isso ...

[Guest]

I do not understand ANYTHING!!

[Isaac Badru 0]

38 minutes ago, Dany Marmur said:

I do not understand ANYTHING!!

ANYTHING, its AMBIGUOUS

[vfbb 285]

@Isaac Badru Your code is complete wrong!! Please, understand, my example of code works perfectly. You don’t need to change it.

 

[Guest]

7 minutes ago, Isaac Badru said:

TODOS es AMBIGUIOZO

Desculpa. Nao habla Portugeze, me.

[Isaac Badru 0]

57 minutes ago, vfbb said:

@Isaac Badru Your code is complete wrong!! Please, understand, my example of code works perfectly. You don’t need to change it.

  

Camarada, entenda, não disse que teu codigo não esteja perfeito, apenas não foste ao encontro do meu problema

Eu preciso de soluçao com Lookup combobox e não Combobox

Eu quero selecionar um pais e no outro butao apenas aparecerem as cidades correspondentes, so isso

Obrigado

 

[Isaac Badru 0]

57 minutes ago, Dany Marmur said:

Desculpa. Nao habla Portugeze, me. 

See 1st post

[Sherlock 663]

@Isaac Badru: Welcome to our little english Delphi forum.

I know it is tough to post in English, especially technical stuff. There are people from all over the world here, and for everyone to profit from all that knowledge we use the common language of Delphi...and English to explain what we try to achieve and what we don't understand or know. Please try sticking to English as there might be a fellow programmer who can't speak Portuguese but is either the absolute expert for the question you are asking or has the same issue and desperately needs help. Google translate or Deepl are adequate tools to help you create your posts in a way anybody can understand, and more importantly can search for using the engine of their choice. You will find that searching for the solution of a programming problem will yield more useful results when the search is done with English key words anyway (or Chinese, but that is a different story), so try to get used to it, and enjoy learning English on the way.

 

I translated above text with https://translate.google.com and with https://www.deepl.com. Which one is better? 

 

Google says: Bem-vindo ao nosso pequeno fórum Delphi em inglês. Eu sei que é difícil postar em inglês, especialmente coisas técnicas. Existem pessoas de todo o mundo aqui e, para que todos possam lucrar com todo esse conhecimento, usamos a linguagem comum do Delphi ... e o inglês para explicar o que tentamos alcançar e o que não entendemos ou sabemos. Tente aderir ao inglês, pois pode haver um colega programador que não sabe falar português, mas é o especialista absoluto na pergunta que você está fazendo ou tem o mesmo problema e precisa desesperadamente de ajuda. O Google translate ou Deepl são ferramentas adequadas para ajudá-lo a criar suas postagens de uma maneira que qualquer pessoa possa entender e, mais importante, pode pesquisar usando o mecanismo de sua escolha. Você descobrirá que procurar a solução de um problema de programação produzirá resultados mais úteis quando a pesquisa for feita com palavras-chave em inglês (ou chinês, mas essa é uma história diferente), então tente se acostumar e aproveite o aprendizado Inglês a caminho.

 

Deepl says: Bem-vindo ao nosso pequeno fórum inglês Delphi. Eu sei que é difícil postar em inglês, especialmente coisas técnicas. Há pessoas de todo o mundo aqui, e para que todos beneficiem de todo esse conhecimento usamos a língua comum da Delphi... e o inglês para explicar o que tentamos alcançar e o que não entendemos ou não sabemos. Por favor, tente manter-se fiel ao inglês, pois pode haver um colega programador que não sabe falar português, mas ou é o especialista absoluto para a pergunta que está a fazer ou tem o mesmo problema e precisa desesperadamente de ajuda. Google translate ou Deepl são ferramentas adequadas para o ajudar a criar as suas mensagens de uma forma que qualquer pessoa possa compreender e, mais importante ainda, pode procurar usar o motor da sua escolha. Você vai descobrir que pesquisar pela solução de um problema de programação irá produzir resultados mais úteis quando a pesquisa é feita com palavras-chave em inglês (ou chinês, mas isso é uma história diferente), por isso tente se acostumar, e aproveite para aprender inglês no caminho.

 

 

[Sherlock 663]

As to the issue at hand, I feel your data is not correct. Every city and every country should have a unique ID. In your example, Sao Paolo and Rio de Janeiro have the same ID. As well as California (not a city, btw) and New York (maybe not a city) who both have the same ID. Furthermore you at least need a country_ID in your city table. Better yet, a junction table that references both IDs*. What follows then is quite simple: create a query to select cities for a variable country_ID. Set this variable according to the country_ID selected in the country combo and update the query. Then the city LookupCombo should display the desired result.

 

*: This might seem a little over the top at first glance, but there is a Santa Cruz in at least 15 different countries...

[Isaac Badru 0]

3 hours ago, Sherlock said:

I know it is tough to post in English, especially technical stuff. There are people from all over the world here, and for everyone to profit from all that knowledge we use the common language of Delphi...and English to explain what we try to achieve and what we don't understand or know. Please try sticking to English as there might be a fellow programmer who can't speak Portuguese but is either the absolute expert for the question you are asking or has the same issue and desperately needs help. Google translate or Deepl are adequate tools to help you create your posts in a way anybody can understand, and more importantly can search for using the engine of their choice. You will find that searching for the solution of a programming problem will yield more useful results when the search is done with English key words anyway (or Chinese, but that is a different story), so try to get used to it, and enjoy learning English on the way. 

 

Good morning,
I had written in English, but when I saw that the vfbb member is from Brazil, I preferred to speak in Portuguese to easily and better understand each other

As for Mr. Dany Marmur, I didn't understand if he don't understand Delphi or Portuguese language

 

[Isaac Badru 0]

 

 

3 hours ago, Sherlock said:

As to the issue at hand, I feel your data is not correct. Every city and every country should have a unique ID. In your example, Sao Paolo and Rio de Janeiro have the same ID. As well as California (not a city, btw) and New York (maybe not a city) who both have the same ID. Furthermore you at least need a country_ID in your city table. Better yet, a junction table that references both IDs*. What follows then is quite simple: create a query to select cities for a variable country_ID. Set this variable according to the country_ID selected in the country combo and update the query. Then the city LookupCombo should display the desired result. 

 

*: This might seem a little over the top at first glance, but there is a Santa Cruz in at least 15 different countries...

Hi, I understand that its better to each city have a differently ID, but as nobody will add or delete any city i did like that, but ok, I can do look like this to connect the referential country:

 

INSERT INTO country (id, country_id,country_name) VALUES (NULL, '2', 'Brazil');

                  INSERT INTO city (id, city_id, CITY_COUNTRY_ID, city_name) VALUES (NULL, '1', '2 ', 'Rio de janeiro');

                  INSERT INTO city (id, city_id, CITY_COUNTRY_ID, city_name) VALUES (NULL, '2', ' 2', 'Sao Paulo');

 

INSERT INTO country (id, country_id, country_name) VALUES (NULL, '4', 'USA');

                  INSERT INTO city (id, city_id, CITY_COUNTRY_ID, city_name) VALUES (NULL, '3', '4 ', 'California');

                  INSERT INTO city (id, city_id, CITY_COUNTRY_ID, city_name) VALUES (NULL, '4', '4 ', 'New York');



in the country FDQuery : select * from country;

       in the city FDQuery : select * from city inner join country where city.CITY_COUNTRY_ID=country.country_id;

 

*************

last

INSERT INTO country (id, country_id, country_name) VALUES (NULL, '2', 'Brazil');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '2', 'Rio de janeiro');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '2', 'Sao Paulo');

 

INSERT INTO country (id, country_id, country_name) VALUES (NULL, '4', 'USA');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '4', 'California');

                  INSERT INTO city (id, city_id, city_name) VALUES (NULL, '4', 'New York'); 



in the country FDQuery I put it: select * from country;

       in the city FDQuery I put it: select * from city inner join country where city.city_id=country.country_id;

 

 

Well, I don't know how to do it, what I would like to do is select a lookupcombobox or combobox country and then when selecting in the other lookupcombobox or combobox only the respective cities of that country appear

 

thanks

[Guest]

I should apologise too. My way of saying "this is an english forum" was apparently considered funny only by myself. @Isaac Badru good luck with you coding!

[Isaac Badru 0]

Its time to english people start to learn other languages

hehe

[Guest]

I almost know one language, my mother tongue.

I have a knowledge of English. In my own opinion it is usable.

I can diddle with at least 4 more languages.

I know of maybe 30 more.

 

I will continue to learn English.

I do not have the capacity to learn more languages in my current context. Sorry 🙂

[Isaac Badru 0]

As long as we are healthy we can always learn something more

I hope to learn French, Russian, Indian or Chinese

[Guest]

A challenge... event to native speakers!

 

https://www.theguardian.com/books/2020/jan/27/brexit-50p-coin-boycott-philip-pullman-oxford-comma

 

*lol*