Step by step to create a list with states and cities with Fields Joomla Topic is solved

[rikaryo]

Friends, I wanted help, I'm trying to create a form field with states and cities in Brazil, I tried to create it with List, but I can't generate it.

I looked for tips in the official documentation, but it's very difficult and I've tried a lot of things on the interface, but I couldn't, I'm self-taught and not a programmer.

I have an xml with all the states and cities in Brazil, but Joomla seems to me like Fields Joomla doesn't have anything that uses this file, via fields I could add state and city one by one.

Could someone show me step-by-step documentation to create this specific field.

Thanks to everyone who can help me.

[gws]

I think you will need a form builder to do what you require.

[ceford]

You could make a table from your xml data and then use an SQL field:

https://jdocmanual.org/jdocmanual?manua ... l-field.md

[rikaryo]

You could make a table from your xml data and then use an SQL field:

https://jdocmanual.org/jdocmanual?manua ... l-field.md

Great tip, thank you, I was trying to understand how this option works, I followed the steps in the tutorial, but it's not working.

Would it be too much to ask for a more complete return from you?

I downloaded the data from IBGE, which handles official data and statistics for states and municipalities.

They provide a ready-made MySQL, when I added it, two tables were created:

1 - cities
2 - states

In the Joomla field I added:

Code: Select all

SELECT `id` AS value, `title` AS text
FROM `states`
WHERE `state` = 1
ORDER BY `title` ASC

When doing this the error appears:

1054
Unknown column 'title' in 'field list'

When I add the second mysql table call:

Code: Select all

SELECT `id` AS value, `title` AS text
FROM `cities`
WHERE `state` = 1
ORDER BY `title` ASC

Several MySQL errors appear, only on the page where I am going to add the form, which is the edit profile page.

See the attached images:

Thank you in advance for your attention

[kishoremaj]

Hi,

Please post the full structure of the table , There is mismatch in column name.

Regards
Kishore

[ceford]

The word 'state' is a bit ambiguous when dealing with areas of a country as it is often used to indicate the state of the record - published, unpublished, archived or trashed. You need to use the field names. Is it really 'title'? Try your query in phpMyAdmin for debugging purposes. Table structure would help.

[rikaryo]

Hi,

Please post the full structure of the table , There is mismatch in column name.

Regards
Kishore

Thanks for the feedback Kishore

Code: Select all

-- Estrutura da tabela `cidades`
--

CREATE TABLE `cidades` (
  `id` int NOT NULL,
  `nome` varchar(120) DEFAULT NULL,
  `id_estado` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Extraindo dados da tabela `cidades`
--

INSERT INTO `cidades` (`id`, `nome`, `id_estado`) VALUES
(1, 'Afonso Cláudio', 8),
(2, 'Água Doce do Norte', 8),
(3, 'Águia Branca', 8),
(4, 'Alegre', 8),
(5, 'Alfredo Chaves', 8),
(6, 'Alto Rio Novo', 8),
(7, 'Anchieta', 8),

over 5 thousand cities....


- Estrutura da tabela `estados`
--

CREATE TABLE `estados` (
  `id` int NOT NULL,
  `nome` varchar(75) DEFAULT NULL,
  `uf` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Extraindo dados da tabela `estados`
--

INSERT INTO `estados` (`id`, `nome`, `uf`) VALUES
(1, 'Acre', 'AC'),
(2, 'Alagoas', 'AL'),
(3, 'Amazonas', 'AM'),
(4, 'Amapá', 'AP'),
(5, 'Bahia', 'BA'),
(6, 'Ceará', 'CE'),
(7, 'Distrito Federal', 'DF'),
(8, 'Espírito Santo', 'ES'),
(9, 'Goiás', 'GO'),
(10, 'Maranhão', 'MA'),
(11, 'Minas Gerais', 'MG'),
(12, 'Mato Grosso do Sul', 'MS'),
(13, 'Mato Grosso', 'MT'),
(14, 'Pará', 'PA'),
(15, 'Paraíba', 'PB'),
(16, 'Pernambuco', 'PE'),
(17, 'Piauí', 'PI'),
(18, 'Paraná', 'PR'),
(19, 'Rio de Janeiro', 'RJ'),
(20, 'Rio Grande do Norte', 'RN'),
(21, 'Rondônia', 'RO'),
(22, 'Roraima', 'RR'),
(23, 'Rio Grande do Sul', 'RS'),
(24, 'Santa Catarina', 'SC'),
(25, 'Sergipe', 'SE'),
(26, 'São Paulo', 'SP'),
(27, 'Tocantins', 'TO');

--
-- Índices para tabelas despejadas
--

--
-- Índices para tabela `cidades`
--
ALTER TABLE `cidades`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_Cidade_estado` (`id_estado`);

--
-- Índices para tabela `estados`
--
ALTER TABLE `estados`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT de tabelas despejadas
--

--
-- AUTO_INCREMENT de tabela `cidades`
--
ALTER TABLE `cidades`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5565;

--
-- AUTO_INCREMENT de tabela `estados`
--
ALTER TABLE `estados`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;
COMMIT;

[Per Yngve Berg]

You are not consistent (estado, estados).

Use characterset utf8mb4 instead of latin1.

[kishoremaj]

The query should be for table
estados (`id`, `nome`, `uf`)

Code: Select all

SELECT `id` AS value, `nome` AS text
FROM `estados`
ORDER BY `nome` ASC

[ceford]

I think you should make the `name` field NOT NULL or you have the prospect of having states and cities with null names. So no DEFAULT either. I don' know what `uf` is but that might be NOT NULL too:

Code: Select all

CREATE TABLE `aaaaa_cidades` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(120) NOT NULL,
  `id_estado` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `aaaaa_estados` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(75) NOT NULL,
  `uf` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

Replace aaaaa with your own prefix.

5K cities is a lot to choose from in a list select!

[rikaryo]

You are not consistent (estado, estados).

Use characterset utf8mb4 instead of latin1.

Thanks for your attention, I've already corrected it to utf8, including the characters that had bad encoding.

[rikaryo]

I think you should make the `name` field NOT NULL or you have the prospect of having states and cities with null names. So no DEFAULT either. I don' know what `uf` is but that might be NOT NULL too:

Code: Select all

CREATE TABLE `aaaaa_cidades` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(120) NOT NULL,
  `id_estado` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `aaaaa_estados` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(75) NOT NULL,
  `uf` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

Replace aaaaa with your own prefix.

5K cities is a lot to choose from in a list select!

I made the recommended corrections and the "kscj5_cidades" table worked very well.

UF is an abbreviation for State officially used in Brazil e.g.: UF= CE (State of Ceará)

Now the error appeared in the kscj5_states table:

MySQL Message: Documentation

#1068 - Defined more than one primary key

This one here:

Code: Select all

CREATE TABLE `kscj5_estados` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nome` varchar(75) NOT NULL,
  `uf` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

--
-- Extraindo dados da tabela `estados`
--

INSERT INTO `kscj5_estados` (`id`, `nome`, `uf`) VALUES
(1, 'Acre', 'AC'),
(2, 'Alagoas', 'AL'),
(3, 'Amazonas', 'AM'),
(4, 'Amapá', 'AP'),
(5, 'Bahia', 'BA'),
(6, 'Ceará', 'CE'),
(7, 'Distrito Federal', 'DF'),
(8, 'Espírito Santo', 'ES'),
(9, 'Goiás', 'GO'),
(10, 'Maranhão', 'MA'),
(11, 'Minas Gerais', 'MG'),
(12, 'Mato Grosso do Sul', 'MS'),
(13, 'Mato Grosso', 'MT'),
(14, 'Pará', 'PA'),
(15, 'Paraíba', 'PB'),
(16, 'Pernambuco', 'PE'),
(17, 'Piauí', 'PI'),
(18, 'Paraná', 'PR'),
(19, 'Rio de Janeiro', 'RJ'),
(20, 'Rio Grande do Norte', 'RN'),
(21, 'Rondônia', 'RO'),
(22, 'Roraima', 'RR'),
(23, 'Rio Grande do Sul', 'RS'),
(24, 'Santa Catarina', 'SC'),
(25, 'Sergipe', 'SE'),
(26, 'São Paulo', 'SP'),
(27, 'Tocantins', 'TO');

--
-- Índices para tabelas despejadas
--

--
-- Índices para tabela `cidades`
--
ALTER TABLE `kscj5_cidades`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_Cidade_estado` (`id_estado`);

--
-- Índices para tabela `estados`
--
ALTER TABLE `kscj5_estados`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT de tabelas despejadas
--

--
-- AUTO_INCREMENT de tabela `cidades`
--
ALTER TABLE `kscj5_cidades`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5565;

--
-- AUTO_INCREMENT de tabela `estados`
--
ALTER TABLE `kscj5_estados`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Thank you for your help

[ceford]

I previously quoted the query I used to test my answer. Here is my dump of the states table after making adjustments:

Code: Select all

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: May 10, 2024 at 04:39 AM
-- Server version: 8.0.33
-- PHP Version: 8.3.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `jdm3`
--

-- --------------------------------------------------------

--
-- Table structure for table `aaaaa_estados`
--

CREATE TABLE `aaaaa_estados` (
  `id` int NOT NULL,
  `nome` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
  `uf` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `aaaaa_estados`
--

INSERT INTO `aaaaa_estados` (`id`, `nome`, `uf`) VALUES
(1, 'Acre', 'AC'),
(2, 'Alagoas', 'AL'),
(3, 'Amazonas', 'AM'),
(4, 'Amapá', 'AP'),
(5, 'Bahia', 'BA'),
(6, 'Ceará', 'CE'),
(7, 'Distrito Federal', 'DF'),
(8, 'Espírito Santo', 'ES'),
(9, 'Goiás', 'GO'),
(10, 'Maranhão', 'MA'),
(11, 'Minas Gerais', 'MG'),
(12, 'Mato Grosso do Sul', 'MS'),
(13, 'Mato Grosso', 'MT'),
(14, 'Pará', 'PA'),
(15, 'Paraíba', 'PB'),
(16, 'Pernambuco', 'PE'),
(17, 'Piauí', 'PI'),
(18, 'Paraná', 'PR'),
(19, 'Rio de Janeiro', 'RJ'),
(20, 'Rio Grande do Norte', 'RN'),
(21, 'Rondônia', 'RO'),
(22, 'Roraima', 'RR'),
(23, 'Rio Grande do Sul', 'RS'),
(24, 'Santa Catarina', 'SC'),
(25, 'Sergipe', 'SE'),
(26, 'São Paulo', 'SP'),
(27, 'Tocantins', 'TO');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `aaaaa_estados`
--
ALTER TABLE `aaaaa_estados`
  ADD PRIMARY KEY (`id`),
  ADD KEY `nome` (`nome`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `aaaaa_estados`
--
ALTER TABLE `aaaaa_estados`
  MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

You can adjust table structure from within phpMyAdmin. For example, you can add (or remove) keys. The cities table needs index keys on `nome` and `id_estado`.

[ceford]

I mentioned that having a list of 5000 cities to choose from may pose a user experience problem. A different solution may be to have a field that works like smart search - start typing a city name and after two characters you get a list of cities beginning with those two characters. The list shrinks as you type more characters. That requires some JavaScript but It still uses the tables you just created. I am unsure how to do that! Keep it in mind.

[rikaryo]

I mentioned that having a list of 5000 cities to choose from may pose a user experience problem. A different solution may be to have a field that works like smart search - start typing a city name and after two characters you get a list of cities beginning with those two characters. The list shrinks as you type more characters. That requires some JavaScript but It still uses the tables you just created. I am unsure how to do that! Keep it in mind.

Ceford, thank you very much for your help, and it would be great to have a field with intelligent research, it would greatly improve the user experience, I will mature this possibility, but for now I will complete this work and later on I will make this modification as an update, even to have time to develop.

When I do, I'll let you know in this post.

Thank you very much and have a great weekend